SQL Server script to change SQL Server Authentication Mode

A friend recently asked me how to change the authentication mode of a SQL Server instance from Windows Authentication to Mixed Mode and back again using SQL Server Management Studio. This blog outlines the steps to change SQL Server authentication.

SQL Server supports Windows authentication and Mixed Authentication. In the Mix authentication, you can use Windows or SQL Server authentication.

You can modify SQL Server authentication in the SQL Server Management Studio GUI. You have to click on SQL Server, go to Properties, and then Security. From there, you can change the SQL Security Authentication Mode to Windows Authentication Mode.

Choose the required server authentication and click Ok. You need to restart SQL Service to take this into effect.

T-SQL Script to configure Windows authentication mode

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO

T-SQL Script to configure SQL Server and Windows authentication(Mixed) mode

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO