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.

About these ads
This entry was posted in Security, SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s