Integrating CAKM for Microsoft SQL Server EKM with SQL Server High Availability (Always ON) Groups

The Always ON Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring.

Introduced in MSSQL Server 2012, Always ON Availability Groups maximizes the availability of a set of user databases for an enterprise. An Availability Group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together.

An Availability Group supports a set of read-write primary databases and one to eight set of corresponding secondary databases. Optionally, secondary databases can be made available for read only access and/or some backup operations.

To perform this integration, you need CAKM for Microsoft SQL Server EKM Provider/Connector.

MSSQL Server Setup

MSSQL Server must be installed and configured as nodes in a Windows Server Failover Cluster (WSFC) for an EKM provider to be used for Always On availability groups and SQL Server failover clustering. For more information, refer to the Microsoft documentation.

Column Encryption

To perform column encryption, perform the following steps on all the SQL Server nodes that will be part of the Always On group.

Creating EKM Provider and Credentials

1. Enable the EKM provider:

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

2. Create the cryptographic provider:

   CREATE CRYPTOGRAPHIC PROVIDER <provider_name> FROM FILE = '<Installation_Directory>\CipherTrust\CAKM For SQLServerEKM\cakm_mssql_ekm.dll'
   

   For example:

   CREATE CRYPTOGRAPHIC PROVIDER cakmSQLEKM FROM FILE = 'C:\Program Files\CipherTrust\CAKM For SQLServerEKM\cakm_mssql_ekm.dll'
   

3. Create a credential and add the credential to connected database user (domain user):

   CREATE CREDENTIAL <cred_name>
   WITH IDENTITY = <ks_user_name>,
   SECRET=<ks_user_password>
   FOR CRYPTOGRAPHIC PROVIDER <provider_name>
   ALTER LOGIN <Domain\login_name> ADD CREDENTIAL <cred_name>
   

   For example:

   CREATE CREDENTIAL TestEKMCred
   WITH IDENTITY = “ekmuser”,
   SECRET=”asdf1234”
   FOR CRYPTOGRAPHIC PROVIDER cakmSQLEKM
   ALTER LOGIN sqlekmserver\ekmuser ADD CREDENTIAL TestEKMCred
   

Creating the Always On Availability Group

Open the Microsoft SQL Server Management Studio on primary replica and create a database first and then take full backup of that database on a shared location that is accessible by all SQL Server nodes.

Open the Always On Availability Group Creation wizard and follow the instructions to create the Always On Availability Group. For detailed steps and prerequisites, refer to the Microsoft online documentation for creating the Always On Availability Group.

After successful creation of the group, the dashboard displays all the participating nodes. An example of a dashboard is shown below. For demonstration, two nodes are added: primary and secondary.

Performing Column Encryption

To perform column encryption:

1. Create symmetric/asymmetric keys for the database.

   USE DB
   

   • Using a Symmetric Key:

     CREATE SYMMETRIC KEY <key_name_in_database>
     FROM PROVIDER <provider_name> WITH ALGORITHM=’<algorithm>’
     PROVIDER _KEY_NAME=’<key name on KeySecure>’,
     CREATION_DISPOSITION=CREATE_NEW
     

     For example:

     CREATE SYMMETRIC KEY SQL_EKM_AES_256_Key
     FROM Provider cakmSQLEKM
     WITH ALGORITHM = AES_256,
     PROVIDER_KEY_NAME = 'EKM_AES_256_Key',
     CREATION_DISPOSITION=CREATE_NEW
     

   • Using an Asymmetric Key:

     CREATE ASYMMETRIC KEY <key name in database>
     FROM Provider <provider name>
     WITH ALGORITHM = <algorithm>,
     PROVIDER_KEY_NAME = '<key name on KeySecure>',
     CREATION_DISPOSITION=CREATE_NEW
     

     For example:

     CREATE ASYMMETRIC KEY <key name in database>
     FROM Provider cakmSQLEKM
     WITH ALGORITHM = RSA_2048,
     PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
     CREATION_DISPOSITION=CREATE_NEW
     

2. Create a table in the database.

   Create Table Test (Id numeric(10), Name varchar (50))
   

3. Encrypt the column using symmetric or asymmetric keys.

   • Using Symmetric Key

     INSERT INTO <table_name> VALUES (1, ENCRYPTBYKEY(KEY_GUID(‘<sym_key_name>’, ‘<data>’));
     

   • Using Asymmetric Key

     INSERT INTO <table_name> VALUES (2, ENCRYPTBYASYMKEY (ASYMKEY_ID (<asym_key_name>'), '<data>'))
     

     For example:

     INSERT INTO Test VALUES (1, ENCRYPTBYKEY(KEY_GUID(‘SQL_EKM_AES_256_Key’, ‘Alice’));
     INSERT INTO Test VALUES (2, ENCRYPTBYASYMKEY (ASYMKEY_ID (‘SQL_EKM_RSA_2048_Key '), 'Bob'))
     

4. Decrypt the column using symmetric or asymmetric keys.

   • Using a Symmetric Key

     SELECT Id, CONVERT(varchar(MAX),DecryptByKey(Name)) LName FROM dbo.test1 where id =6
     

   • Using an Asymmetric Key

     SELECT id, CONVERT (varchar (MAX), DecryptByAsymKey(AsymKey_Id ('SQL_EKM_RSA_2048_Key'), Name)) FROM dbo.test where id =2
     

5. Login to the secondary node.

   Verify that output is the same as that on the primary replica.

Creating Symmetric Key Encrypted by Asymmetric Key on CAKM for Microsoft SQL Server EKM

To create a symmetric key encrypted by an asymmetric key on CAKM for Microsoft SQL Server EKM, open the SMS on primary replica:

1. Execute the following command from SQL query window.

   Create SYMMETRIC KEY key1
   WITH ALGORITHM = AES_256
   ENCRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;
   

   where SQL_EKM_RSA_2048_Key is an existing asymmetric key.

2. Open the key before using it. Execute the following command to open the symmetric key.

   OPEN SYMMETRIC KEY key1 DECRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;
   

3. Encrypt the data using the key1.

   INSERT INTO dbo.test
   values ( 3,
   Encryptbykey(KEY_GUID('Key1'), 'text to be encrypted'))
   

4. Decrypt the data using the key1.

   SELECT id,CONVERT(varchar(MAX), DecryptByKey(name))
   FROM dbo.test where id =3
   

5. Close the symmetric key.

   CLOSE SYMMETRIC KEY key1
   

6. Now execute the steps 2-5 on secondary replica and verify if the output is same as primary node.

Enable Transparent Database Encryption on Always On Group

To enable transparent data encryption on the SQL Server databases:

• Enable TDE on primary node

• Restore the database on secondary node

Enabling TDE

1. Create an asymmetric key in the master database.

   CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
   FROM Provider cakmSQLEKM
   WITH ALGORITHM = RSA_2048,
   PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
   CREATION_DISPOSITION=CREATE_NEW
   

2. Login to secondary node and create the same asymmetric key.

   CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
   FROM Provider cakmSQLEKM
   WITH ALGORITHM = RSA_2048,
   PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
   CREATION_DISPOSITION=OPEN_EXISTING;
   

3. Create a new credential.

   CREATE CREDENTIAL ekm_tde_cred
   WITH IDENTITY = 'ekmuser',
   SECRET = 'asdf1234'
   FOR CRYPTOGRAPHIC PROVIDER cakmSQLEKM;
   

4. Create a login using the asymmetric key created above.

   CREATE LOGIN tde_login
   FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;
   

5. Add the credential to the login created above.

   ALTER LOGIN tde_login
   ADD CREDENTIAL ekm_tde_cred;
   

6. Execute steps 2-5 on all the secondary nodes. It is required because TDE encryption key, credential, and login are created in the master database and it is not a part of the Availability Group.

7. Create a database and its encryption key on primary node.

   CREATE DATABASE TDE_TEST;
   USE TDE_TEST;
   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;
   

8. Enable Database Encryption.

   ALTER DATABASE TDE_TEST
   SET ENCRYPTION ON;
   

9. To query the status of database encryption, TDE key change and its percentage completion.

   SELECT DB_NAME (e.database_id) AS DatabaseName,
   e.database_id, e.encryption_state,
   CASE e.encryption_state
   WHEN 0 THEN 'No database encryption key present, no encryption'
   WHEN 1 THEN 'Unencrypted'
   WHEN 2 THEN 'Encryption in progress'
   WHEN 3 THEN 'Encrypted'
   WHEN 4 THEN 'Key change in progress'
   WHEN 5 THEN 'Decryption in progress'
   END AS encryption_state_desc,
   c.name,
   e.percent_complete
   FROM sys.dm_database_encryption_keys AS e
   LEFT JOIN master.sys.asymmetric_keys AS c
   ON e.encryptor_thumbprint = c.thumbprint
   

   Note

   Before adding the already encrypted database into availability group, take the full backup of the database on shared location that is accessible by all secondary nodes.

10. Log in to Primary node and add the database into the availability group.

    use master;
    ALTER AVAILABILITY GROUP <availability_group_name> ADD DATABASE <database_name>;
    

    For example:

    ALTER AVAILABILITY GROUP AGroup ADD DATABASE TDE_TEST;
    

    This command will add the database into the availability group but it will not be available on the secondary node.

Adding Database on Secondary Node

1. Login to the secondary node and restore the database with NO RECOVERY option.

   RESTORE DATABASE TDE_TEST
   FROM DISK =’<backup_file_location>’
   WITH NORECOVERY;
   

2. Login to the secondary node and add the database in availability group.

   USE master;
   ALTER DATABASE <database_name> SET HADR AVAILABILITY GROUP = <availability_group_name>;
   

   For example:

   ALTER DATABASE TDE_TEST SET HADR AVAILABILITY GROUP = AGroup;
   

3. Query the status of database encryption and its percentage completion on the secondary node.

   SELECT DB_NAME (e.database_id) AS DatabaseName,
   e.database_id,
   e.encryption_state,
   CASE e.encryption_state
   WHEN 0 THEN 'No database encryption key present, no encryption'
   WHEN 1 THEN 'Unencrypted'
   WHEN 2 THEN 'Encryption in progress'
   WHEN 3 THEN 'Encrypted'
   WHEN 4 THEN 'Key change in progress'
   WHEN 5 THEN 'Decryption in progress'
   END AS encryption_state_desc,
   c.name,
   e.percent_complete
   FROM sys.dm_database_encryption_keys AS e
   LEFT JOIN master.sys.asymmetric_keys AS c
   ON e.encryptor_thumbprint = c.thumbprint
   

Transparent Database Encryption (TDE) Key Rotation

1. Create an asymmetric key using CAKM for Microsoft SQL EKM Provider on primary node.

   Use master;
   CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
   FROM Provider cakmSQLEKM
   WITH ALGORITHM = RSA_2048,
   PROVIDER_KEY_NAME = ‘EKM_RSA_2048_Key_TDE_Rot’, CREATION_DISPOSITION=CREATE_NEW
   

2. Create same asymmetric key using CAKM for Microsoft SQL EKM Provider on the secondary node.

   Use master;
   CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
   FROM Provider cakmSQLEKM
   WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
   CREATION_DISPOSITION=OPEN_EXISTING
   

3. Create a credential for CAKM for Microsoft SQL EKM Provider.

   CREATE CREDENTIAL ekm_tde_cred
   WITH IDENTITY = 'ekmuser',
   SECRET = 'asdf1234'
   FOR CRYPTOGRAPHIC PROVIDER cakmSQLEKM ;
   

4. Create a login based on the asymmetric key created above.

   CREATE LOGIN mylogin
   FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;
   

5. Map the credential created above to the login created above.

   ALTER LOGIN mylogin
   ADD CREDENTIAL ekm_tde_cred;
   

6. Execute steps 2-5 for all secondary nodes. It is required because TDE encryption key, credential, and login are created in the master database and it is not a part of Availability Groups. Therefore, you need to create the same key, credential, and login in the master database of all the secondary nodes to access the encrypted tables created in the database.

7. Perform Transparent Database Encryption Key Rotation on the primary node.

   Use tde_test;
   ALTER DATABASE ENCRYPTION KEY
   REGENERATE
   WITH ALGORITHM = AES_128
   ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
   

8. To query the status of database encryption, TDE key change, and its percentage completion.

   SELECT DB_NAME (e.database_id) AS DatabaseName,
   e.database_id,
   e.encryption_state,
   CASE e.encryption_state
   WHEN 0 THEN 'No database encryption key present, no encryption'
   WHEN 1 THEN 'Unencrypted'
   WHEN 2 THEN 'Encryption in progress'
   WHEN 3 THEN 'Encrypted'
   WHEN 4 THEN 'Key change in progress'
   WHEN 5 THEN 'Decryption in progress'
   END AS encryption_state_desc,
   c.name,
   e.percent_complete
   FROM sys.dm_database_encryption_keys AS e
   LEFT JOIN master.sys.asymmetric_keys AS c
   ON e.encryptor_thumbprint = c.thumbprint