See more

Using SDKMS with Microsoft SQL Server TDE

  • Last updated:
  • access_time Reading time:

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 SDKMS to be used with SQL Server TDE.

Before starting, download the SDKMS client installer for Windows 64-bit from Resources.

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

FortanixKmsClientConfig.exe machine --api-endpoint https://www.smartkey.io

To configure the Fortanix KMS Server URL for the current user, run

FortanixKmsClientConfig.exe user --api-endpoint https://www.smartkey.io

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:

  • CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov. Use the correct location of the EKM DLL.
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
  • Create a credential that will be used by system administrators. See Getting Started 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
  • 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
  • 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
  • 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 ;
  • 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
  • Create table employee.
CREATE TABLE employee (first_name VARCHAR2(128),last_name VARCHAR2(128),empID NUMBER,salary NUMBER(6));
CREATE DATABASE employee
GO
  • Create the database encryption key that will be used for TDE.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM  = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO
  • Alter the database to enable transparent data encryption.
ALTER DATABASE employee
SET ENCRYPTION ON ;
GO