By Hally | Last Updated
How to change SQL Server authentication mode? For example, how to connect to SQL Server with Mixed Authentication mode instead of Windows authentication mode?
Now two effective methods are offered in this passage. No matter you could login into SQL Server 2005/2008/2012/2014/2016 or not, changing SQL authentication mode can be done with them. And generally, the authentication mode is changed from Windows Authentication mode to Mixed Authentication Mode.
This method requires SQL Server logon with Windows authentication at first. Therefore, if you have connected to SQL Server with Windows Authentication mode, you just need to change logon settings in SQL Server Management Studio.
Step 1: Open SQL Server Management Studio from Start Programs Microsoft SQL Server 2005/2008 SQL Server Management Studio.
Choose Windows Authentication mode, and click Connect to login SQL Server.
Step 2: Right click Server in Object Explorer and select Properties.
Step 3: In Server Properties dialog, select Security and pick "SQL Server and Windows Authentication mode" in the right panel of window. And then click "OK" to save changes.
Step 4: Click Security Logins sa in SQL Server Management Studio, and right click "sa", select Properties.
Step 5: In Login Properties dialog, type password and confirm password for "sa" account below SQL Server authentication.
And tap Status option, check Enabled under Login to enable SQL Server logon with "sa".
As long as we could login into computer with administrator, this method would help to change SQL Server authentication mode by editing LoginMode value data in Registry Editor.
Step 1: Click Start and type "regedit" in Run box. Press Enter to open Registry Editor.
Step 2: Expand to open HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer, and find "LoginMode" at the right side of window.
Step 3: Double click LoginMode and a new dialog pops up. Set Value data as 2.
"1" means Windows Authentication Mode, and "2" means SQL Server and Windows Authentication Mode or Mixed Mode Authentication.
Step 4: Close the dialog and Registry Editor.
Step 5: Restart SQL Server Services.
Step 6: Start SQL Server Management Studio, and choose SQL Server Authentication, type sa password to login SQL Server.
Related Articles:
