Support Portal

Welcome, Guest Login

Support Center

How to Configure SMK for Microsoft SQL Server

Last Updated: Oct 25, 2016 02:44PM PDT

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 on DxConsole 14.x, DxEnterprise 15.x or DxEnterprise 16.x, 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 over 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 management console (formerly DxConsole or DxEnterprise management console) and right-click on Node02 under the virtual host; 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 by 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
  8. Rehost the instance over from Node02 to Node01 and check the errorlog for 15466 errors
  9. Rehost 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)

Applies to...

  • DxEnterprise 15.0
  • DxEnterprise 15.5
  • DxEnterprise 16.0
  • DxConsole
  • Microsoft SQL Server

Contact Us

support.desk@dh2i.com
http://assets3.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete