Intelliopz

Securing SQL Server with Transparent Data Encryption (TDE ) - Extensible Key Management(EKM)

What is SQL Server Transparent data encryption(TDE) and why you need it?

Transparent data encryption (TDE) encrypts SQL server data files. This encryption is known as encrypting data at rest.
You can take number of precautions to help with securing the databases like,
– Designing a secure system
– Encrypting confidential assets.
– Building a firewall around database servers.

However, any malicious party who steals your physical media like disk drives, backup tables or other media can restore/attach the databases and browse its contents.

TDE solution is encrypt sensitive data in a database and use a certificate to protect the keys the encrypt the data. This way it prevents anyone without the keys from using the the data.

The encryption uses a database encryption key(DEK). The database boot record stores the key and it is available during database recovery. The DEK can be
– a symmetric key (secure by a certificate that the server’s master database)
– an asymmetric key (an external key management module to protect it).

We will discuss how you protect the key by using EKM(External key management) module in rest of the discussion.

What is Extensible Key Management(EKM) and why use it?

As we discussed before, data can be either encrypted using a symmetric data encryption key(DEK) or asymmetric Key data encryption.

Using the EKM provider will enable SQL server to protect the data encryption keys by using asymmetric key stored outside of SQL server in an external cryptographic provider like Azure Key Vault. This will add an additional layer of security and separates the management of keys and data.

Azure Key vault can be used with SQL Sever on-premises installation and Azure VM installations (Infrastructure as a service).
The SQL server connector act as a bridge between SQL Server and Azure Key Vault.
An addition to this the Azure key vault service also provides the option to use controlled and monitored Hardware security Model(HSMs) like Vormetric for a higher level of protection for asymmetric encryption keys.

How to Setup?

There are five basic steps to follow.
1. Setup an Azure AD service principle
2. Create a Key vault
3. Install the SQL Server connector
4. Configure SQL Server
5. Encrypt the databases from TDE

Pre-requisites

– Azure powerShell version 5.2.0 or later
– Install the version of Visual studio C++ redistributable based on the SQL version

For SQL server 2008->2014:  https://www.microsoft.com/en-us/download/details.aspx?id=48145
For SQL Server 2016->2019: https://www.microsoft.com/en-us/download/details.aspx?id=40784

 
 
1. Setup an Azure AD Service principle

Anyone who uses key vaults, whether users or application needs to be registered in Azure active directory(AAD).

So, navigate to the Azure Active directory and click on “App registration” under “Manage” section.

On the app registration pane, go and create “New registration”.

Enter the meaningful name and click register.

Once created, click on “Certificates and secrets”.

Under the “New Client secret”, add a description and expiry date. And then click “add”.
Note: You might be wondering that what we should do to renew the secret once its expired. It is covered in this blog:

Once you have created, copy the values under client secrets.

Now, go back to the “Overviews” tab and copy the values for “Application(client) ID.
These values will be used to create an asymmetric key in SQL server. Please keep them copy somewhere as this cannot be seen again.

 
 
2. Create an Azure Key Vault

From the Azure portal, go to “create a service” and type “Key vault”.

Enter the Key vault name, resource group and the region and create.

Once created, go the Azure key vault and click “Access policies”

Then add access policies.

Select “Key Management” and then select “Get, List, Unwrap Key and Wrap key” permissions as below.

Select “Principle” from the list.

Finally, click Add.

Then click “Save” to commit the changes.

Now, navigate to “Keys” setting and add a key.

Enter the key vault name and select followings.

 
 
 
3. Install the SQL Server Connector for Azure Key vault.

The SQL connector can be downloaded from below location and need to be installed on the SQL server VM.
https://www.microsoft.com/en-us/download/details.aspx?id=45344

 
 
 
4.Configure SQL Server.

The following steps needs to be executed from the SQL server.

Step 1: Configure SQL server to use EKM:
Connect to the SQL Server from SQL Server management studio (SSMS)

USE master; 
GO EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO -- Enable EKM provider
EXEC sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;
Step 2: Register the SQL Server connector as an EKM provider with SQL server.

The Cryptographic provider here in this case is AzureKV_EKM and we are using default DLL path of the connector installation.

CREATE CRYPTOGRAPHIC PROVIDER AzureKV_EKM
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';
GO
Step 3:  Setup a SQL Server credential for a SQL server login to use the key vault.

A credential must be added to each login that will perform encryption by using a key from the key vault.

USE master;
CREATE CREDENTIAL sysadmin_ekm_credentials
WITH IDENTITY = 'TDESQLKeyvalult',
SECRET = '7cb558117879456sdx978f53345d241ff.7Q~uxcdxJdtLFW1n16CbWm~CbkzUzyfqI'
FOR CRYPTOGRAPHIC PROVIDER AzureKV_EKM;

Identity: Enter the value of the key vault
Secret : Application(client) ID + Secret value from application registration.
Note: Please remove any hyphens from the client ID.

Step 4:  Add the credential to the SQL server admin login
ALTER LOGIN saadmin
ADD CREDENTIAL sysadmin_ekm_credentials;

Step 5: Open Azure key vault key in SQL server instance:

Before doing this, we need to complete two prerequisites.

  • Open regedit
  • Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
  • Create a new Key called “SQL Server Cryptographic Provider” (without quotes)
  • Right click the key, from the context menu select ‘permissions.

 

Once completed, create Asymmetric key by referring the key in Key vault.

CREATE ASYMMETRIC KEY TDEAsymKey
FROM PROVIDER [AzureKV_EKM]
WITH PROVIDER_KEY_NAME = 'TDEKey',
CREATION_DISPOSITION = OPEN_EXISTING;

Step 6: Create a new login for Asymmetric key and remap credential.

Create a new login that is associate the asymmetric key

CREATE LOGIN TDESQL
FROM ASYMMETRIC KEY TDEAsymKey;

Drop the association with previous login (saadmin).

ALTER LOGIN [saadmin]
DROP CREDENTIAL sysadmin_ekm_credentials;

Map the EKM credentials to the new login:

ALTER LOGIN TDESQL
ADD CREDENTIAL sysadmin_ekm_credentials;

Now, we have completed all our login and credential mappings.

 
 
 
5. Encrypt the user databases from TDE

Check if the database is already encrypted.

Create the database encryption key by using Asymmetric key (TDEAsymKey):

USE SecureME
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY TDEAsymKey;

Encrypt the database:

ALTER DATABASE SecureME
SET ENCRYPTION ON;

Check if the database is encrypted:

 

 

Summary and Next Steps:

We believe you can’t afford to lose productivity, expose sensitive information, or access to your company’s confidential documents. It is vital to secure your data-at-rest locations by implementing the solutions like TDE. There are number of benefits data encryption will bring in an Enterprise environment. It is always best to follow the best practices and adding extra layer of security when enabling Transparent Data Encryption (TDE) with SQL Server databases . With our enhance security expertise, we are able to implement the best practices for the enterprise environments.

Need help securing your data, databases and assets? Please contact Intelliopz.