Skip to main content
Version: Archive

Configure SMK for Microsoft SQL Server

Applies to:

  • DxEnterprise for Windows

Summary​

Microsoft SQL Service Master Key (SMK) has two encryptions: service account encryption and machine key encryption. The machine key encryption depends entirely on the machine name. So, it is expected to fail in clustering environment – this is the same on Microsoft Clustering Services (WSFC/MSCS). However, if the machine key encryption is no longer valid, the service account encryption will be used to access the SMK and the machine key encryption is repaired. This is part of Microsoft SQL Server self-healing mechanism.

Information​

When configuring the SMK with using service account key for Microsoft SQL Server the following procedure is recommended (for a two-node cluster):

  1. On Node01, regenerate the SMK with the force option:

    ALTER SERVICE MASTER KEY FORCE REGENERATE

  2. On Node02, use Microsoft SQL Server Configuration Manager to change the service account to “localsystem” – you do not need to start the local instance.

  3. Rehost the instance from Node01 to Node02 – this still generates error 15466 (which is expected because the service account is not the same as Node01).

  4. Open DxAdmin and right-click on Node02 under the Vhost; select Freeze vhost on node – this will put the Vhost in the administratively frozen state in which no failover will take place.

  5. Use Microsoft SQL Server Configuration Manager to change the service account from localsystem to your domain service account (the same Microsoft SQL Server service account on Node01). The instance should be restarted using Configuration Manager.

  6. Check the errorlog and there shouldn’t be any 15466 errors.

  7. Right-click on Node02 under the virtual host and select Unfreeze on node – this returns the Vhost/instance to the normal operating mode with failover.

  8. Rehost the instance from Node02 to Node01 and check the errorlog for 15466 errors.

  9. Rehost the instance back to Node02 and check the errorlog again.

  10. Once everything has been verified, please backup the SMK (just in case you ever have a need to restore it).