How to change SQL Server Authentication Mode -

In this blog we will discus on how to change SQL Server Authentication Mode. In the later versions of SQL Server we have two types of authentication modes. These are Windows Authentication mode and SQL Server and Windows Authentication mode.
When using Windows Authentication the identity of a user principal will be checked by Windows either by Active Directory or a local operating system. While using SQL Authentication the identity will be checked by SQL Server itself. To use SQL authentication we need to enable mixed or SQL and Windows authentication there is no SQL only authentication in the later version of SQL Server.
Some of the cases where we need to change authentication mode are for example if SQL Server first installed with only Windows Authentication mode sa account will not be available. We could change to SQL Server and Windows Authentication mode and enable sa account by using ALTER LOGIN statement.

To change the authentication mode using t-sql we could use

--Convert authentication type to Windows Only Authentication Mode
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
			  N'Software\Microsoft\MSSQLServer\MSSQLServer', 
			  N'LoginMode', REG_DWORD, 1
GO
 
--Convert authentication type to 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

As the script shows the effect will be applied in the registry.After running this scripts we have to restart the service for the changes to get applied.

To change your server’s authentication mode using the SSMS
1, Right click on the server itself and select Properties

2, Then click on security tab on left and change the Server Authentication mode on the right.

3, After you change authentication mode you will get this notification that restarting the service is required for the change to get effect.

4. We could restart the service again by right clicking on the server and select Restart.