Correct the SQL Server Authentication Mode in the Windows Registry

By:   |   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\.

3

Step 3 - In the right pane, find the LoginMode key.

2rev

Step 4 - Double click on the LoginMode key and change the Value Data to 2.

1

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford 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



Comments For This Article




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..


Tuesday, July 10, 2012 - 8:25:56 AM - MiddleMac Back To Top (18418)

Guess what?  This solution works in SQL2008 R2 also.  Nice little work-around and something I think I'll keep in my toolkit.

MiddleMac


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


Tuesday, February 26, 2008 - 9:23:01 PM - jxs2151 Back To Top (324)

 Or:

Login with credentials that have local admin rights

NET START MSSQLSERVER /m

EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin';

GO

Restart SQL Services

 















get free sql tips
agree to terms