Pending Restart for Authentication Type Change

In a previous post, I talked about how to determine if your SQL Server needs a restart(click here to read it).  In that post I talk about how to use the configurations view to determine if a restart is needed.  This just looks at the running vs configured values of the configuration settings. After talking about this with a co-worker, he had a very interesting question.

What about the server settings that are not in the configuration view?

Some server level settings, when changed require a reboot to take effect.  For example, if you change the Authentication type, a restart of the SQL Server service is needed for the change to take effect.

You will see this pop up box when you change a server property and a restart is needed.

In order to determine if a restart is needed, we need to look at two places, the registry and a ServerProperty.  There are a number of Server Properties. Because this post is about the Authentication type, the property we are looking for is “IsIntegratedSecurityOnly

When you change the value of the Authentication type in SQL Server Management Studio, it actually only changes it in the registry.  For the Server Property to change and match the registry, a restart of the SQL Server service is needed.

The code below can be used to completed the comparison. It looks at both places and completes the comparison.

USE MASTER
GO
DECLARE @authmodeServerProp TINYINT = 0
DECLARE @Restart            VARCHAR(100)
SELECT  @authmodeServerProp = CASE SERVERPROPERTY(‘IsIntegratedSecurityOnly’)
            WHEN 0 THEN 2  –Set to Mixed, Windows and SQL authentication
            WHEN 1 THEN –Set to Windows Only
END
DECLARE @AuthMode INT = NULL
EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’,
                         N’Software\Microsoft\MSSQLServer\MSSQLServer’,     
                         N’LoginMode’, @AuthMode OUTPUT,
       N’no_output’
IF @authmodeServerProp = @AuthMode
 BEGIN
  SELECT @Restart = ‘No pending restart of the service due ‘ +
       ‘a change in the Authenication Mode.’
 END
ELSE
  BEGIN
   SELECT @Restart =‘The Authentication Mode has changed, look into ‘ +
                 ‘restarting the service’
  END
  SELECT    CASE @authmodeServerProp
      WHEN  1 THEN ‘Windows Only’
      WHEN  2 THEN ‘Windows and SQL Authentication’
      ELSE  ‘Something when wrong’ END AS ‘ServerProperty’
    , CASE @authmode
      WHEN  1 THEN ‘Windows Only Authentication’
      WHEN  2 THEN ‘Mixed Authentication’
      ELSE ‘There was a problem’ END AS ‘Registry value’
    , @Restart   AS ‘RestartNeeded?

 

The code above simply looks into the registry and the Server Properties.  If they do not match, then a restart of the service is needed.  One thing to notice, the server property works with values of 0 and 1, while the registry works with values of 1 and 2.  That is why the code changes the 0 and 1’s of the server property to a 1 or a 2.

When you run the above code, you will have a data set similar to the one below.  The first two columns return the current value of each of the locations.  While the last column will tells us of the a restart is needed.

This will be added to my “How to complete a Server Assessment” SQL Saturday presentation.

Thanks for stopping by my blog.