Authentication Mode Changed After Applying Service Pack 4 for SQL Server 2005

I recently worked on an issue wherein after applying Service Pack 4(SP4) for SQL Server 2005 on a cluster, we were unable to connect to the SQL Server instance using SQL Server authentication.

To explain it better, let’s say, there are 2 nodes in a cluster with names nodes Node1 & Node2, SQL Server 2005 instance was active on Node1. SP4 was run from Node1 which first applies the SP4 on passive node and then applies on active node. From Management studio both windows and SQL authentication works fine on Node1, but once the SQL instance is failed over to Node2 it comes online without any issue, but when we try to connect to the SQL instance using management studio using SQL Server authentication, it gives login failed error message.

Based on our initial research we found that the connectivity issue occurred because the Authentication Mode of the SQL instance was changed to WINDOWS ONLY on passive node whereas on active node the authentication mode was still MIXED. We changed the following registry key value to 2 and now both the windows and SQL authentication works fine.

SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INST1\MSSQLServer\LoginMode

Now, the million dollar question was why the authentication mode was changed to WINDOWS ONLY on Node2?

Based on our research, we found that in the SQL Server 2005 setup code when patch/reinstall scenario runs on secondary cluster node the authentication mode is always set to NT authentication (WINDOWS ONLY).
This registry key which has LoginMode is part of SQL Server cluster registry checkpoint. Once the failover happens the login mode on the secondary node is updated automatically from the primary node to the correct value. There is no manual work required in this process and is transparent to the user. The following are the Engine registry sub-hives that are checkpointed under the InstanceID registry hive in SQL Server 2005:

“Cluster” , “MSSQLServer” , “Replication” , “SQLServerAgent” , “PROVIDERS”, “SQLServerSCP” , “CPE”

In my clustered environment, I found that only CPE has checkpoint, all other registry hives do not have checkpoint. That means any change applied to any registry key value which belong to registry hives other than CPE will not get updated automatically from primary node to the correct value. And LoginMode registry key is one such key which belong to MSSQLServer which do not have checkpoint on it. Hence, during SP4 installation once the value of this key is changed to 1, it will remain one until it is changed manually to 2. To resolve this issue, we have to make sure that the above registry hives should have checkpoint. KB article http://support.microsoft.com/kb/953504 will help you in adding the checkpoint to the registry hives and the precautions you need to take in order to execute the steps. However, I have mentioned the steps below to add checkpoint to the above registry hives:

Run the following commands using Command Prompt:

Imp: Beware that change to checkpointed registry keys are saved only when the associated resources are online (In this case, SQL Server). When the resources are not online, changes to the checkpointed keys will be overwritten by the last saved values from the checkpointed file.
If the instance is default, please replace the InstanceName with MSSQLSERVER

cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\Cluster"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\Replication"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\PROVIDERS"

Once these changes are performed, try failover failback.

Happy Reading,
SQLServerF1 Team

 

Leave a Reply

Your email address will not be published. Required fields are marked *