Microsoft SQL Server

Instructions are provided to use encryption keys from UKCClosedUnbound Key Control - The name of Unbound's key management product. with Microsoft SQL Server. Two encryption options are detailed:

TDEClosedTransparent 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 TDEClosedTransparent Data Encryption - Technology to encrypt database files.:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/enable-tde-on-sql-server-using-ekm

AEClosedAlways 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. AEClosedAlways 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 AEClosedAlways Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

Prerequisites

You need the following prerequisites for integration with UKCClosedUnbound Key Control - The name of Unbound's key management product.:

SQL Server Encryption Using TDE

To configure UKCClosedUnbound Key Control - The name of Unbound's key management product. for your SQL Server:

  1. Copy the file C:\Program Files\Dyadic\ekm-client\bin\ekmsqlcp.dll to the system32 directory.

    2. 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 run
    mklink C:\Windows\System32\ekmsqlcp.dll C:\Progra~1\Dyadic\ekm-client\bin\ekmsqlcp.dll

  2. In the SQL management studio, enable crypto in your SQL database.

    3. sp_configure 'show advanced', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'EKM provider enabled', 1
    GO
    RECONFIGURE
    GO

  3. Create a UKCClosedUnbound Key Control - The name of Unbound's key management product. provider.

    4. Create CRYPTOGRAPHIC PROVIDER ekmProvider
    FROM FILE = 'C:\Windows\System32\ekmsqlcp.dll'

  4. Create the RSA key for the database.

    5. 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 (“_”).

  5. Create the AES login key.

    6. USE tde_DB;
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key;
    GO

  6. Encrypt the database.

    7. USE tde_DB;
    ALTER DATABASE tde_DB
    SET ENCRYPTION ON ;
    GO

  7. Check that the key was created for your database.

    8. 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 UKCClosedUnbound Key Control - The name of Unbound's key management product. to a new SQL server, use the following process. It is assumed that the UKCClosedUnbound Key Control - The name of Unbound's key management product. is already running and can be accessed from the new SQL server.

  1. The UKCClosedUnbound Key Control - The name of Unbound's key management product. Client is installed on the SQL server and registered with the partition.

  2. Follow steps 1 through 3 in the previous section (SQL Server Encryption Using TDE).
  3. 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.

    4. USE master
    CREATE ASYMMETRIC KEY ekm_login_key
    FROM PROVIDER ekmProvider
    WITH PROVIDER_KEY_NAME='SQLKey',
    CREATION_DISPOSITION = OPEN_EXISTING;
    GO

  4. Restore the SQL server backup to the database.

SQL Server Encryption Using AE

Note
Always encrypted only works using an RSA key.

Encryption using AEClosedAlways 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 AEClosedAlways Encrypted - allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine:

https://blogs.msdn.microsoft.com/sqlsecurity/2015/11/16/using-hardware-security-modules-with-always-encrypted/

To configure UKCClosedUnbound Key Control - The name of Unbound's key management product. for your SQL Server using Always encrypted:

  1. Create the Column Master key.

    2. ucl generate -t RSA --name AlwaysEncryptedCMK

  2. Associate the Column Master Key with your database. For this example, a database called encrypt_test is used.

    3. 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'
    )

  3. Create the column encryption key by running the command:

    4. SetupAlwaysEncrypted.exe AlwaysEncryptedCMK

    This program creates a new Column Encryption Key (CEKClosedColumn Encryption Key - used for database encryption) and encrypts it with the CMKClosedColumn Master Key - used for database encryption.

    Note
    This program can be downloaded from the following link:
    https://github.com/unbound-tech/ub_sql_always_encrypted

  4. Save ENC_VALUE that is output from the previous step.
  5. Create a column key. You must replace ENC_VALUE in the following example with the value from the previous step.

    6. USE encrypt_test;
    GO
    CREATE COLUMN ENCRYPTION KEY [CEK1]
    WITH VALUES
    (
    COLUMN_MASTER_KEY = [CMK1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = ENC_VALUE
    )

  6. Create a table with encrypted columns. The following example creates 2 encrypted columns, SSN and BirthDate.

    7. 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]
    )

  7. Execute a test that creates data and then decrypts it using the sample program.

    8. UseAlwaysEncrypted.exe <IP address> encrypt_test

    This program does the following:

    • 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.

    Note
    This program can be downloaded from the following link:
    https://github.com/unbound-tech/ub_sql_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:

  1. Expand Databases > encrypt_test > Security > Always Encrypted Keys > Column Master Keys.
  2. Right-click on CMK1 and select Properties.
  3. Check that the key path of CMK1 is within Dyadic KSP.

To inspect the Column Encryption Keys:

  1. Expand Databases > encrypt_test > Security > Always Encrypted Keys > Column Encryption Keys.
  2. Right-click on CEK1 and select Properties.
  3. Check that CEK1 is encrypted with CMK1.
  4. Inspect the columns that are encrypted with CEK1: