http://www.sqlservercentral.com/blogs/ctrl-alt-geek/2012/07/11/quick-tip-configure-sql-server-authentication-mode-using-t-sql/

Printed 2014/04/16 12:43AM

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

2012/07/11

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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.