Microsoft SQL

1Set up Luna HSM

2Configure Luna EKM

3Configure SQL Server

1Ensure that the HSM is set up, initialized, provisioned, and ready for deployment. For more information, refer to Luna HSM documentation.

2Create a partition that will be later on used by Microsoft SQL.

3Create and exchange certificate between the Luna Network HSM and client system. Register client and assign partition to create an NTLS connection.

4Initialize Crypto Officer and Crypto User roles for the registered partition.

5Run the following command to verify that the partition has been successfully registered and configured:

C:\Program Files\SafeNet\LunaClient>lunacm.exe

Upon successful execution, you should observe an output similar to the example provided below:

lunacm.exe (64-bit) v10.3.0-275. Copyright (c) 2020 SafeNet. All rights reserved.
Available HSMs:
Slot Id ->                           0
Label ->                             org.example.com
Serial Number ->                     1238696044924
Model ->                             LunaSA 7.4.0
Firmware Version ->                  7.4.0
Configuration ->                     Luna User Partition With SO (PW) Signing With Cloning Mode
Slot Description ->                  Net Token Slot
FM HW Status ->                      Non-FM

Note

Refer to Luna HSM documentation for detailed steps on creating NTLS connection, initializing the partitions, and assigning various user roles.

1Transfer the downloaded .zip file to your client workstation using pscp, scp, or other secure means

2Extract the .zip file into a directory on your client workstation.

3Extract or untar the appropriate client package for your operating system. Do not extract to a new subdirectory; place the files in the client install directory.

cvclient-min.zip

4Run the setenv script to generate a new configuration file with the necessary information for the Luna Cloud HSM service. Right-click setenv.cmd and select Run as Administrator.

Note

To add the configuration to an already installed UC client, use the –addcloudhsm option when running the setenv script.

5Run the LunaCM utility and verify that the Cloud HSM service is listed.

1Install the Luna EKM on the host system. Download the Luna EKM package from the Thales support portal using the provided DOC IDs:

• For EKM 1.5: KB0023244

• For EKM 1.4: KB0016274

• For EKM 1.3: KB0014957

Note

The installation program is Windows-based and will seamlessly guide you through the installation process.

Note

Luna EKM 1.5 introduces additional support for the crypto-user of the partition, enabling read-only operations like encryption or decryption.

2If you're using Luna EKM 1.5 and prefer a silent installation, execute the following command:

msiexec.exe /i LunaEKM.msi /qn INSTALLLEVEL=101 /l* EKM.txt

3After installing Luna EKM, manage it using the LunaEKMConfig utility, found in the Luna EKM installation folder. Employ the following commands in LunaEKMConfig:

• To register a new slot with Luna EKM: RegisterSlot

• To view the list of slots/HSMs configured with your client: ViewSlots

• To configure logging settings for Luna EKM: LogSettings

• Specify the log level using one of the following options: NONE=0, INFO=1, DEBUG=2

• Specify the name and location of the log file using this format: LogFile name: <Name and location of LogFile>

1Enable EKM Provider option

2Create and register Luna EKM Provider

3Set up credential for Luna EKM Provider

4Use Luna EKM Provider option

5Enable TDE using asymmetric key

6Rotate Keys for TDE

7Migrate TDE from SQL EKM to Luna EKM

8Use EKM on a SQL Server failover cluster

1Launch the SQL Server Management Studio.

2Connect to the SQL Server.

3Open a new query window and run the following query:

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

Note

The sp_configure is compatible with the Enterprise, Developer, and Evaluation editions of SQL Server. Executing the command on a different version may result in an error.

1Launch the SQL Server Management Studio.

2Connect to the SQL Server.

3Open a new query window and execute the following command:

CREATE CRYPTOGRAPHIC PROVIDER <Name of Cryptographic Provider>
FROM FILE = '<Location of Luna EKM Provider Library>'

Note

Ensure the Name of Cryptographic Provider is a unique user-defined name. These steps facilitate the creation and registration of the Luna EKM Provider within your SQL Server environment.

4Verify the list of EKM providers:

SELECT [provider_id], [name], [guid], [version], [dll_path], [is_enabled]
FROM [model].[sys].[cryptographic_providers]

1. 5Verify the provider properties:

SELECT [provider_id], [guid], [provider_version], [sqlcrypt_version], [friendly_name], [authentication_type],
[symmetric_key_support], [symmetric_key_persistance], [symmetric_key_export], [symmetric_key_import],
[asymmetric_key_support], [asymmetric_key_persistance], [asymmetric_key_export], [asymmetric_key_import]
FROM [master].[sys].[dm_cryptographic_provider_properties]

1Open a query window and execute the following command:

CREATE CREDENTIAL <Name of Credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<HSM Partition Password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider

Note

In Luna EKM 1.5, it is essential to note that when creating a credential for the crypto-user of the partition, the IDENTITY value must include the prefix CU_. This prefix ensures proper identification and association with the crypto-user role. Failure to include the prefix will default to using the crypto-officer role for login to the HSM partition.

2Create a credential for a crypto-officer (CO):

CREATE CREDENTIAL EKMCredential
WITH IDENTITY='EKMUser', SECRET='userpin1'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider

3Create a credential for a crypto-user (CU):

CREATE CREDENTIAL EKMCredential 
WITH IDENTITY='CU_EKMUser', SECRET='userpin2'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider

4Map the credential with the SQL service account or login:

ALTER LOGIN [Domain\Login Name] ADD CREDENTIAL '<Name of Credential created>';

Note

Make sure to reopen the EKM session when there are changes to user credentials or the HSM service, or when the client machine is deleted from the service, or in the event of a network disconnection on the machine.

1Create a test table in the MASTER database with fields:

CREATE TABLE test (
id NUMERIC(10),
name VARCHAR(50),
data VARCHAR(MAX)
)

2Execute the following command from the SQL query window:

INSERT INTO dbo.test
VALUES (1, 'some text', EncryptByKey(Key_GUID('SQL_EKM_AES_256_Key'), 'text to be encrypted'))

1Run the following SQL command:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW

Note

SQL Server does not implement FIPS 186-4. Consequently, RSA key generation by SQL Server is unsupported when the HSM is in FIPS Mode. For HSM in FIPS mode, employ the CMU utility to generate the key via the command prompt:
cmu generatekeypair -label EKM_RSA_2048_Key -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T

2Map the key in SQL Server. After the key is created in the HSM, it needs to be mapped within SQL Server to establish a connection between the HSM key and its representation in the SQL Server database. This mapping process ensures that SQL Server recognizes and can appropriately use the key created in the external HSM for encryption and decryption operations. Run the following command to map the key:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=OPEN_EXISTING

Note

Once a key is created on Luna HSM, it becomes accessible by its name from SQL Server. For instance, in the test case above, SQL_EKM_RSA_2048_Key serves as the unique key name in SQL Server, applicable for encryption and decryption operations.

1Create a test table in the MASTER database with fields:

CREATE TABLE test (
  id NUMERIC(10),
  name VARCHAR(50),
  data VARCHAR(MAX)
)

2Execute the following command from the SQL query window:

INSERT INTO dbo.test
VALUES (1, 'some text', EncryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), 'text to be encrypted'))

1Execute the following command in the SQL query window:

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

Note

SQL_EKM_RSA_2048_Key refers to an existing asymmetric key on the Luna HSM. For details on generating an asymmetric key, consult the Manage asymmetric keys section.

2Open the key before you can use it for decryption operations:

sql OPEN SYMMETRIC KEY key1 DECRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;

Note

For Microsoft SQL Server 2017, apply the recommended patch, as described in the Troubleshooting Tips section.

3Create a test table in the master database with the following fields:

CREATE TABLE test (
  id NUMERIC(10),
  name VARCHAR(50),
  data VARCHAR(MAX)
)

4Encrypt the table data using the symmetric key:

INSERT INTO dbo.test
VALUES (1, 'some text', Encryptbykey(KEY_GUID('key1'), 'text to be encrypted'))

5Decrypt the data using the symmetric key:

SELECT id, name, CONVERT(VARCHAR(MAX), DecryptByKey(data)) 
FROM dbo.test WHERE id = 1

6Close the symmetric key:

CLOSE SYMMETRIC KEY key1

1Create an asymmetric key using Luna EKM Provider:

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
CREATION_DISPOSITION=CREATE_NEW;

Note

For FIPS mode, generate the key using the CMU utility in the command prompt:
cmu generatekeypair -label EKM_RSA_2048_Key_TDE -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T

2Map the key in SQL Server:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
CREATION_DISPOSITION=OPEN_EXISTING;

3Create a credential for Luna EKM Provider:

CREATE CREDENTIAL <Name of credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<HSM partition password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

4Create a login based on the asymmetric key:

CREATE LOGIN <Name of login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;

5Map the credential to the login:

ALTER LOGIN <Name of Login> 
ADD CREDENTIAL <Name of credential>;

6Create a database encryption key:

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

7Enable TDE:

ALTER DATABASE TDE
SET ENCRYPTION ON;

8Query the status of database encryption:

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;

1Generate an asymmetric key, depending on whether your HSM is in non-FIPS or FIPS mode.

Non-FIPS mode

If your HSM is in non-FIPS mode, run the following command to generate an asymmetric key using the Luna EKM Provider:

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=CREATE_NEW

FIPS mode

If your HSM is in FIPS mode, SQL Server's native RSA key generation isn't supported. Follow these instructions instead:

• Open the command prompt and generate the key using the CMU utility provided with the HSM Client.

cmu generatekeypair -label EKM_RSA_2048_Key_TDE_Rot -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T

• After generating the key, map it in SQL Server:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=OPEN_EXISTING

2Map the key in SQL Server:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=OPEN_EXISTING

3Create a credential for Luna EKM Provider:

CREATE CREDENTIAL <Name of credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<HSM partition password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider

4Create a login based on the asymmetric key:

CREATE LOGIN <Name of login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;

5Map the credential to the login:

ALTER LOGIN <Name of Login> 
ADD CREDENTIAL <Name of credential>;

6Enable TDE key rotation:

• Rotate the Database Encryption Key (DEK):

USE tde;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_128;

• Rotate the asymmetric key used to encrypt the DEK:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
go
SELECT * FROM sys.dm_database_encryption_keys;
go

7Query the status of TDE after rotation:

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;

1Create an asymmetric key:

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_AW',
CREATION_DISPOSITION=CREATE_NEW

Note

SQL Server does not implement FIPS 186-4. For FIPS Mode, generate the key using CMU utility provided with the HSM Client:
cmu generatekeypair -label EKM_RSA_2048_Key_AW -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T

2Map the key in SQL Server:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_AW',
CREATION_DISPOSITION=OPEN_EXISTING

3Create a credential for Luna EKM Provider:

CREATE CREDENTIAL <Name of credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<HSM partition password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider

4Create a login based on the asymmetric key:

CREATE LOGIN <Name of login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW;

5Map the credential to the login:

ALTER LOGIN <Name of Login> 
ADD CREDENTIAL <Name of credential>;

6Migrate TDE from SQL to Luna EKM.

1Perform a backup of both the database and transaction logs. Ensure the completion of the backup process, and then proceed to restart the SQL database for the changes to take effect.

2Rotate the DEK:

USE AdventureWorks;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_256;

3Migrate to KEK:

USE AdventureWorks;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW
Go
SELECT * FROM sys.dm_database_encryption_keys
Go

Note

This example uses the AdventureWorks database. Please replace AdventureWorks with your actual database name.

1Back up the database and transaction logs. Restart the SQL database after backup completes.

2Migrate to KEK:

USE AdventureWorks;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW;
Go
SELECT * FROM sys.dm_database_encryption_keys
Go

3Check the status of TDE after migration:

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

1Install failover cluster. Follow the SQL Server documentation for failover cluster installation. Set up shared storage for the SQL Server Cluster, determining the size based on the required number of certificates.

2Install the Luna HSM client on both nodes after ensuring the cluster's functionality.

3Configure the HSM on both nodes. Register the same partition or Cloud HSM service on both nodes in the SQL Server Cluster.

4Install the Luna EKM client on both nodes.

5Configure the Luna EKM provider on both nodes.

6Open SQL Server Management Studio to register the Luna EKM provider on the first node.

7Establish the credential on the first node.

8Generate encryption keys using the Luna EKM provider on the first node.

9Create a table and encrypt a column with the Luna EKM key on the first node.

10Shut down the first node.

11Log in to the second node and decrypt the data encrypted on the first node.

12Confirm successful decryption of the data on the second node, ensuring EKM with Luna EKM is functioning seamlessly within the SQL Server cluster.

1Enable EKM Provider option

2Create and register Luna EKM Provider

3Set up credential for Luna EKM Provider

4Create Always On Availability Group

5Enable TDE using asymmetric key on Luna HSM

6Add encrypted database to Availability Group

7Rotate keys for TDE

1Launch the SQL Server Management Studio (SSMS) application.

2Connect to the SQL Server.

3Open a query window, and execute the following commands:

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

Note

Ensure that you are using the Enterprise, Developer, or Evaluation editions of SQL Server. The sp_configure command may not be supported on alternative versions, leading to an error if executed.

1Launch the SSMS application.

2Establish a connection to the SQL Server instance.

3Create the cryptographic provider:

CREATE CRYPTOGRAPHIC PROVIDER <Name of Cryptographic Provider>
FROM FILE = '<Location of Luna EKM Provider Library>'

Note

Replace <Name of Cryptographic Provider> with a unique user-defined name. Ensure to provide the correct location for the Luna EKM Provider Library.

4View the list of EKM providers:

SELECT [provider_id], [name], [guid], [version], [dll_path], [is_enabled]
FROM [model].[sys].[cryptographic_providers]

5View the provider properties:

SELECT [provider_id], [guid], [provider_version], [sqlcrypt_version], [friendly_name], [authentication_type],
[symmetric_key_support], [symmetric_key_persistance], [symmetric_key_export], [symmetric_key_import],
[asymmetric_key_support], [asymmetric_key_persistance], [asymmetric_key_export], [asymmetric_key_import]
FROM [master].[sys].[dm_cryptographic_provider_properties]

1Execute the following command to create the credential:

CREATE CREDENTIAL <Name of Credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<HSM partition password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

Note

Replace <Name of Credential> and <Name of EKM User> with user-defined names.

2Map the credential to the SQL service account or login:

ALTER LOGIN [Domain\Login Name]
ADD CREDENTIAL <Name of Credential created>;

Note

We recommend using a domain user on all SQL Server nodes. Ensure to reopen the EKM session if the user changes the HSM service, or the client machine is deleted from the service, or the machine suffers a network disconnection.

1Open Microsoft SQL Server Management Studio on the primary node.

2Create a database.

3Back up the database to a shared network location accessible by all SQL Server nodes.

4Open the Always On Availability Group Creation wizard and follow these steps:

• Launch the wizard.

• Specify the name for the Availability Group.

• Add the participating nodes (primary and secondary) to the Availability Group.

• Configure the necessary settings, such as failover mode and backups.

Note

Refer to the Microsoft Documentation for detailed instructions on creating the Always On Availability Group.

1Open SQL Server Management Studio (SSMS) on the primary node.

2Execute the following command in the SQL query window:

USE HSMDB;

3Execute the following command to create a symmetric key using the Luna EKM Provider:

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

Note

After creating the key on Luna HSM, you can reference it by its unique name in SQL Server. For instance, use the name SQL_EKM_AES_256_Key to identify and utilize this key within SQL Server.

1Create a test table in the HSMDB database with the following fields:

CREATE TABLE test (
id NUMERIC(10),
name VARCHAR(50),
data VARCHAR(MAX)
);

2Execute the following command:

INSERT INTO dbo.test
VALUES (1, 'some text', EncryptByKey(Key_GUID('SQL_EKM_AES_256_Key'), 'text to be encrypted'));

1Execute the following command:

SELECT id, name, CONVERT(VARCHAR(MAX), DecryptByKey(data)) 
FROM dbo.test WHERE id = 1;

2Execute the above command on the secondary replica and verify that the output is the same as the primary replica.

1Execute the following command in the SQL query window:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW;

Note

SQL Server does not adhere to FIPS 186-4, and consequently, RSA key generation by SQL Server is not supported when the Hardware Security Module (HSM) is in FIPS Mode. Please follow the provided steps for generating an RSA Key when the HSM is in FIPS mode.

2When using HSM in FIPS mode, open the command prompt and generate the key using the CMU utility provided with HSM Client:

cmu generatekeypair -label EKM_RSA_2048_Key -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T

3Map the key in SQL Server:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=OPEN_EXISTING;

Note

Once a key is created, it can be used or referred to by its name in SQL Server. For example, in the above test case, SQL_EKM_RSA_2048_Key is the unique name of the key in SQL Server, and this key name can be used for encrypt and decrypt operations.

1Execute the following command from the SQL query window:

SELECT id, name, CONVERT(varchar(MAX), DecryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), data))
FROM dbo.test WHERE id = 2;

2Execute the above command on the secondary replica and verify that the output is the same as the primary replica.

1Launch SQL Server Management Studio (SSMS) on the primary node.

2Execute the following command in the SQL query window:

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

Note

Ensure that the specified asymmetric key (SQL_EKM_RSA_2048_Key) is already present in the database before attempting to use it for encrypting a symmetric key. If the specified asymmetric key does not exist, you should create it first before proceeding with the command to create the symmetric key.

3Prior to utilizing the key, open it using the command:

OPEN SYMMETRIC KEY key1 DECRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;

Note

If you are using Microsoft SQL Server 2017, apply the recommended patch mentioned in the Troubleshooting Tips section.

4Encrypt data utilizing key1.

INSERT INTO dbo.test
VALUES (3, 'some text',
ENCRYPTBYKEY(KEY_GUID('Key1'), 'text to be encrypted'))

5Decrypt data with key1.

SELECT id, name, CONVERT(varchar(MAX),
DECRYPTBYKEY(data))
FROM dbo.test
WHERE id = 3

6Close the symmetric key.

CLOSE SYMMETRIC KEY key1

7Execute steps 3-6 on the secondary replica and verify consistency with the primary replica's output.

1Create an asymmetric key on the primary replica:

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
CREATION_DISPOSITION = CREATE_NEW;

Note

SQL Server does not adhere to FIPS 186-4, and consequently, RSA key generation by SQL Server is not supported when the Hardware Security Module (HSM) is in FIPS Mode. Please follow the provided steps for generating an RSA Key when the HSM is in FIPS mode.

2If you are operating the HSM in FIPS mode:

• Execute the following command in the command prompt to generate the key using the CMU utility provided with the HSM Client:

cmu generatekeypair -label EKM_RSA_2048_Key_TDE -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T

• Proceed to map the generated key in SQL Server using the following script:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
CREATION_DISPOSITION=OPEN_EXISTING

3Create the same asymmetric key on the secondary replica:

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',
CREATION_DISPOSITION = OPEN_EXISTING;

4Create a credential for Luna EKM Provider:

CREATE CREDENTIAL <Name_of_credential>
WITH IDENTITY = '<Name_of_EKM_User>', SECRET = '<HSM_partition_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

5Create a login based on the asymmetric key:

CREATE LOGIN <Name_of_login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;

6Map the credential to the login:

ALTER LOGIN <Name_of_Login>
ADD CREDENTIAL <Name_of_credential>;

7Execute steps 2-5 on all secondary nodes:

Note

It is essential to repeat the procedure for every node in the database. This is necessary because the TDE encryption key, credential, and login are objects residing in the master database and are not automatically replicated when including a node in the Availability Groups.

8Create a database encryption key on the primary node:

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

9Enable TDE:

ALTER DATABASE TDE
SET ENCRYPTION ON;

10Query the status of database encryption:

SELECT
  DB_NAME(e.database_id) AS DatabaseName,
  e.database_id,
  e.encryption_state,
  CASE e.encryption_state
    WHEN 0 THEN 'No DB 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;

1Launch SMS on the primary node where your encrypted database resides.

2Add database to Availability Group (such as, AGroup):

USE master;
ALTER AVAILABILITY GROUP AGroup ADD DATABASE tde;

Note

This command adds the database to the Availability Group, but it's not yet accessible on the secondary node.

3Restore the database on the secondary node from the location where you stored the encrypted database. Use the RESTORE WITH NORECOVERY parameter.

4Add the database on the secondary node:

USE master;
ALTER DATABASE tde SET HADR AVAILABILITY GROUP = AGroup;

5Query the status of database encryption and its completion percentage 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;

1Create an asymmetric key on the primary node:

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=CREATE_NEW;

Note

SQL Server does not implement FIPS 186-4. If using HSM in FIPS mode:
a. Execute the following command in the command prompt to generate the key using the CMU utility provided with the HSM Client:
cmu generatekeypair -label EKM_RSA_2048_Key_TDE_Rot -modulusBits=2048 -publicExp=65537 -sign=T -verify=T -encrypt=T -decrypt=T
b. Map the key in SQL Server:
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=OPEN_EXISTING;

2Create the same asymmetric key on a secondary node:

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

3Create a credential for Luna EKM Provider:

CREATE CREDENTIAL <Name of Credential>
WITH IDENTITY='<Name of EKM User>', SECRET='<HSM Partition Password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

4Create a login based on the asymmetric key:

CREATE LOGIN <Name of Login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;

5Map the credential to the created login:

ALTER LOGIN <Name of Login> 
ADD CREDENTIAL <Name of Credential>;

6Repeat Steps 2-5 for All Secondary Nodes.

Note

It is essential to repeat the procedure for every node in the database. This is necessary because the TDE encryption key, credential, and login are objects residing in the master database and are not automatically replicated when including a node in the Availability Groups.

7Enable TDE key rotation on the primary replica:

USE tde;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_128

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
go 
SELECT * FROM sys.dm_database_encryption_keys;
go

8Query the status of database encryption and key rotation:

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;

1Visit http://www.verisign.com/support/roots.html and download all root certificates.

2Install the downloaded certificate.

3Import the certificate into the Trusted Root Certification Authorities store.

1Reboot the OS server.

2Try to create the cryptographic provider again.

1Download the cumulative update package for SQL Server provided by Microsoft.

2Apply the cumulative update to resolve the decryption issue.

3The cumulative update package can be found at https://support.microsoft.com/en-us/help/4342123/cumulative-update-10-for-sql-server-2017.