Correct the SQL Server Authentication Mode in the Windows Registry

By: Tim Ford | Comments (7) | Related: > Security

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

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Monday, June 24, 2013 - 2:33:29 PM - IsaNeil	Back To Top (25543)
It works in SQL 2008 Enterprise Edition... thanks!!

Tuesday, December 18, 2012 - 6:56:25 AM - Rohith Shetty	Back To Top (21003)
Its working.. many thanks..

Thursday, June 28, 2012 - 4:19:56 AM - Burak	Back To Top (18241)
Many thanks for the solution :) It's better than microsoft's one...:)

Friday, April 18, 2008 - 5:37:10 PM - timmer26	Back To Top (893)
Just to let you know that I'm trying this out in the lab. You've raised a very good point. I'm not sure if it creates an sa login with blank password behind the scenes anyway or not. This goes to show you that it is alway a good idea to set a Dedicated Administrator Connection (DAC) just in case.

Wednesday, April 9, 2008 - 12:11:34 AM - okdeshpande	Back To Top (851)
Hi , Its very good. I have one doubt here assume that we set SQL 2005 windows authentication mode at the time of installation. after that we changed the value to mixed mode then intially we don't have password for sa login at that time how to configure password for sa.? how to change the authentication mode for SQL server agent? Thanks