Change SQL Server Authentication Mode

This post explains how to use T-SQL or SQL Server Management Studio to modify the server authentication mode in SQL Server. The Windows Authentication mode or the SQL Server and Windows Authentication mode is selected for the SQL Server Database Engine during installation. The authentication mode can be changed at any moment after installation.

When installing in Windows Authentication mode, the SA login is disabled, and setup assigns a password. The sa login remains disabled even if you later switch to SQL Server and Windows Authentication mode. Use the ALTER LOGIN command to activate the sa login, then specify a new password to utilise it. Only SQL Server Authentication will allow the sa login to establish a connection to the server.

By using the GUI, the SQL Server authentication mode can be set. You must first click on Server, then select Properties, and finally Security. From there, you may change mode to

1.     Windows authentication mode

2.     SQL Server authentication method ( Mixed Mode)

The script listed below can also be used to check and modify the SQL Server authentication mode.

We can use  below Query to Check the SQL Server Authentication Mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  

WHEN 1 THEN 'Windows Authentication'  

WHEN 0 THEN 'Windows and SQL Server Authentication'  

END as [SQLServerAuthenticationMode]

 

--To Change to Windows Authentication

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1

GO

                                                        

 

--To change to Mixed Mode( SQL Server and Windows Authentication mode)

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

Change authentication mode with SSMS

1.     In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

2.     On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

3.     In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

4.     In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

Enable sa login

You can enable the sa login with SSMS or T-SQL.

Use SSMS

1.     In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

2.     On the General page, you might have to create and confirm a password for the sa login.

3.     On the Status page, in the Login section, click Enabled, and then click OK.