2

I can change SQL Server settings using Management Studio or programmatically as described e.g. here.

I would like to make this changes WITHOUT the need to restart SQL Server services. Is there any trick to do so??

migrated from stackoverflow.com May 13 '13 at 13:55

This question came from our site for professional and enthusiast programmers.

  • 3
    Which specific settings? – Martin Smith May 13 '13 at 13:52
  • I need to change Server authentication setting programmatically. – Rav May 13 '13 at 14:00
2

If you want to change the Authentication Mode, you can do this by running the following SQL

USE master
DECLARE @INSTANCEID VARCHAR(30), @STRVERSION VARCHAR(30), @SQLVERSION VARCHAR(30), @CMD VARCHAR(2000)
SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion'))))
SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))

IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10 
    SET @STRVERSION = 'MSSQL10'
ELSE
 IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9
  SET @STRVERSION = 'MSSQL'

IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL'
 SET @INSTANCEID = 1
ELSE
 IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10'
  SET @INSTANCEID = 'MSSQLSERVER'

SET @CMD = 'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' + '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION +'.'+ @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+', '+'REG_DWORD'+','+ ' 2' --2 is mixed auth.

EXEC(@CMD)
--PRINT @CMD

You may also have to ensure that the sa login is enabled.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

You will however still have to restart the SQL Server service.

  • 1
    Restarting is the main problem. But thank you :) – Rav May 13 '13 at 14:34
  • 1
    Well, then the answer is simple: "No, you cannot change this setting without restarting." – RoKa May 13 '13 at 14:35
  • You could just use xp_instance_regwrite as per my answer to make it a lot more robust – gbn May 13 '13 at 14:49
7

It depends on what settings you changed based on http://msdn.microsoft.com/en-GB/library/ms189631.aspx. Some require a restart (RR), other can be done via RECONFIGURE

There is no trick or workaround. At all.

Edit, now we know it is Authentication mode...

This is automatically deals with the instance name

 EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    1; -- or 2 for mixed

Restart is still needed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.