0

I have a Windows Failover Cluster set up with SQL 2016 Enterprise SP2. TDE was implemented on a DB, on node1 the DB is able to start without issues. However, when a failover occurs, on node2 the DB always goes into "Recovery Pending" state and has to be failed over back to node1 in order to start correctly.

Error: 15581, Severity: 16, State: 7. Please create a master key in the database or open the master key in the session before performing this operation.

I have checked the certificates and everything seems to match up. Not sure what is happening that it only seems to be able to start on node1 and not on node2.

Manuel
  • 71
  • 5
  • Have you checked the errorlog while database is starting up at node 2 and see if there is any mismatch from node 1? We had one scenario wherein drives were accessible at both the nodes and SQL service was up and running at both the nodes. We asked Infra admin to define dependency at cluster level - First to check the VIP, second to check the drive and then only start the SQL service(3rd step) and so far its working fine after that incident. – Learning_DBAdmin Mar 11 '19 at 18:31
  • I see these differences in the log, when the instance gets moved to node 2 I found these other errors: Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details. An error occurred during Service Master Key initialization. SQLErrorCode=x_cse_CannotDecryptSMK, State=8, LastWindowsError=0. – Manuel Mar 11 '19 at 18:36
  • 3
    Do you use the same domain account to run the SQL Server service on both nodes? The SMK is encrypted by the machine account (different on failover) and the service account (should be the same on failover). – David Browne - Microsoft Mar 11 '19 at 18:37
  • Yes, both nodes have the same domain account to run the SQL services. – Manuel Mar 11 '19 at 18:39
  • Please check the KB(Knowledge Base) article from Microsoft on this as --> https://support.microsoft.com/en-gb/help/3091643/fix-error-occurs-when-you-configure-sql-server-replication-or-set-up-a – Learning_DBAdmin Mar 11 '19 at 18:42
  • I brought the DB online by providing the master key with password and then setting the DB online. However after a few minutes the DB went back to recovery pending. I see the following errors in the log: 1)Lock request time out period exceeded. 2)An error occurred while processing log encryption. The process was recovered automatically. No user action is required. 3)Please create a master key in the database or open the master key in the session before performing this operation. 4)The log for database 'DBName' is not available. Check the event log for related error messages... – Manuel Mar 11 '19 at 22:16
  • Performed a failback to node1, no issues there. DB comes online without issues. This behavior is only experienced in node2. Could these be related to the SAN? – Manuel Mar 11 '19 at 22:21

0 Answers0