SQL Server stores its authentication mode in the registry. The registry can be read and updated using the T-SQL commands xp_regread and xp_regwrite.
Step 1: Construct our registry key path, run the following query and record the result:
DECLARE @InstanceName NVARCHAR(1000), EXEC master..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', --for a named instance substitute the instance name here @InstanceName OUTPUT PRINT @InstanceName
Use the result from this to build the registry path to the login key and check the current authentication mode:
DECLARE @Path NVARCHAR(4000)
,@LoginMode INT
SET @Path = N'Software\Microsoft\Microsoft SQL Server\'
+ <the result from the first query here>
+ N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Path,
N'LoginMode',
@LoginMode OUTPUT
PRINT @LoginModeA login mode of 1 is Windows authentication, 2 is mixed mode.
Update as per your requirements:
DECLARE @Path NVARCHAR(4000)
SET @Path = N'Software\Microsoft\Microsoft SQL Server\'
+ <the result from the first query here>
+ N'\MSSQLServer\'
EXEC master..xp_regwrite N'HKEY_LOCAL_MACHINE',
@Path,
N'LoginMode',
'REG_DWORD',
2 -- or 1...A SQL Server restart is required for the change to take effect.