This article is going to give an overview on how the Transparent Data Encryption is SQL Server can be accomplished by using the Database Encryption Key stored outside of the SQL Server there by increasing the safety of the data.
Prerequisites
Must have an Azure subscription (A free Tier would suffice for the scope of this article).Understand the concepts behind Extensible Key Management Using Azure Key Vault (SQL Server).
Refer to Extensible Key Management Using Azure Key Vault (SQL Server) for details.
Steps
Login to Azure portal using your credentials. (https://portal.azure.com/). 2. Set up an Azure Active Directory (AAD) service principal.
For setting up Service Principal account in AAD, Register an application with Azure Active Directory. For detailed step-by-step instructions to register an application, refer below steps :
2.1 ) Go to Azure Portal > Azure Active Directory > App Registrations > New Registration
📷
2.2 ) Name the application. (For eg : TDE_Application ) and Choose one account type as required. Redirect URI is not required for our scope. Click Register.
📷
2.3) Now the application page will look like the below page.
📷
Note down the client ID (here : fe1cee84-d5ee-4369-8274-b6cdc71162c4) for later use to create SQL Server credential in step 5.3 . The client ID needs to be concatenated with Client SECRET to use it as the parameter ‘SECRET’ during credential creation. Refer 2.4 for creation of client secret.
2.4) In order to create a client secret you need to click on the Clients and Secrets option on the left side panel in the TDE_Application page shown in step 2.3.
📷
Click on the ‘New Client Secret’ as highlighted in the above image which will create a client secret for you. Options are there to set the expiry of the secret. For this article , I have named the Client secret as TDE_ClientSecret.
📷
📷
Copy the Client secret (6R8tTY6zBFk]b5Cs.l-q[ECS-d6Q]EFZ) for later use in step 5.3 while creating SQL Server credentials.
Create a Key Vault and Key
The key vault and key created here will be used by the SQL Server Database Engine for encryption key protection.
Note that the subscription where the key vault is created must be in the same default Azure Active Directory where the Azure Active Directory service principal was created. If you want to use an Active Directory other than your default Active Directory for creating a service principal for the SQL Server Connector, you must change the default Active Directory in your Azure account before creating your key vault.
3.1 Create Key Vault
Search for ‘Key Vault’ in the Azure portal OR navigate to dashboard, select + Create a resource, then Security + Identity, then Key Vault.
📷
Click on ‘Key Vaults’ option and you will reach the page to create a new key vault. Click on the ‘Create Key Vault’ button in the page and will take you to the below page.
Select the Subscription you want to opt. Here I went with my free tier. Select the Resource group if you have one already or you can create it easily by clicking on ‘Create New’ button at the bottom part of the Resource group text box.
Mention a name for the key Vault ( eg : TDEKeyVault)
Select the nearest region.
Keep the retention period. (Can be selected between 7 – 90). Soft delete is an option which allows you to recover a deleted Key Vault or objects within the key vault with in the retention period we specified.
📷
3.2 Set the Access Policy
Once you filled all the specified fields in the page mentioned in 3.1, Click on the ‘Access Policy’ tab to choose the necessary set of privileges required for the EKM to work. The Azure Active Directory service principal and USER must have at least the get, wrapKey, and unwrapKey permissions for the key vault.
📷
Once the access is granted for the USER, you need to grant the same set of permissions to the Service principal as well. Perform the below steps to grant it.
Click on ‘Add access Policy’ in the ‘Create key Vault page’ shown in the step 3.2.Select Key Management from the template. Grant the get, wrap and unwrap privileges in the Key permissions field.Grant ‘Get’ privilege in the secret permissions field. (In some cases, you may need to grant full access to get the asymmetric key created in step 5.5)Search the principal Name (TDE_application in this example).
📷
Click Add.
3.3 Move on to Networking Tab.
📷
As mentioned in the description choose the required option so that you can connect to this key vault either publicly, via public IP addresses or service endpoints, or privately, using a private endpoint. I choose the public endpoint with all networks enabled option here as I am creating this for article purpose. You can better go for the second option if more security is required.
3.4 Create tags though it is not mandatory. This is for categorizing resources and for easy consolidation.
📷
Once all the categories are filled, Click on the ‘Review and Create’ button so that it will review and create the key vault for you .
📷
Once the Key Vault is completed , you will get a similar message on the key Vault creation completion.
📷
Create a key with RSA 2048 algorithm which is the one supported by the SQL Server latest versions.
Go to resource from the last step of 3.4 and click on ‘Generate\Import a key’ Option after selecting the ‘Keys’ option in the Left pane.
Name the key eg: TDEKey .
Click on ‘Create’.
📷
Once the key is created, you will get a similar page as follows showing a message that the Key has been successfully created. You can use the option to import the key also if you already have one created earlier.
📷
With the key creation , we are done with the configuration with Azure portal for setting up TDE with Extensible key management (EKM). Now we need to head towards the SQL Server configuration with the set of objects that we created in the Azure portal like Client ID, Client Secret, KeyVault, Key etc.
However, it is equally important to know that , there should be bridging unit between SQL Server and the third party vendor application for managing EKM (Here it is Azure). This bridging unit is termed as SQL Server connector. We need to install the SQL Server connector first prior to configuration of SQL Server TDE configuration.
Install the latest SQL Server connector
Download the exe file and just install it in the workstation where your SQL server is installed.
By default, the connector installs at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. This location can be changed during setup. (If changed, adjust the scripts below.)
There is no interface for the Connector, but if it is installed successfully, the Microsoft.AzureKeyVaultService.EKM.dllis installed on the machine. This is the cryptographic EKM provider DLL that needs to be registered with SQL Server by using the CREATE CRYPTOGRAPHIC PROVIDER statement.
SQL Server TDE configuration
Launch SSMS.Enable the EKM provider in sp_configure options.
-- Enable advanced options.
USE master;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- Enable EKM provider
sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;Register (create) the SQL Server Connector as an EKM provider with SQL Server.
A cryptographic provider will be created in SQL server with the file created in the step 4 while installing SQL Connector for Azure
CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';
GO📷
5.4 Setup a SQL Server credential for a SQL Server login to use the key vault.
This is one of the critical portion of TDE configuration using EKM. A credential is required to be added to each login which performs encryption using a key from the key Vault which will act as a one-one mapping.
Query to create the credential is as follows :
USE master; CREATE CREDENTIAL sysadmin_ekm_cred
WITH IDENTITY = 'TDEKeyVault', -- for public Azure -- WITH IDENTITY =
'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government -- WITH IDENTITY =
'ContosoDevKeyVault.vault.azure.cn', -- for Azure China -- WITH IDENTITY =
'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany
SECRET = 'fe1cee84d5ee43698274b6cdc71162c46R8tTY6zBFk]b5Cs.l-q[ECS-d6Q]EFZ'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;where Identity is the KeyVault name as in your Azure portal.( Refer 3.1), SECRET is the string created by concatenating the ClientID without hyphens and the Client secret.
From step 2.3 Client ID is fe1cee84-d5ee-4369-8274-b6cdc71162c4 From step 2.4 Client Secret is 6R8tTY6zBFk]b5Cs.l-q[ECS-d6Q]EFZ
Hence the SECRET to be used in the query is
fe1cee84d5ee43698274b6cdc71162c46R8tTY6zBFk]b5Cs.l-q[ECS-d6Q]EFZ📷
Now , Create a SQL Server login associated with the asymmetric key for the Database engine to use when it loads a database encrypted by TDE.
USE master
CREATE LOGIN TDE_Login2
FROM ASYMMETRIC KEY CONTOSO_KEY
GOAdd the credential to the SQL Server administrator's domain login
ALTER LOGIN [LAPTOP-612JJTU1\Vishnu Gupthan Nair]
ADD CREDENTIAL sysadmin_ekm_cred;
GO5.5 Open your Azure Key Vault key in SQL Server
Whether you created a new key, or imported an asymmetric key, you will need to open the key. Open the key by providing your key name in the following Transact-SQL script.
CREATE ASYMMETRIC KEY [TDEinSQLServer_KEY]
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'TDEKey',
CREATION_DISPOSITION = OPEN_EXISTING;📷
Once the asymmetric key is created, you can view it under Asymmetric keys tab under master database.
Create the Database Encryption Key (DEK)
The DEK will encrypt your data and log files in the database instance, and in turn be encrypted by the Azure Key Vault asymmetric key. The DEK can be created using any SQL Server supported algorithm or key length.
USE AdventureWorks2016;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY [TDEinSQLServer_KEY];
GOTurn on TDE
-- Alter the database to enable transparent data encryption.
ALTER DATABASE AdventureWorks2016
SET ENCRYPTION ON;
GO