Problem
I have a SQL Server instance with Mixed Security Mode, BUILTIN\Administrators rights revoked and I have not set up a Dedicated Administrator Connection (DAC) in the SQL Server. I have tasked my Junior DBA with setting up sysadmin server role rights for the Active Directory group that contains all the domain logins for your DBAs and then to change the security mode to Windows Authentication Mode. Unfortunately, the Junior DBA changes the security mode first and now you find yourself locked out of your own SQL instance. What do you do to regain access to the SQL Server instance?
Solution
You hack the registry of course! Yes – This is reality behind the situation. It is necessary to shutdown SQL Server, change the registry, restart SQL Server and then add the necessary groups. Performing the following steps will get you be back in your system in no time:
Steps to Change the SQL Server Authentication Mode in the Windows Registry
Step 1 – Stop the SQL Server services via the Windows Services Applet or Management Studio.
Step 2 – Execute regedt32 from the Windows Run command and then browse to HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\.
Step 3 – In the right pane, find the LoginMode key.
Step 4 – Double click on the LoginMode key and change the Value Data to 2.
Step 5 – Restart SQL Server services via the Windows Services Applet or Management Studio.
Step 6 – Login with the sa login and password via Management Studio.
Next Steps
- At this point you can setup your Active Directory group as a SQL Server login and assign rights. You can also change the security mode back to Windows Authentication Mode. The value for HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode stores the security mode for the default SQL instance. A value of 1 signifies Windows Authentication Mode, 2 denotes Mixed Mode security.
- If you were dealing with a named instance then the key would be located under HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer\LoginMode where n is the instance number for the named instance.
- Do not panic if you face a SQL Server security issue, The next time you
- Review MSSQLTips for additional SQL Server Security Tips
