Blog Post

Quick tip: configure SQL Server authentication mode using T-SQL

,

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 @LoginMode

A 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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating