Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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??

share|improve this question
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
add comment

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

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

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Restarting is the main problem. But thank you :) –  Rav May 13 '13 at 14:34
    
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
add comment

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

share|improve this answer
    
+1 for xp_instance_regwrite ! –  RoKa May 13 '13 at 15:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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