Microsoft SQL

Set up Luna HSM

Configure Luna EKM

Configure SQL Server

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

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

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

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

Run 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

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

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

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

Extract 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

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

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

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

Install 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

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

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

If 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

After 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>

Enable EKM Provider option

Create and register Luna EKM Provider

Set up credential for Luna EKM Provider

Use Luna EKM Provider option

Enable TDE using asymmetric key

Rotate Keys for TDE

Migrate TDE from SQL EKM to Luna EKM

Use EKM on a SQL Server failover cluster

Launch the SQL Server Management Studio.

Connect to the SQL Server.

Open 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

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.

Launch the SQL Server Management Studio.

Connect to the SQL Server.

Open a new query window and execute the following command:

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

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.

Verify the list of EKM providers:

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

1. Verify 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]

Open 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

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.

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

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

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

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

Map the credential with the SQL service account or login:

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

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.

Create a test table in the MASTER database with fields:

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

Execute 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'))

Run 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

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

Map 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

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.

Create a test table in the MASTER database with fields:

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

Execute 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'))

Execute 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;

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.

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

OPEN SYMMETRIC KEY key1 DECRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;

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

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

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

Encrypt the table data using the symmetric key:

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

Decrypt the data using the symmetric key:

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

Close the symmetric key:

CLOSE SYMMETRIC KEY key1

Create 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;

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

Map 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;

Create 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;

Create a login based on the asymmetric key:

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

Map the credential to the login:

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

Create 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;

Enable TDE:

ALTER DATABASE TDE
SET ENCRYPTION ON;

Query 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;

Generate 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

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

Create 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

Create a login based on the asymmetric key:

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

Map the credential to the login:

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

Enable 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

Query 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;

Create 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

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

Map 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

Create 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

Create a login based on the asymmetric key:

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

Map the credential to the login:

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

Migrate TDE from SQL to Luna EKM.

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

Rotate the DEK:

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

Migrate 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

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

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

Migrate 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

Check 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

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

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

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

Install the Luna EKM client on both nodes.

Configure the Luna EKM provider on both nodes.

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

Establish the credential on the first node.

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

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

Shut down the first node.

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

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

Enable EKM Provider option

Create and register Luna EKM Provider

Set up credential for Luna EKM Provider

Create Always On Availability Group

Enable TDE using asymmetric key on Luna HSM

Add encrypted database to Availability Group

Rotate keys for TDE

Launch the SQL Server Management Studio (SSMS) application.

Connect to the SQL Server.

Open 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

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.

Launch the SSMS application.

Establish a connection to the SQL Server instance.

Create the cryptographic provider:

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

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

View the list of EKM providers:

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

View 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]

Execute 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;

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

Map the credential to the SQL service account or login:

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

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.

Open Microsoft SQL Server Management Studio on the primary node.

Create a database.

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

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

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

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

Execute the following command in the SQL query window:

USE HSMDB;

Execute 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;

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.

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

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

Execute the following command:

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

Execute the following command:

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

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

Execute 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;

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.

When 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

Map 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;

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.

Execute 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;

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

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

Execute 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;

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.

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

OPEN SYMMETRIC KEY key1 DECRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;

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

Encrypt data utilizing key1.

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

Decrypt data with key1.

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

Close the symmetric key.

CLOSE SYMMETRIC KEY key1

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

Create 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;

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.

If 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

Create 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;

Create 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;

Create a login based on the asymmetric key:

CREATE LOGIN <Name_of_login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;

Map the credential to the login:

ALTER LOGIN <Name_of_Login>
ADD CREDENTIAL <Name_of_credential>;

Execute steps 2-5 on all secondary nodes:

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.

Create 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;

Enable TDE:

ALTER DATABASE TDE
SET ENCRYPTION ON;

Query 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;

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

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

USE master;
ALTER AVAILABILITY GROUP AGroup ADD DATABASE tde;

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

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

Add the database on the secondary node:

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

Query 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;

Create 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;

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;

Create 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;

Create 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;

Create a login based on the asymmetric key:

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

Map the credential to the created login:

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

Repeat Steps 2-5 for All Secondary Nodes.

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.

Enable 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

Query 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;

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

Install the downloaded certificate.

Import the certificate into the Trusted Root Certification Authorities store.

Reboot the OS server.

Try to create the cryptographic provider again.

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

Apply the cumulative update to resolve the decryption issue.

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