Microsoft SQL Server
Instructions are provided to use encryption keys from UKCUnbound Key Control - The name of Unbound's key management product. with Microsoft SQL Server. Two encryption options are detailed:
- Transparent Data Encryption (TDE
Transparent Data Encryption - Technology to encrypt database files.)
- Always Encrypted (AE
Always Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine)
TDETransparent Data Encryption - Technology to encrypt database files. performs real-time I/O encryption and decryption of the data and log files, protecting data "at rest". It provides the ability to comply with many laws, regulations, and guidelines established in various industries. Refer to the following link for more information about TDE
Transparent Data Encryption - Technology to encrypt database files.:
AEAlways Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine is a feature designed to protect sensitive data, such as credit card numbers, which are stored in an Azure SQL Database or SQL Server databases. AE
Always Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine, providing a separation between those who own the data and those who manage the data.
Refer to the following link for more information about AEAlways Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine:
Prerequisites
You need the following prerequisites for integration with UKCUnbound Key Control - The name of Unbound's key management product.:
- UKC
Unbound Key Control - The name of Unbound's key management product. is installed and running.
- The UKC
Unbound Key Control - The name of Unbound's key management product. Client is installed and registered with the UKC
Unbound Key Control - The name of Unbound's key management product. partition.
- To run TDE
Transparent Data Encryption - Technology to encrypt database files. encryption
- Microsoft SQL Server 2008 or newer.
- The Enterprise edition of SQL Server is required.
- To run AE
Always Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine
- Microsoft SQL Server 2008 or newer.
- The Enterprise edition of SQL Server is required when running versions prior to SQL Server 2016 (13.x).
SQL Server Encryption Using TDE
To configure UKCUnbound Key Control - The name of Unbound's key management product. for your SQL Server:
- Copy the file
C:\Program Files\Dyadic\ekm-client\bin\ekmsqlcp.dll
to thesystem32
directory. - In the SQL management studio, enable crypto in your SQL database.
- Create a UKC
Unbound Key Control - The name of Unbound's key management product. provider.
- Create the RSA key for the database.
- Create the AES login key.
- Encrypt the database.
- Check that the key was created for your database.
Tip
This step is not required if the system32
directory already contains a symbolic link to this file. Using the symbolic link is the recommended practice. It simplifies the client upgrade procedure.
To establish the symbolic link runmklink C:\Windows\System32\ekmsqlcp.dll C:\Progra~1\Dyadic\ekm-client\bin\ekmsqlcp.dll
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO
Create CRYPTOGRAPHIC PROVIDER ekmProvider
FROM FILE = 'C:\Windows\System32\ekmsqlcp.dll'
USE master;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER ekmProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQLKey' ;
GO
Note
PROVIDER_KEY_NAME must not contain underscores (“_”).
USE tde_DB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key;
GO
USE tde_DB;
ALTER DATABASE tde_DB
SET ENCRYPTION ON ;
GO
ucl list -p <partition name>
The database is now encrypted.
Troubleshooting
If you receive an error when initializing the key, check the SQL console or log file. If it contains this warning message:
Attempting to load library 'C:\Windows\System32\ekmsqlcp.dll' into memory
Check that the correct DLL was copied in step 1 of this procedure.
Restoring a Database
If you are restoring a database backup that was encrypted with UKCUnbound Key Control - The name of Unbound's key management product. to a new SQL server, use the following process. It is assumed that the UKC
Unbound Key Control - The name of Unbound's key management product. is already running and can be accessed from the new SQL server.
-
The UKC
Unbound Key Control - The name of Unbound's key management product. Client is installed on the SQL server and registered with the partition.
- Follow steps 1 through 3 in the previous section (SQL Server Encryption Using TDE).
- Use the RSA key from the database backup. In the example, SQLKey is used for the key name. If you changed this name, use the new name in the following code.
- Restore the SQL server backup to the database.
USE master
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER ekmProvider
WITH PROVIDER_KEY_NAME='SQLKey',
CREATION_DISPOSITION = OPEN_EXISTING;
GO
SQL Server Encryption Using AE
Note
Always encrypted only works using an RSA key.
Encryption using AEAlways Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine involves the following terms:
- Column encryption key - used to encrypt data in an encrypted column.
- Column master key - a key-protecting key that encrypts one or more column encryption keys.
In addition, it may be helpful to read the following blog about using HSMs with AEAlways Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine:
To configure UKCUnbound Key Control - The name of Unbound's key management product. for your SQL Server using Always encrypted:
- Create the Column Master key.
- Associate the Column Master Key with your database. For this example, a database called
encrypt_test
is used. - Create the column encryption key by running the command:
- Save ENC_VALUE that is output from the previous step.
- Create a column key. You must replace ENC_VALUE in the following example with the value from the previous step.
- Create a table with encrypted columns. The following example creates 2 encrypted columns, SSN and BirthDate.
- Execute a test that creates data and then decrypts it using the sample program.
- Creates 3 new records in the database.
- Prints the contents of the records containing a specific SSN (“123-45-6789”)
- Prints the contents of the records containing an additional specific SSN (“111-22-3333”)
- Prints the content of all the records.
ucl generate -t RSA --name AlwaysEncryptedCMK
USE encrypt_test ;
GO
CREATE COLUMN MASTER KEY [CMK1]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CNG_STORE',
KEY_PATH = N'Dyadic Security Key Storage Provider/AlwaysEncryptedCMK'
)
SetupAlwaysEncrypted.exe AlwaysEncryptedCMK
This program creates a new Column Encryption Key (CEKColumn Encryption Key - used for database encryption) and encrypts it with the CMK
1. Column Master Key - used for database encryption
2. Crypto Master Key - used to encrypt other keys.
Note
This program can be downloaded from the following link:
https://github.com/unbound-tech/ub-integration/tree/master/SQL-Server-Always-Encrypted
USE encrypt_test;
GO
CREATE COLUMN ENCRYPTION KEY [CEK1]
WITH VALUES
(
COLUMN_MASTER_KEY = [CMK1],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = ENC_VALUE
)
USE encrypt_test;
GO
CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1),
[SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN_DEC] [nvarchar](50) NULL,
[BirthDate_DEC] [nvarchar](50) NULL,
[BirthDate] [date]
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY]
)
UseAlwaysEncrypted.exe <IP address> encrypt_test
This program does the following:
Note
This program can be downloaded from the following link:
https://github.com/unbound-tech/ub-integration/tree/master/SQL-Server-Always-Encrypted
Viewing Keys in the SQL Server Management Console
You can verify the previous procedure by viewing the contents of the database with the SQL Server Management Console.
To inspect the Column Master Keys:
- Expand Databases > encrypt_test > Security > Always Encrypted Keys > Column Master Keys.
- Right-click on CMK1 and select Properties.
- Check that the key path of CMK1 is within Dyadic KSP.
To inspect the Column Encryption Keys:
- Expand Databases > encrypt_test > Security > Always Encrypted Keys > Column Encryption Keys.
- Right-click on CEK1 and select Properties.
- Check that CEK1 is encrypted with CMK1.
- Inspect the columns that are encrypted with CEK1:
- “SSN” field is encrypted is “Deterministic
Calculation that each time results in the same value” Encryption Type.
- “BirthDate” field is encrypted with “Randomized” Encryption Type.