Transparent Data Encryption (TDE) in Microsoft SQL Server Databases allows you to protect sensitive data in tables by encrypting them when they are stored on media. The data is transparently decrypted for authorized users or applications when they access the data. See Transparent Data Encryption for more information.
This article describes how to integrate SDKMS to be used with SQL Server TDE. The steps below have been tested for MSSQL version: 2014.
Before starting, download the SDKMS client installer for Windows 4-bit from Resources.
Encryption Hierarchy
SQL Server encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. Asymmetric keys and symmetric keys can be stored outside of SQL Server in an Extensible Key Management (EKM) module.
The following illustration shows that each layer of the encryption hierarchy encrypts the layer beneath it and displays the most common encryption configurations. The access to the start of the hierarchy is usually protected by a password.
Figure 1: Encryption Hierarchy
Keep in mind the following concepts:
For best performance, encrypt data using symmetric keys.
Database master keys are protected by the Service Master Key. The Service Master Key is created by SQL Server setup and is encrypted with the Windows Data Protection API (DPAPI).
Other encryption hierarchies stacking additional layers are possible.
An Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside of SQL Server.
Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by API Key protected by the database master key of the master database, or by an asymmetric key stored in an EKM.
The Service Master Key and all Database Master Keys are symmetric keys.
The following illustration shows the same information in an alternative manner.
Figure 2: Encryption Hierarchy
Database TDE Key Management
Contents
What is DEK?
What is Master Key?
How it is securely stored?
How is it managed inside a database?
Before enabling TDE, a DEK must be created which is used to encrypt the contents of the database. It is a symmetric key and supported algorithms are AES with 128-bit, 192bit, or 256bit keys or 3 Key Triple DES. Once TDE is enabled on a database, the DEK is used to encrypt the contents of the database and the log. When TDE is enabled for any database on the server, TempDB is also encrypted and its DEK is managed internally by SQL Server.
TDE also requires creating an EKM master key which is created on SDKMS using EKM module. Recommended algorithm for master key is RSA 2048 or higher. This EKM Key is used to encrypt the service master key and other database keys. See the diagram below for key hierarchy.
Figure 3: Key Management
DEK Storage
Database encryption key is stored inside the database boot page; the contents of this boot page are not encrypted so the DEK has to be encrypted by another key; we call it the DEK's encryptor. Currently SQL Server allows encrypting a DEK by SDKMS Asymmetric key. Besides the DEK, the boot page also contains other information necessary to identify and open an encrypted database.
DEK's Encryptor
In case of an EKM key, the Asymmetric key resides on the SDKMS end which makes management a little easier. In either case it is important to hold on to this encryptor as long as the database or the log is dependent on it.
When you restore or attach a TDE database on another server make sure that the encryptor is present on this server as well. In case of an EKM key, the provider and the key should be available on this server as well.
Before you Begin
Limitations and Restrictions
You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. That user must be able to be authenticated by the EKM module.
Upon startup the Database Engine must open the database. To do this, you should create a credential that will be authenticated by the EKM and add it to a login that is based on an asymmetric key. Users cannot sign in using that login, but the Database Engine will be able to authenticate itself with the EKM device.
If the asymmetric key stored by EKM Provider (SDKMS) is lost, the database will not be able to be opened by SQL Server. Hence, it is recommended to never delete/edit SQL Server managed keys from SDKMS manually. Even after key rotation, its recommended to keep the old keys, so that older backups can be used in contingency scenario.
Access to install the Fortanix KMS Server file to configure it on the machine and user.
Security
Permissions
This article uses the following permissions:
To change a configuration option and run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
Requires ALTER ANY CREDENTIAL permission.
Requires ALTER ANY LOGIN permission.
Requires CREATE ASYMMETRIC KEY permission.
Requires CONTROL permission on the database to encrypt the database.
Installation
FortanixKmsClient.msi installs the SDKMS CNG Provider, as well as the EKM provider and the PKCS#11 library.
Configuration for Windows Client
The Fortanix KMS Server URL and proxy information are configured in the Windows registry for the local machine or current user with C:\Program Files\Fortanix\KmsClient\FortanixKmsClientConfig.exe.
The machine key store uses the local machine configuration, and the user key store uses the current user configuration.
For example, to configure the Fortanix KMS Server URL for the local machine, run:
To configure the Fortanix KMS Server URL for the current user, run:
FortanixKmsClientConfig.exe user --api-endpoint https://sdkms.fortanix.com
To configure proxy information, add --proxy http://proxy.com or --proxy none to unconfigure proxy.
Configuration for SQL Server
To Configure SQL Server to use Fortanix EKM Provider, run the following commands in SQL Server Studio:
Use the correct location of the EKM DLL.
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
Figure 4: Create cryptographic provider
In case of error such as "Extensible key management is not supported or enabled in this edition of SQL server":
Figure 5: Error Scenario
Run the following commands:
sp_configure 'show advanced', 1 GO RECONFIGURE GO sp_configure 'EKM provider enabled', 1 GO RECONFIGURE GO
Figure 6: Run commands for error scenario
Now, re-run the following command:
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
Create a credential that will be used by the system administrators. See the Getting Started guide on how to generate an API Key.
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
Click COPY API KEY to copy the API key of your application, and then paste the key in the SECRET field of the command above.
Figure 7: Copy API key
Run the command again to create a credential using the copied API key in your SQL Studio Server.
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = 'NjUyMTg1OGMtNmM5Zi00ODFjLWEzNWEtNWUyYWFhZWViOTM5OlZoUzdjanhEa3d6RmltYVZMQWloUFZWZ0Z6VVJGbkx2NmhYQkNzY2VmTnR4MkhINkhYLUlEeWtYelk0cWh2YWhGZmF1emFUQ1ZRdkI2b0dOQzk5ZHVB'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
Figure 8: Create credential
Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login].
ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator
ADD CREDENTIAL sa_ekm_tde_cred ;
GO
In case there is no domain and the machine is part of a workgroup/standalone, please run the commands below:
ALTER LOGIN MSSQL2017 ADD CREDENTIAL "sa_ekm_tde_cred";
GO
Figure 9: Command for no domain
In case you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below.
Figure 10: Map credentials
Create an asymmetric key stored inside the EKM provider.
USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO
Figure 11: Create Asymmetric key
Create a credential that will be used by the Database Engine.
USE master ;
CREATE CREDENTIAL ekm_tde_cred
WITH IDENTITY = 'Identity2'
, SECRET = '<API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
Figure 12: Create credential for database engine
Add a login used by TDE and add the new credential to the login.
CREATE LOGIN EKM_Login
FROM ASYMMETRIC KEY ekm_login_key ;
GO
ALTER LOGIN EKM_Login
ADD CREDENTIAL ekm_tde_cred ;
GO
Figure 13: Add the new credential to the login
Create Database employee.
CREATE DATABASE employee
Create table employee.
USE employee CREATE TABLE employee (first_name VARCHAR(128),last_name VARCHAR(128),empID DECIMAL,salary DECIMAL(6));
GO
Figure 14: Create Table
Create the database encryption key that will be used for TDE.
USE employee CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO
Figure 15: Create Database encryption key
Alter the database to enable transparent data encryption.
ALTER DATABASE employee
SET ENCRYPTION ON ;
GO
Figure 16: Enable transparent data encryption
Monitor TDE Progress:
SQL Server keeps track of the encryption progress and we can pull that information by querying sys.dm_database_encryption_keys. Particularly ‘Percent_Complete’ and ‘encryption_state’ are the two columns which are required to understand the progress of TDE. ‘Encryption_state’ column returns an integer value (0-6) which indicates the encryption status of the database and ‘percent_complete’ column tells us the percent completed of the DB encryption state change.
Encryption_state(int)
Description
0
No database encryption key present, no encryption
1
Unencrypted
2
Encryption in progress
3
Encrypted
4
Key change in progress
5
Decryption in progress
6
Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed)
Below T-SQL statement can be used to monitor TDE progress/status.
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, encryption_state_desc = CASE encryption_state WHEN '0' THEN 'No database encryption key present, no encryption' WHEN '1' THEN 'Unencrypted' WHEN '2' THEN 'Encryption in progress' WHEN '3' THEN 'Encrypted' WHEN '4' THEN 'Key change in progress' WHEN '5' THEN 'Decryption in progress' WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
The output of above query comes really handy to manage TDE, Now let’s move on to managing TDE.
Rotating Keys for Transparent Data Encryption (TDE) in Fortanix
Fortanix recommends updating your TDE security keys regularly by rotating the available symmetric and asymmetric encryption keys. Execute the following from the SQL query window.
Rotate keys for TDE
Generate an asymmetric key using the Fortanix External Key Manager (EKM) Provider.
Use master; CREATE ASYMMETRIC KEY SQL_EKM_KEY_v7 FROM Provider EKM_Prov WITH ALGORITHM = RSA_2048, PROVIDER_KEY_NAME = 'SQL_EKM_KEY_v7', CREATION_DISPOSITION=CREATE_NEW
Where SQL_EKM_KEY_v7 is the new rotation key.
Figure 17: Rotate Keys
Create a CREDENTIAL for EKM Provider created by Fortanix.
CREATE CREDENTIAL <Name of credential> WITH IDENTITY='<Name of EKM User>', SECRET='<SDKMS API KEY>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
Figure 18: Create Credential for EKM Provider
Where Credential and Identity can be as per your internal organization policy.
Create a login based on the recently created asymmetric key.
CREATE LOGIN <Name of login> FROM ASYMMETRIC KEY SQL_New Rotation key;
Figure 19: Create Login for Asymmetric Key
Map the credential to the recently created login.
ALTER LOGIN <Name of Login> ADD CREDENTIAL <Name of credential>;
Use employee; ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_128 ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_New Rotation key; go SELECT * FROM sys.dm_database_encryption_keys go
Where employee is the Database name.
Figure 21: Enable TDE Key Rotation
Using Extensible Key Management on a SQL Server Failover Cluster
This section focuses on the preparation of the environment for 2-node SQL Server Cluster in Windows Server
Setting up a Shared Storage To set up a shared storage disk for SQL Server Cluster, refer to the configuration procedures that apply for your shared storage solution. Plan the size of the shared storage depending on the number of certificates that you are enrolling.
Once the cluster is up and running, install Fortanix client on both the nodes.
Configure and setup the appliance on both the nodes.
Install Fortanix EKM client on both the nodes.
Configure the Fortanix EKM provider on both the nodes.
Open the SQL Server Management Studio to register the Fortanix EKM provider on the first node.
Setup the credentials on the first node.
Now create some keys using the Fortanix EKM provider on the first node.
Create a table and encrypt some column with the Fortanix EKM key with the first node.
Shutdown the first node.
Now log in to the second node and decrypt the data encrypted on the first node.
Data is decrypted successfully.
Extensible Key Management using Fortanix EKM is now working fine on a SQL Server cluster.
Using TDE Within an Always On Availability Group
These procedures have been tested for an availability group that used two servers. Server 1 held a (nominal) primary replica, Server 2 held a (nominal) secondary replica. Primary and secondary replicas were read/write. The configuration used Fortanix HSMs, and no shared disk. Each server could be logged into directly, or through a cluster availability group (virtual) address. The configuration also required a third server to act as Remote File System (RFS).
Setting up and Switching on TDE
NOTE: The MSSQL Server Studio Add Database Wizard (versions to SQL Server 2014) will not support addition of a database that is already encrypted, or that includes a database encryption key even if encryption is switched off. However, you may set up TDE encryption for an existing non-encrypted database that is already within an availability group using T-SQL, as described in the steps below.
SQL Server (versions to SQL Server 2014) may not support a readable secondary using a clustered column store index within the context of availability group failover.
The following steps should be performed for each database, the primary, and each secondary, that is part of the availability group, and for which you wish to switch on TDE encryption. Before starting, it is assumed that the database you wish to encrypt:
Already exists
Is already part of an availability group within a cluster
Is NOT currently encrypted, and includes no database encryption key (TDEDEK)
Has never been encrypted before. If it has, you may see errors and a request for a log backup
NOTE: In the examples shown here, the database to be encrypted is called SourceDatabase, and the database wrapping key is called SQL_Server_Key2 in the SQLEKM provider, and ekm_login_key1 in the master database. Change names or other parameters to your own requirements. Also, these steps assume that a wrapping key of the same name does not already exist in either the SQLEKM provider or the master database.
Before proceeding with the following steps:
Make sure your database is recently backed up.
Make sure that primary and secondary replicas are synchronized within the availability group, and that failover can occur without any data loss.
If you prefer a particular server for the primary role, then you are failed over to that server.
You should also remember the roles (primary/secondary) that each server node starts with.
Perform the following steps in the order shown. The following description is written as if the server nodes retain the initial primary or secondary roles they begin with. You can use the availability group cluster virtual address, and manually failover between the nodes in order to access them but bear in mind this description refers to the initial (starting) role of each node, even if its actual role changes later.
Before you proceed, make sure that the Primary and Secondary database are in synchronized state as shown below.
Figure 22: SQL Server Management Studio
Now install the Fortanix EKM provider in the Windows machine and set up the endpoints. The Fortanix KMS Server URL and proxy information are configured in the Windows registry for the local machine or current user with C:\Program Files\Fortanix\KmsClient\FortanixKmsClientConfig.exe. The machine key store uses the local machine configuration, and the user key store uses the current user configuration. For example, to configure the Fortanix KMS Server URL for the local machine, run: For Machine:
Click COPY API KEY to copy the API key of your application, and then paste the key in the SECRET field of the command above.
Figure 27: Get API Key
Run the command again to create a credential using the copied API key in your SQL Studio Server.
CREATE CREDENTIAL sa_ekm_tde_cred WITH IDENTITY = 'Identity1', SECRET = ''NjUyMTg1OGMtNmM5Zi00ODFjLWEzNWEtNWUyYWFhZWViOTM5OlZoUzdjanhEa3d6RmltYVZMQWloUFZWZ0Z6VVJGbkx2NmhYQkNzY2VmTnR4MkhINkhYLUlEeWtYelk0cWh2YWhGZmF1emFUQ1ZRdkI2b0dOQzk5ZHVB' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
Figure 28: Create Credential using API
Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login].
ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator ADD CREDENTIAL sa_ekm_tde_cred ; GO
In case there is no domain and the machine is part of a workgroup/standalone, please run the commands below:
ALTER LOGIN MSSQL2017 ADD CREDENTIAL sa_ekm_tde_cred ; GO
Figure 29: No Domain Scenario
In case you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below.
Figure 30: Map Credentials
On Primary: Set up the database wrapping key, TDE credential and then log in:
Make sure you are running this on the PRIMARY.
This script sets up a TDE wrapping key, login and credential on the primary.
Create wrapping key.
USE master CREATE ASYMMETRIC KEY SQL_Server_Key2 FROM PROVIDER EKM_Prov WITH PROVIDER_KEY_NAME= 'ekm_login_key1', CREATION_DISPOSITION = CREATE_NEW, ALGORITHM = RSA_2048; GO
Create wrapping key credential.
Use master CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY SQL_Server_Key2; CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'Identity1', SECRET = '0O*dG0ffz2' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov; ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
On (each) Secondary: Restart the SQL Server instance. Set up the database wrapping key, TDE credential and then log in.
Make sure you are running this on the SECONDARY.
NOTE: the wrapping key must already exist, as created by the primary.
This script opens a wrapping key, TDE login and credential on the secondary.
The credential must match the primary.
Create the wrapping key.
USE master CREATE ASYMMETRIC KEY SQL_Server_Key2 FROM PROVIDER EKM_Prov WITH PROVIDER_KEY_NAME='ekm_login_key1', CREATION_DISPOSITION = OPEN_EXISTING; --Wrapping key should already have been created on the primary. GO
Now we will sync the wrapping key credential on the secondary as created in the Primary.
Use master CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY SQL_Server_Key2; CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'Identity1', SECRET = '0O*dG0ffz2' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov; ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
On both primary and secondary, check that the database remains synchronized. To do this, on the SQL Server Management Studio, look at the [Server name] → [Name of your database]. If after the previous steps you find that the database is now ‘Not Synchronized’, re-synchronize by running the following query:
Run on the primary/secondary that appears to be unsynchronized with availability group.
USE master; GO ALTER DATABASE [SourceDatabase] SET HADR RESUME
If the database remains unsynchronized after performing this step, then you may have configuration problems. Try to correct this before proceeding.
On Primary: Create the database encryption key and switch on the TDE encryption.
Make sure you are running this on PRIMARY.
Create the actual database encryption key (TDEDEK).
USE SourceDatabase; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_Server_Key2; GO
A short delay may be required here before switching on encryption.
WAITFOR DELAY '00:00:05';
Set delay period as required. One second = '00:00:01'
Break any connection with the SourceDatabase so that encryption can commence.
USE [master]; GO
Enable TDE (switch on encryption) on the SourceDatabase:
ALTER DATABASE SourceDatabase SET ENCRYPTION ON; GO
Key Rotation on SQL Always On Availability Group
On Primary
On Primary, run the command below.
use master; CREATE ASYMMETRIC KEY SQL_EKM_KEY_v1 FROM Provider EKM_Prov WITH ALGORITHM = RSA_2048, PROVIDER_KEY_NAME = 'SQL_EKM_KEY_v1', CREATION_DISPOSITION=CREATE_NEW
Figure 31: Create New SQL EKM Key
Where “SQL_EKM_KEY_v1” is the new key are rotation.
Create a CREDENTIAL for EKM Provider created by Fortanix.
CREATE CREDENTIAL <Name of credential> WITH IDENTITY='<Name of EKM User>', SECRET='<SDKMS API KEY>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
Figure 32: Create Credential for EKM Provider
Where the New Credential is “New Rotation” and tagged with Identity as “New Rotation”.
Create a login based on the recently created asymmetric key.
CREATE LOGIN <Name of login> FROM ASYMMETRIC KEY SQL_EKM_KEY_v1;
Figure 33: Create Login for the Asymmetric Key
Map the credential to the recently created login.
ALTER LOGIN <Name of Login> ADD CREDENTIAL <Name of credential>;
On Primary, go to Object Explorer, Click Always On Availability group, select Availability Database and then select the Database, and suspend the Data movement as shown below.
Figure 34: Suspend Data Movement
Configure Access on an Availability Replica
In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
Expand the Always On High Availability node and the Availability Groups node.
Click the availability group whose replica you want to change.
Right-click the availability replica, and then click Properties.
In the Availability Replica Properties dialog box, you can change the connection access for the primary role and for the secondary role, as show in Figure 35.
For the secondary role, select a new value from the Readable secondary drop list, as follows:
Yes All connections are allowed secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access. Please follow the screenshot below:
Figure 35: Secondary Database
Use AutoHa-sample; ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_128 ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_KEY_v1; GO SELECT * FROM sys.dm_database_encryption_keys GO
The Server Asymmetric Key will be rotated.
Run the below the command to check the status of the key as per the screenshot below.
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, encryption_state_desc = CASE encryption_state WHEN '0' THEN 'No database encryption key present, no encryption' WHEN '1' THEN 'Unencrypted' WHEN '2' THEN 'Encryption in progress' WHEN '3' THEN 'Encrypted' WHEN '4' THEN 'Key change in progress' WHEN '5' THEN 'Decryption in progress' WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
Figure 36: Check the Status of the Key
On Secondary
Reopen the new key created on Primary.
USE master CREATE ASYMMETRIC KEY SQL_EKM_KEY_v1 FROM PROVIDER EKM_Prov WITH PROVIDER_KEY_NAME='SQL_EKM_KEY_v1', CREATION_DISPOSITION = OPEN_EXISTING; GO
Figure 37: Reopen the new Key
To create the same credential as per Primary.
CREATE CREDENTIAL <Name of credential> WITH IDENTITY='<Name of EKM User>', SECRET='<SDKMS API KEY>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
Figure 38: Create Same Credential as Primary
Create a new login on Secondary.
CREATE LOGIN <Name of login> FROM ASYMMETRIC KEY SQL_EKM_KEY_v1;
Figure 39: Create New Login on Secondary
Once Encryption key is regenerated on Primary, run the below query on secondary to check the status if the key has been rotated/altered.
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, encryption_state_desc = CASE encryption_state WHEN '0' THEN 'No database encryption key present, no encryption' WHEN '1' THEN 'Unencrypted' WHEN '2' THEN 'Encryption in progress' WHEN '3' THEN 'Encrypted' WHEN '4' THEN 'Key change in progress' WHEN '5' THEN 'Decryption in progress' WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
Figure 40: Check the Status of the key on Secondary
Troubleshooting
If your database has previously been encrypted, you may see errors at this point. If you are asked to take a pending log backup, then take backup using the below methods:
Using command:
BACKUP LOG employee TO DISK = 'C:\employee.TRN'
GO
This will create a transaction log backup of the employee database and write the backup contents to file "C:\employee.TRN". The .TRN extension is commonly used for identifying that the backup is a transaction log backup.
Using SQL Server Management Studio:
Right click the database name.
Select Tasks > Backup.
Select Transaction Log as the backup type.
Select Disk as the destination.
Click Add... to add a backup file and type "C:\company.TRN" and click OK.
Click OK again to create the backup.
Figure 41: SQL Server Management Studio
If you get an error requesting that you take a log backup, try adapting the following code to your own requirements, and then run it.
USE master; GO ALTER DATABASE <Name-of-your-database> SET RECOVERY FULL; GO USE master; GO
NOTE: You should have provided a path to your backups when setting up your availability group.
EXEC sp_addumpdevice 'disk', '<Name-of-your-device>', ‘<Path-to-your-backups>\<Name-of-your-log-backup-file>'; GO
Back up the log
BACKUP LOG <Name-of-your-database> TO <Name-of-your-device>; GO
Drop backup device
EXEC sp_dropdevice '<Name-of-your-device>';
Example:
USE master; GO ALTER DATABASE SourceDatabase SET RECOVERY FULL; GO USE master; GO EXEC sp_addumpdevice 'disk', 'EncryptedSourceDatabaseBackupLog', '\\Server-2\NetWorkShareFolder\SourceDatabase_20160210122459'; GO
Back up the log
BACKUP LOG SourceDatabase TO EncryptedSourceDatabaseBackupLog; GO
tab: https://sdkms.fortanix.com/#/apps
NOTE: The MSSQL Server Studio Add Database Wizard (versions to SQL Server 2014) will not support addition of a database that is already encrypted, or that includes a database encryption key even if encryption is switched off. However, you may set up TDE encryption for an existing non-encrypted database that is already within an availability group using T-SQL, as described in the steps below.
