SQL Server 2016 AlwaysOn Availability Group Enhancements: Support for Encrypted Databases
Overview
In SQL Server 2012 and SQL Server 2014, encrypted databases could be added to an AlwaysOn availability group, but they could not be added using the New Availability Group wizard. Additionally, in the event of a failover, the encrypted database data could not be accessed. This is because the database master key in the availability group database was not compatible with the service master key on the other SQL Server instances hosting replicas for that availability group.
For more information on how to add encrypted databases to an availability group and make those databases highly available on all replicas in SQL Server 2012 or SQL Server 2014, see the AlwaysOnPro blog:
How to enable TDE Encryption on a database in an Availability Group
SQL Server 2016 adds support to the availability group wizard for adding encrypted databases and simplifies database access following availability group failover.
NOTE This improvement is part of support for adding the SQL Server Integration Services database (SSISDB) to an AlwaysOn availability group for high availability. For more information see
New Availability Group Wizard Adds Support for Encrypted Database
In SQL Server 2016, if you select an encrypted database for inclusion in an availability group, the New Availability Group wizard detects that the database is defined with a database master key and prompts the administrator for the database master key password.
After adding the database with a database master key, the wizard will verify the password during validation. When clicking Finish, credentials are created on each replica using the password of the database master key.
Added Support for Accessing Encrypted Availability Group Database After Failover
On creation, a database's database master key is encrypted using the SQL Server service master key, which is unique to that SQL Server instance. When a database defined with a database master key is added to an availability group, upon failover to another replica, decryption is not possible for that database when brought online in the primary role on a different SQL Server instance with an incompatible service master key.
When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails (which in the case following failover, would occur), SQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.
Beginning in SQL Server 2016, when you create an availability group containing an database containing a database master key, the New Availability Group Wizard executes procedure sp_control_dbmasterkey_password against each replica to create these credentials associated with the database master key.
EXEC sp_control_dbmasterkey_password @db_name = @dbName, @password = @pwd, @action = N''add''',N'@dbName nvarchar(9),@password nvarchar(20)',@dbName=N'encryptDB',@password=N'1a2b3c4d5e6f7g8h9i0!'
In the event that this replica becomes the primary, and the database master key must be decrypted, SQL Server will first attempt to decrypt the key with the SQL Server instance service master key which will fail. Next, SQL Server will search for and find these credentials that can decrypt the database master key.
You can view the new credentials by querying sys.credentials and sys.master_key_passwords.
More Information About the Relationship between the SQL Server service master key and the database master key
SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database. The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key. The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database.
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in both the database and in master. Typically, the copy stored in master is silently updated whenever the master key is changed.
Database Master Key credentials
When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails, SQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.
In prior versions of SQL Server, if a database contain some encrypted columns protected by database master key and the database is added into an AlwaysOn availability group, the database in the new primary node cannot decrypt the database master key automatically after failover. In order to encrypt/decrypt data on the new primary node, you have to use OPEN MASTER KEY statement explicitly to open the database master key.
As the above picture show, When the database master key is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password. To enable the automatic decryption of the database master key, a copy of the key is encrypted by using the Service Master Key (SMK) and stored in both the database and in master database.
Since the Service Master Key is generated for a SQL Server instance, SQL Server Instance A and SQL Server Instance B have different service master key.
When failover occurs with the database, the secondary node (Node 2) became the primary node of the availability group, but the copy of database master key in Node 2’s database is encrypted by Service Key A instead of Service Key B. Thus after failover, the database in SQL Server Instance B cannot decrypt the database master key automatically.
For more information see the following resources:
For more information, please see
SQL Server Encryption SQL Server and Database Encryption Keys (Database Engine) CREATE MASTER KEY (Transact-SQL) How to enable TDE Encryption on a database in an Availability Group sp_control_dbmasterkey_password (Transact-SQL)
Comments
- Anonymous
August 27, 2016
This would be great if it were working.... - Anonymous
February 13, 2019
I have found that when running the "Add Database to Availability Group" wizard in for SSISDB in SSMS, even though it allows me to enter the Master Key Password in the Password field, it skips the steps of adding the Database Credentials to the replicas. I have not been able to find a sequence where this does work in the wizard. However if I manually run:EXEC sp_control_dbmasterkey_password @db_name = N'SSISDB', @password = N'MySSISCatalogPassword', @action = N'add'; ... against each replica after the SSISDB is joined to the Availability Group, then it automatically fixes the SSISDB master key errors I was previously getting from SSIS jobs after fail over. - Anonymous
March 14, 2019
Agree with Don Ferguson. I faced the same issue for SSISDB AlwaysON.Running "EXEC sp_control_dbmasterkey_password"on both the replica fixed the issue . Is it a bug ? - Anonymous
June 12, 2019
There appears to be a bug with the SSMS, if you select:'Full Database and Log Backup', it works as expected, and the credentials are created on the replicas.'Automatic Seeding' to add the database, it does not run sp_control_dbmasterkey_password.If Automatic Seeding is selected, in the validation check "Checking password of the database master key", it says it has skipped it because: "Skipped Checking password of the database master key because you skipped initial data synchronization."Bug raised, to vote for fix:https://feedback.azure.com/forums/908035-sql-server/suggestions/37896418-ag-wizard-does-not-run-sp-control-dbmasterkey-pass