By default, the Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support databases that are already encrypted: see Encrypted Databases with AlwaysOn Availability Groups (SQL Server) .
If you have a database that is already encrypted, it can be added to an existing Availability Group – just not through the wizard. You’ll need to follow the procedures outlined in Manually Prepare a Secondary Database for an Availability Group .
This article discusses how TDE encryption can be enabled for a database that already belongs to an Availability Group. After a database is already a member of an Availability Group, the database can be configured for TDE encryption but there are some key steps to do in order to avoid errors.
To follow the procedures outlined in this article you need:
For this configuration, there are two servers:
SQL1 – the primary replica instance, and
SQL2 – the secondary replica instance.
To determine if an instance has a DMK, issue the following query:
USE MASTER
GO
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
If a record is returned, then a DMK exists and you do not need to create one, but if not, then one will need to be created. To create a DMK, issue the following TSQL on each replica instance that does not have a DMK already:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mhl(9Iy^4jn8hYx#e9%ThXWo*9k6o@';
Notes:
To have a Database Encryption Key (DEK) that will be used to enable TDE on a given database, it must be protected by a Server Certificate. To create a Server Certificate issue the following TSQL command on the primary replica instance ( SQL1 ):
USE MASTER
GO
CREATE CERTIFICATE TDE_DB_EncryptionCert
WITH SUBJECT = 'TDE Certificate for the TDE_DB database'
To validate that the certificate was created, you can issue the following query:
SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates
which should return a result set similar to:
The thumbprint will be useful because when a database is encrypted, it will indicate the thumbprint of the certificate used to encrypt the Database Encryption Key. A single certificate can be used to encrypt more than one Database Encryption Key, but there can also be many certificates on a server, so the thumbprint will identify which server certificate is needed.
Once the server certificate has been created, it should be backed up using the BACKUP CERTIFICATE TSQL command (on SQL1 ):
USE MASTER
BACKUP CERTIFICATE TDE_DB_EncryptionCert
TO FILE = 'TDE_DB_EncryptionCert'
WITH PRIVATE KEY (FILE = 'TDE_DB_PrivateFile',
ENCRYPTION BY PASSWORD = 't2OU4M01&iO0748q*m$4qpZi184WV487')
The BACKUP CERTIFICATE command will create two files. The first file is the server certificate itself. The second file is a “private key” file, protected by a password. Both files and the password will be used to restore the certificate onto other instances.
When specifying the filenames for both the server certificate and the private key file, a path can be specified along with the filename. If a path is not specified with the files, the file location where Microsoft SQL Server will save the two files is the default “data” location for databases defined for the instance. For example, on the instance used in this example, the default data path for databases is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA”.
Note:
If the server certificate has been previously backed up and the password for the private key file is not known, there is no need to panic. Simply create a new backup by issuing the BACKUP CERTIFICATE command and specify a new password. The new password will work with the newly created files (the server certificate file and the private key file).
The previous TSQL command created two files – the server certificate (in this example: “TDE_DB_EncryptionCert”) and the private key file (in this example: “TDE_DB_PrivateFile”) The second file being protected by a password.
These two files along with the password should then be used to create the same server certificate on the other secondary replica instances.
After copying the files to SQL2 , connect to a query window on SQL2 and issue the following TSQL command:
CREATE CERTIFICATE TDE_DB_EncryptionCert
FROM FILE = '<path_where_copied>\TDE_DB_EncryptionCert'
WITH PRIVATE KEY
( FILE = '<path_where_copied>\TDE_DB_PrivateFile',
DECRYPTION BY PASSWORD = 't2OU4M01&iO0748q*m$4qpZi184WV487')
This installs the server certificate on SQL2 . Once the server certificate is installed on all secondary replica instances, then we are ready to proceed with encrypting the database on the primary replica instance ( SQL1 ).
On the primary replica instance ( SQL1 ) issue the following TSQL command to create the Database Encryption Key.
USE TDE_DB2
go
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_DB_EncryptionCert
The DEK is the actual key that does the encryption and decryption of the database. When this key is not in use, it is protected by the server certificate (above). That is why the server certificate must be installed on each of the instances. Because this is done inside the database itself, it will be replicated to all of the secondary replicas and the TSQL does not need to be executed again on each of the secondary replicas.
At this point the database is NOT YET encrypted – but the thumbprint identifying the server certificate used to create the DEK has been associated with this database. If you run the following query on the primary or any of the secondary replicas, you will see a similar result as shown below:
SELECT db_name(database_id), encryption_state,
encryptor_thumbprint, encryptor_type
FROM sys.dm_database_encryption_keys
Notice that TempDB is encrypted and that the same thumbprint (i.e. Server Certificate) was used to protect the DEK for two different databases. The encryption state of TDE_DB2 is 1, meaning that it is not encrypted yet.
We are now ready to turn on encryption. The database itself as a database encryption key (DEK) that is protected by the Server Certificate. The server certificate has been installed on all replica instances. The server certificate itself is protected by the Database Master Key (DMK) which has been created on all of the replica instances. At this point each of the secondary instances is capable of decrypting (or encrypting) the database, so as soon as we turn on encryption on the primary, the secondary replica copies will begin encrypting too.
To turn on TDE database encryption, issue the following TSQL command on the primary replica instance ( SQL1 ):
ALTER DATABASE TDE_DB2 SET ENCRYPTION ON
To determine the status of the encryption process, again query sys.dm_database_encryption_keys :
When the encryption_state = 3, then the database is encrypted. It will show a status of 2 while the encryption is still taking place, and the percent_complete will show the progress while it is still encrypting. If the encryption is already completed, the percent_complete will be 0.
At this point, you should be able to fail over the Availability Group to any secondary replica and be able to access the database without issue.
The database will quit synchronizing and possibly report “suspect” on the secondary. This is because when the SQL engine opens the files and begins to read the file, the pages inside the file are still encrypted. It does not have the decryption key to decrypt the pages. The SQL engine will think the pages are corrupted and report the database as suspect. You can confirm this is the case by looking in the error log on the secondary. You will see error messages similar to the following:
2014-01-28 16:09:51.42 spid39s Error: 33111, Severity: 16, State: 3.
2014-01-28 16:09:51.42 spid39s Cannot find server certificate with thumbprint '0x48CE37CDA7C99E7A13A9B0ED86BB12AED0448209'.
2014-01-28 16:09:51.45 spid39s AlwaysOn Availability Groups data movement for database 'TDE_DB2' has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
2014-01-28 16:09:51.56 spid39s Error: 3313, Severity: 21, State: 2.
2014-01-28 16:09:51.56 spid39s During redoing of a logged operation in database 'TDE_DB2', an error occurred at log record ID (31:291:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
The error messages are quite clear that the SQL engine is missing a certificate – and it’s looking for a specific certificate – as identified by the thumbprint. If there is more than one server certificate on the primary, then the one that needs to be installed on the secondary is the one whose thumbprint matches the thumbprint in the error message.
The way to resolve this situation is to go back to step three above and the back up the certificate from SQL1 (whose thumbprint matches) and then create the server certificate on SQL2 as outlined in step four. Once the server certificate exists on the secondary replica instance ( SQL2 ), then you can issue the following TSQL command on the secondary ( SQL2 ) to resume synchronization:
ALTER DATABASE TDE_DB2 SET HADR RESUME
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.