SQL SERVER – AlwaysOn AG (Availability Group) and TDE Error – Please Create a Master Key

SQL
2 Comments

Along with performance consulting, sometimes I also get requests from a few clients to look at the AlwaysOn availability group related problems.

ENVIORNMENT:

Three node AlwaysOn availability group with multi-subnet cluster with two nodes (SQL-A and SQL-B) within one subnet and the third node (SQL-C) in second subnet.  All three instances are SQL Server 2014 build 12.0.4100.  There is a single Availability Group (ALWAYSONAG) which contains database TESTAG which is shown as not synchronizing.

ISSUE

I have asked more about database and found that the database was TDE enabled. They already followed the steps explained in books online to do backup and restore. But as soon as the database is added to availability group it fails. We found below error message in ERRORLOG and can see that database goes to not synchronizing.

SQL SERVER - AlwaysOn AG (Availability Group) and TDE Error - Please Create a Master Key TDE-AG-800x351
2016-12-23 17:46:41.60 spid55 Starting up database ‘TESTAG’.
2016-12-23 17:46:41.65 spid55 The database ‘TESTAG’ is marked RESTORING and is in a state that does not allow recovery to be run.
2016-12-23 17:46:42.09 spid55 ALTER DB TESTAG with AGNAME:ALWAYSONAG
2016-12-23 17:46:42.09 spid55 ALTER DB param option: SET
2016-12-23 17:46:42.15 spid24s Starting up database ‘TESTAG’.
2016-12-23 17:46:42.16 spid24s Error: 15581, Severity: 16, State: 7.
2016-12-23 17:46:42.16 spid24s Please create a master key in the database or open the master key in the session before performing this operation.

As we can see in the error log there are messages that imply TDE encrypted databases cannot be decrypted by system SPID.

WORKAROUND / SOLUTION

We needed to add encryption by service master key to the database master key. This would allow SQL Server system SPIDs (like recovery spid) to automatically open it as required.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password goes here'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

As soon as we ran the command, the issue was resolved. I think this issue can also happen even with transaction log shipping based log restore also.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Unable to Apply Security Patch – The setup has encountered an unexpected error in datastore
Next Post
SQL SERVER – Understanding JSON Use is Case-Sensitive

Related Posts

2 Comments. Leave new

  • You probably could have just exported the encyption cert…then created a new service master key and imported the encryption certificate.

    USE Master

    GO

    — Create a new master key.

    CREATE MASTER KEY ENCRYPTION

    BY PASSWORD = ‘farm password’

    — Restore the certificate.

    CREATE CERTIFICATE SPContentCert

    FROM FILE=’d:\support\SPContentCert’

    WITH PRIVATE KEY (

    FILE = ‘d:\support\SPContentCertKey’,

    DECRYPTION BY PASSWORD=’farm password’)

    Reply

Leave a Reply

Menu