Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Correct the SQL Server Authentication Mode in the Windows Registry


By:   |   Last Updated: 2008-02-25   |   Comments (7)   |   Related Tips: More > 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


Last Updated: 2008-02-25


get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 24, 2013 - 2:33:29 PM - IsaNeil Back To Top

It works in SQL 2008 Enterprise Edition... thanks!!


Tuesday, December 18, 2012 - 6:56:25 AM - Rohith Shetty Back To Top

Its working.. many thanks..


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

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

Many thanks for the solution :) It's better than microsoft's one...:)


Friday, April 18, 2008 - 5:37:10 PM - timmer26 Back To Top

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 09, 2008 - 12:11:34 AM - okdeshpande Back To Top

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

 Or:

Login with credentials that have local admin rights

NET START MSSQLSERVER /m

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

GO

Restart SQL Services

 


Learn more about SQL Server tools