1.0 Overview
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 Fortanix Data Security Manager (DSM) to be used with SQL Server TDE. The steps below have been tested for MSSQL version: 2014.f
Before starting, download the Fortanix DSM client installer for Windows 64-bit from Resources.
2.0 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 the 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
3.0 Database TDE Key Management
3.1 Contents
- What is DEK?
- What is Master Key?
- How is it 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 Fortanix DSM using the EKM module. The recommended algorithm for the 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 the key hierarchy.
Figure 3: Key Management
3.2 DEK Storage
The 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 Fortanix DSM Asymmetric key. Besides the DEK, the boot page also contains other information necessary to identify and open an encrypted database.
3.3 DEK's Encryptor
In the case of an EKM key, the Asymmetric key resides on the Fortanix DSM 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.
4.0 Before you Begin
4.1 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 (Fortanix DSM) 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 Fortanix DSM manually. Even after key rotation, it is recommended to keep the old keys, so that older backups can be used in contingency scenarios.
- Access to install the Fortanix KMS Server file to configure it on the machine and user.
5.0 Security
5.1 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.
6.0 Installation
FortanixKmsClient.msi
installs the Fortanix DSM CNG Provider, as well as the EKM provider and the PKCS#11 library.
6.1 Installation Prerequisites
If Extensible Key Management is not supported or enabled in the SQL server edition, then run the following commands:
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO
Figure 4: Enable EKM
6.2 Windows Server Client Configuration
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:
FortanixKmsClientConfig.exe machine --api-endpoint https://sdkms.fortanix.com
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.
6.3 SQL Server Configuration
To Configure SQL Server to use Fortanix EKM Provider, run the commands in Section 6.3.1 in SQL Server Studio.
6.3.1 Create Cryptographic Provider
- Use the correct location of the
EKM DLL
.CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ; GO
Figure 5: Create cryptographic provider
6.3.2 Create Credential
- Get the API Key:
- Go to https://sdkms.fortanix.com/#/
- In the UI left panel, click the Apps
tab: https://sdkms.fortanix.com/#/apps
- 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 6: Copy API key</strong
- Create a credential using the copied API key in your SQL Studio Server that will be used by the system administrators.
CREATE CREDENTIAL sa_ekm_tde_cred WITH IDENTITY = 'Identity1', SECRET = '<DSM API Key>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
Figure 7: Create Credentials - 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
ALTER LOGIN LOCALHOST\Administrator
ADD CREDENTIAL "sa_ekm_tde_cred"; GO
Figure 8: Command for no domain If you are not an administrator and hence you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below.
Figure 9: 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 10: 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 = '<DSM API Key>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
Figure 11: 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 12: 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 13: 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 14: Create Database encryption key - Alter the database to enable transparent data encryption.
ALTER DATABASE employee SET ENCRYPTION ON ; GO
Figure 15: 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 that 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) 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
7.0 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.
7.1 Rotate keys for TDE
To rotate the keys for TDE, you must be logged in as a service account or system admin user to create the master key.
- Create the credentials. To know the procedure, follow the steps 1 to 3 mentioned in section 6.3.2 Create Credentials.
- Create a CREDENTIAL for EKM Provider created by Fortanix.
USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key_v2
FROM PROVIDER EKM_Prov
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_Server_Key_v2' ;
GOWhere
SQL_EKM_KEY_v7
is the new rotation key.Figure 16: Rotate Keys in Fortanix DSM
Figure 17: Rotate Keys in the Database
- Create a
CREDENTIAL
for EKM Provider created by Fortanix.USE master ;
GO
CREATE CREDENTIAL db_ekm_tde_cred_v2
WITH IDENTITY = 'Identity_v2',
SECRET = '{key}'
FOR CRYPTOGRAPHIC PROVIDER EKM_ProvFigure 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 EKM_Login_v2
FROM ASYMMETRIC KEY ekm_login_key_v2 ;
GO
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>;
Figure 20: Map Credentials - Rotate the master key for Transparent Database Encryption Key.
USE employee;
Where
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key_v2;employee
is the Database name.
Figure 21: Enable TDE Key Rotation
Figure 22: Old Key
Figure 23: New Key
8.0 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.
- Refer to SQL Server documentation to install a failover cluster.
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 the Fortanix client on both nodes.
- Configure and set up the appliance on both nodes.
- Install Fortanix EKM client on both nodes.
- Configure the Fortanix EKM provider on both 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 columns with the Fortanix EKM key with the first node.
- Shut down 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.
9.0 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).
9.1 Setting up and Switching on TDE
- 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
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 is in a synchronized state as shown below.
Figure 24: 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:
FortanixKmsClientConfig.exe machine --api-endpoint https://sdkms.fortanix.com
For User:
FortanixKmsClientConfig.exe user --api-endpoint https://sdkms.fortanix.com
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 25: Configure SQL Server
In case of an error such as "Extensible key management is not supported or enabled in this edition of SQL server":
Figure 26: SQL Server Error
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO
Figure 27: Reconfigure SQL Server
Re-run the following command to add a cryptographic provider.
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
Figure 28: Add Cryptographic Provider
Create a credential that will be used by the system administrators. See the Fortanix DSM Getting Started guide on how to generate an API Key.
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<DSM API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
For getting the API Key:
- Go to https://sdkms.fortanix.com/#/
- In the UI left panel, click the Apps mceclip1.png tab: https://sdkms.fortanix.com/#/apps
- 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 29: 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 = '<DSM API KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
Figure 30: 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 EC2AMAZ-1RDPAEU\Administrator
ADD CREDENTIAL sa_ekm_tde_cred ;
GO
Figure 31: No Domain scenario
In case you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below.
Figure 32: 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 a 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 the wrapping key credential.
Use master
CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY SQL_Server_Key2;
CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'Identity1', SECRET = '<DSM API Key>'
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.
- This script opens a wrapping key, TDE login, and credentials 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 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 = '<DSM API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;
ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
- Make sure you are running this on the SECONDARY.
- 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 the availability group.
USE master;
GO
ALTER DATABASE [SourceDatabase] SET HADR RESUME
- Run on the primary/secondary that appears to be unsynchronized with the availability group.
- 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';
- 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
-
9.2 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 33: Create New SQL EKM Key Where “SQL_EKM_KEY_v1
” is the new key for rotation. - Create a
CREDENTIAL
for EKM Provider created by Fortanix.CREATE CREDENTIAL <Name of credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<DSM API KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
Figure 34: 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 35: 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 36: Suspend Data MovementConfigure 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 shown 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 37: Secondary Database
- Yes
- Enable Transparent Database Encryption Key Rotation.
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 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 38: 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 39: Reopen the new Key - To create the same credential as per Primary.
CREATE CREDENTIAL <Name of credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<DSM API KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
Figure 40: Create Same Credential as Primary - Create a new login on Secondary.
CREATE LOGIN <Name of login>
FROM ASYMMETRIC KEY SQL_EKM_KEY_v1;
Figure 41: Create New Login on Secondary - Once the 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 42: Check the Status of the key on Secondary
9.3 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
"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 43: SQL Server Management Studio
- Using command:
- 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;
GOEXEC sp_addumpdevice 'disk', '<Name-of-your-device>',
‘<Path-to-your-backups>\<Name-of-your-log-backup-file>';
GOBACKUP LOG <Name-of-your-database> TO <Name-of-your-device>;
GOEXEC sp_dropdevice '<Name-of-your-device>';
Example:
USE master;
Back up the log
GO
ALTER DATABASE SourceDatabase
SET RECOVERY FULL;
GO
USE master;
GO
EXEC sp_addumpdevice 'disk', 'EncryptedSourceDatabaseBackupLog',
'\\Server-2\NetWorkShareFolder\SourceDatabase_20160210122459';
GOBACKUP LOG SourceDatabase TO EncryptedSourceDatabaseBackupLog;
Drop backup device
GOEXEC sp_dropdevice 'EncryptedSourceDatabaseBackupLog';
- Break any connection with the SourceDatabase so that encryption can commence.
USE [master];
GOALTER DATABASE SourceDatabase SET ENCRYPTION ON;
GO
10.0 Migrating the TDE Enabled Databases to Another/ New Server
Considering that you have an SQL Server TDE already enabled on one of the Servers that is, SQL1, do the following steps to Migrate the TDE enabled database. Refer to Section 8 for configuring SQL server SQL1.
ALTER DATABASE Company
SET ENCRYPTION OFF ;
GO
Figure 44: Turn encryption off
10.1 Backup SQL1 Server Database
Now take the backup of your database from the SQL1 server.
In the given example we will use the database name as Company
and we are backing it up from the Object Explorer or T-SQL Command.
- Object Explorer:
- Right-click on the desired database (
Company
). - Select Tasks and then click Back Up.
Figure 45: Take backup of SQL1 - Select the backup path.
Figure 46: Select the backup path - Backup completed successfully.
Figure 47: Backup completed - Move the backup database to the secondary server.
- Log in to the secondary server – SQL2.
- Perform the necessary configuration on Windows client for the server SQL2.
- Right-click on the desired database (
10.2 Configuration for Windows Client
Refer to Section 6.2 for steps to configure the windows client.
10.3 Configuration for SQL Server - SQL2
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 48: Create cryptographic provider
In case of an error such as "Extensible key management is not supported or enabled in this edition of SQL server":
Figure 49: Error Scenario Run the following commands:sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO
Figure 50: Run commands for error scenarioNow, 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 = '<DSM API Key>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
- For getting the API key, go to https://sdkms.fortanix.com/#/
- In the UI left panel, click the Applications
- 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 51: 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 = '<DSM API Key>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
Figure 52: 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, run the commands below:
ALTER LOGIN MSSQL2017
ADD CREDENTIAL "sa_ekm_tde_cred";
GO
Figure 53: No domain scenario - In case you are unable to alter the login, open the Object Explorer and map the Credentials as shown below.
Figure 54: Map credentials - Reopen the Asymmetric key which is saved in the EKM server.
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH PROVIDER_KEY_NAME = 'SQL_Key',
CREATION_DISPOSITION = OPEN_EXISTING; - Create a credential that will be used by the database engine.
USE master ;
CREATE CREDENTIAL ekm_tde_cred
WITH IDENTITY = 'Identity2',
SECRET = '<DSM API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
Figure 55: Create a Credential - Add a login used by the 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 56: Add New Credential to Login - Restore the database in SQL Server – SQL2.
- Once restored create the following Database Encryption Key that will be used for Encryption.
USE Company
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO
Figure 57: Create DEK - Alter the database to enable transparent data encryption.
ALTER DATABASE employee
SET ENCRYPTION ON ;
GO
Figure 58: Enable TDE - 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) 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
11.0 Cell/Column Level Encryption (CLE)
In CLE separate data fields in the same table can be encrypted under different encryption keys. These keys can be protected by different credentials. Unlike TDE protection, the user will need to obtain keys from the SQL Extensible Key Management (EKM) provider and must have the correct credentials to authorize and load the encryption key(s) for the specific encrypted data they wish to access. Non-encrypted data is not affected by this and is visible to any authorized user.
Cell-level encryption will only work on data stored in the database as VARBINARY
type. You must provide any necessary type conversions so that data is in VARBINARY form before encryption is performed. Decryption will return the data to its original VARBINARY structure. It may then be necessary to reconvert to its original type for viewing in human-readable form.
11.1 Creating a Symmetric Key from SQL Server
Use the following command to create a symmetric key from the SQL server:
USE Company
CREATE SYMMETRIC KEY dbAES256Key
FROM PROVIDER [EKM_Prov]
WITH PROVIDER_KEY_NAME='ekmAES256Key',
IDENTITY_VALUE='Identity5',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=AES_256;
GO
Once the symmetric key is created, you will be able to see the same key in Fortanix DSM.
11.2 Symmetric Key Creation in Fortanix Data Security Manager
Create a symmetric key in Fortanix DSM.
Figure 59: Symmetric key
After the key is created you can run the following command to encrypt the column from the database. Select the sample database as shown in the below command where we would encrypt the column salary.
Figure 60: Encrypt column
To encrypt the salary column, run the following command to encrypt with the Symmetric key created earlier.
USE Company
UPDATE [dbo].[employee_test]
SET salary = EncryptByKey(Key_GUID('dbAES256Key'),
salary)
For Decryption of the salary column run the following command.
USE Company
UPDATE [dbo].[employee_test]
SET salary = DecryptByKey(salary);
11.3 Creating an Asymmetric Key From SQL Server
To create an asymmetric key, use the following command:
USE column_encryption_test
CREATE ASYMMETRIC KEY dbRSA2048 FROM PROVIDER [EKM_Prov]
WITH PROVIDER_KEY_NAME='ekmRSA2048',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048;
GO
Figure 61: Create Asymmetric Key
11.4 Asymmetric Key Creation in Fortanix Data Security Manager
Create an asymmetric key in Fortanix DSM.
Figure 62: Create Asymmetric Key
Once the asymmetric key is created, encrypt the employee id (empID
) column. Run the following command to encrypt with the Asymmetric key created earlier.
USE Company
UPDATE [dbo].[employee]
SET empID = EncryptByKey(Key_GUID('dbRSA2048'),
empID)
For Decryption of the employee id (empID
) column run the following command:
USE Company
UPDATE [dbo].[employee]
SET empID = DecryptByKey(empID);
11.5 View the Keys Present in the SQL Server
- To view the symmetric keys in a database:
USE Company
SELECT * FROM sys.symmetric_keys
Figure 63: View symmetric key - To view the asymmetric keys in a database:
USE Company
SELECT * FROM sys.asymmetric_keys
Figure 64: View Asymmetric Key
To check all the asymmetric keys between the database and the cryptographic provider run the following commands:
DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '[EKM_Prov]');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.symmetric_keys
ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
FULL OUTER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
GO
To check all the symmetric keys between the database and the cryptographic provider run the following commands:
DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '[EKM_Prov]');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.symmetric_keys
ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
WHERE sys.dm_cryptographic_provider_keys.key_type = 'SYMMETRIC KEY'
GO
12.0 Logging
To find the external key manager (Ekm) logs, run the following command on the admin command prompt.
cd c:\
dir /s/a "EkmLog.txt"
The Ekm log file is always located in the following path: C:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Roaming\Fortanix\KmsClient
Where, MSSQLSERVER
will change according to your local setup.
Comments
For EKM's log, except searching, it usually located at
The "MSSQLSERVER" might change according to your local setup.
Please sign in to leave a comment.