Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...