Microsoft SQL Server

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 later be used by Microsoft SQL Server.

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 from a Windows command prompt on the HSM client system to verify that the partition is accessible:

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

A successful configuration will display output similar to the following:

lunacm.exe (64-bit) v10.5.0-470.

        Available HSMs:

        Slot Id ->              0
        Label ->                sql01
        Serial Number ->        1312109862216
        Model ->                LunaSA 7.7.1
        Firmware Version ->     7.7.1
        Bootloader Version ->   1.1.2
        Configuration ->        Luna User Partition With SO (PW) Key Export 
                                With Cloning Mode
        Slot Description ->     Net Token Slot
        FM HW Status ->         Non-FM

        Current Slot Id: 0

Enable partition policies 22 and 23 on PED-authenticated HSMs to allow partition activation and auto-activation.

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 software on the SQL Server host system using the Windows-based installation wizard provided with the Luna EKM installation package. The installation package contains the Luna EKM provider library, configuration utilities, and supporting components required for SQL Server integration. You can download the appropriate Luna EKM installation package from the Thales Customer Support Portal using the following document IDs:

• For EKM 1.6: KB0023244

• For EKM 1.5: KB0023244

• For EKM 1.4: KB0016274

• For EKM 1.3: KB0014957

In Luna EKM v1.5 and later, support is introduced for the Crypto User role of the partition. The Crypto User can perform cryptographic operations such as encryption and decryption but cannot create, delete, or rotate keys.

You can also install Luna EKM silently by executing the following command on the host system:
msiexec.exe /i LunaEKM.msi /qn INSTALLLEVEL=101 /l* EKM.txt

After installation, use the LunaEKMConfig command-line utility to register and configure the Luna EKM provider. This utility is available in the Luna EKM installation directory and provides commands to register HSM slots, view configured slots, and configure logging. Execute the following commands using LunaEKMConfig:

• Register a slot with Luna EKM.

RegisterSlot

• View the list of HSM slots configured for the client.

ViewSlots

• Configure logging settings.

LogSettings
LogLevel (NONE=0,INFO=1,DEBUG=2): <LogLevel>
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 <provider_name>
FROM FILE = '<full_path_to_LunaEKM_provider_library>'

• provider_name — User-defined unique name for the cryptographic provider. This name is referenced later when creating credentials and performing key management operations through the Luna EKM Provider.

• full_path_to_LunaEKM_provider_library — Absolute path to the LunaEKM.dll file installed with the Luna Client.

Example:

CREATE CRYPTOGRAPHIC PROVIDER LunaEKMProvider
FROM FILE = 'C:\Program Files\SafeNet\LunaClient\EKM\LunaEKM.dll'

Verify the list of EKM providers:

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

The query output should display the registered Luna EKM Provider, similar to the example shown below.

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]

The output should display the Luna EKM Provider properties, similar to the example shown below.

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

CREATE CREDENTIAL <credential_name>
WITH IDENTITY = '<ekm_user_name>',
SECRET = '<hsm_partition_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider

• credential_name — User-defined unique name for the SQL Server credential.

• ekm_user_name — Luna HSM partition user name. Use the prefix CU_ when creating a credential for the Crypto User (CU) role. If no prefix is specified, the Crypto Officer (CO) role is used by default.

• hsm_partition_password — Password of the Crypto Officer (CO) or Crypto User (CU) for the HSM partition.

• LunaEKMProvider — Name of the cryptographic provider defined during Luna EKM registration.

Credentials created using the CU role provide read-only cryptographic capabilities. Logins mapped with a CU credential *annot create, delete, or rotate keys on the HSM. They can only perform cryptographic operations such as encryption and decryption.

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

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.

Execute the following command from the SQL query window to verify that the symmetric key has been created:

SELECT * FROM [master].[sys].[symmetric_keys]

The output should display the newly created symmetric key, similar to the example shown below.

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;

Execute the following command from the SQL query window to decrypt the entire column of the table:

SELECT *, CONVERT(VARCHAR(MAX), DecryptByKey(data))
FROM dbo.test;

The output should display the decrypted values for all rows in the data column, similar to the example shown below.

Close the symmetric key:

CLOSE SYMMETRIC KEY key1

Create an asymmetric key using the Luna EKM Provider:

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;

Create a credential for the 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 for encryption testing:

CREATE DATABASE TDE;

Create the database encryption key (DEK):

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

Enable Transparent Data Encryption:

ALTER DATABASE TDE
SET ENCRYPTION ON;

Query the status of database encryption and monitor progress:

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;

The query output displays the encryption status of the database along with the completion percentage of the encryption process, similar to the example shown below.

Generate a new asymmetric key using the Luna EKM Provider:

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;

Create a credential for the 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 newly created 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>;

Rotate the Database Encryption Key (DEK):

This operation decrypts the database using the existing DEK and re-encrypts it with a newly generated DEK. The process may take time depending on the size of the database.

USE TDE;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_192;

Rotate the asymmetric key (Key Encryption Key – KEK) used to protect the DEK:

This operation replaces the asymmetric key used to encrypt the DEK without re-encrypting the entire database. It completes quickly because only the DEK is re-encrypted.

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;

Query the status of the database encryption keys:

GO
SELECT * FROM sys.dm_database_encryption_keys;
GO

The output displays the current status of the Database Encryption Key (DEK) and its encryption state, similar to the example shown below.

Query the database encryption state and monitor key rotation progress:

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;

The query output displays the encryption state and progress of the TDE key rotation operation.

Create an asymmetric key on the Luna HSM using the Luna EKM Provider:

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;

Create a credential for the 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>;

Back up the database and transaction logs before performing the migration.

Rotate the Database Encryption Key (DEK):

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

Migrate the DEK protection to the KEK stored on Luna HSM:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW;

Re-encrypt the DEK using the KEK stored on the Luna HSM:

USE AdventureWorks;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW;

Verify the status of the Database Encryption Key (DEK):

GO
SELECT * FROM sys.dm_database_encryption_keys;
GO

Check the status of Transparent Data Encryption 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;

The query output displays the database encryption status and completion percentage of the migration process, similar to the example shown below.

Install and configure a SQL Server Failover Cluster. Refer to the Microsoft SQL Server documentation for detailed instructions on installing and configuring a SQL Server Failover Cluster. Ensure that shared storage is properly configured and sized according to the number and size of the databases that will be protected by TDE.

Install either the Luna Network HSM Client or the Luna Cloud HSM Client on both nodes participating in the SQL Server cluster.

Configure the Luna HSM client on each node and register the same HSM partition (or Luna Cloud HSM service partition) so that both nodes can access the cryptographic keys stored in the HSM.

Install the Luna EKM Provider on both nodes:

Install the Luna EKM Provider on each node of the SQL Server cluster.

Configure the Luna EKM Provider on both nodes. Run the LunaEKMConfig utility on both nodes to register the HSM slots and verify connectivity to the Luna HSM partition.

Register the Luna EKM Provider on the primary node. Open SQL Server Management Studio (SSMS) on the primary node and register the Luna EKM Provider using the SQL commands described in the earlier section.

Create a credential that maps the SQL Server login or service account to the Luna HSM partition credentials.

Generate symmetric or asymmetric keys using the Luna EKM Provider on the primary node.

Create a test table and encrypt one of its columns using the encryption key stored in the Luna HSM.

Simulate a failover scenario. Shut down or fail over the primary node to transfer SQL Server operations to the secondary node.

Verify decryption on the secondary node. Log in to the secondary node and attempt to decrypt the data that was encrypted on the primary node.

Validate cluster operation. If the encrypted data can be successfully decrypted on the secondary node, the EKM integration using the Luna EKM Provider is functioning correctly in the SQL Server failover cluster environment.

Enable EKM Provider option

Create and register Luna EKM Provider

Set up credential for Luna EKM Provider

Create Always On Availability Group

Validate Luna EKM Integration with 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.

Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.

Execute the following command to create and register the Luna EKM Provider:

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

<Name of Cryptographic Provider> can be any unique user-defined name. The file path should point to the Luna EKM Provider library (for example, LunaEKM.dll) installed with the Luna Client software.

Verify that the Luna EKM Provider has been registered:

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

Verify the provider capabilities and 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];

The query output should display the capabilities and configuration details of the Luna EKM Provider, similar to the example shown below.

Execute the following command to create the credential:

CREATE CREDENTIAL <credential_name>
WITH IDENTITY = '<ekm_user_name>',
SECRET = '<hsm_partition_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

• credential_name — User-defined unique name for the SQL Server credential.

• ekm_user_name — Luna HSM partition user name. Use the prefix CU_ when creating a credential for the Crypto User (CU) role. If no prefix is specified, the Crypto Officer (CO) role is used by default.

• hsm_partition_password — Password of the CO or CU for the HSM partition.

• LunaEKMProvider — Name of the cryptographic provider defined during Luna EKM registration.

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 (SSMS) on the primary node and connect to the SQL Server instance.

Create a database that will participate in the Always On Availability Group.

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

Launch the Always On Availability Group Creation Wizard in SQL Server Management Studio and configure the Availability Group by selecting the participating nodes and the database created earlier. After the Availability Group is successfully created, the dashboard displays the participating nodes and their synchronization status.

An example of the Always On Availability Group dashboard is shown below. In this example, two nodes are configured: Primary and Secondary.

Open SQL Server Management Studio (SSMS) on the primary node and connect to the SQL Server instance.

Switch to the database participating in the Availability Group.

USE HSMDB;

Create a symmetric key stored on the Luna HSM:

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;

Verify the symmetric keys available in the database:

SELECT *
FROM [HSMDB].[sys].[symmetric_keys];

Create a test table:

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

Encrypt data using the symmetric key stored on the Luna HSM:

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

Decrypt the encrypted data:

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

Execute the same query on the secondary replica and verify that the output matches the primary replica.

Switch to the Availability Group database.

USE HSMDB;

Create an asymmetric key using the Luna EKM Provider:

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;

Verify the asymmetric keys available in the database:

SELECT * FROM [HSMDB].[sys].[asymmetric_keys];

Encrypt data using the asymmetric key:

INSERT INTO dbo.test
values ( 2,'some text',
EncryptByAsymKey (AsymKey_Id ('SQL_EKM_RSA_2048_Key'), 'text to be encrypted'));

Decrypt the encrypted data:

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

Run the same query on the secondary replica and verify that the results match those on the primary replica.

Switch to the Availability Group database.

USE HSMDB;

Create a symmetric key encrypted by the asymmetric key:

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

Open the symmetric key before using it:

OPEN SYMMETRIC KEY key1
DECRYPTION BY ASYMMETRIC KEY SQL_EKM_RSA_2048_Key;

Encrypt data using the symmetric key:

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

Decrypt the encrypted data:

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

Close the symmetric key:

CLOSE SYMMETRIC KEY key1;

Execute the encryption and decryption steps on the secondary replica and confirm that the results match those from the primary replica.

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;

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

Repeat the credential, login, and map configuration on all secondary replicas.

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 that will use TDE:

CREATE DATABASE TDE;

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

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 the credential, login, and map configuration on all secondary replicas.

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:

• Rotate the Database Encryption Key (DEK):

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

• Rotate the asymmetric key (Key Encryption Key – KEK) on Luna HSM:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;

• Check the status of the DEK and current KEK:

SELECT * FROM sys.dm_database_encryption_keys;

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;

Open SQL Server Management Studio and connect to the SQL Server instance where the Luna EKM Provider is currently configured.

Perform a full backup of all databases that use Transparent Data Encryption (TDE) or column encryption through the Luna EKM Provider to ensure recovery is possible if the upgrade fails.

Create a directory where the new Luna EKM package will be installed temporarily, using a location different from the default Luna Client installation directory.

C:\newekm

Install Luna EKM v1.6 in the temporary directory created in the previous step.

Remove the credentials associated with SQL Server logins that use the Luna EKM Provider, ensuring that the logins themselves are not deleted because only the credential mapping must be removed before replacing the EKM provider library.

ALTER LOGIN <LoginName>
DROP CREDENTIAL <EKM_Credential_Name>

Example:

ALTER LOGIN [WIN-GQOLVL7303G\Administrator]
DROP CREDENTIAL LunaEKMCredential;

ALTER LOGIN Administrator
DROP CREDENTIAL LunaEKMCredential_TDE;

Drop the credentials previously associated with the Luna EKM Provider, ensuring that the credential names are recorded because they must be recreated after the new EKM provider DLL is registered.

DROP CREDENTIAL LunaEKMCredential_TDE;
DROP CREDENTIAL LunaEKMCredential;

Drop the existing Luna EKM cryptographic provider registered in SQL Server while noting the provider name because the same name must be reused when registering the updated provider library.

DROP CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

Create a backup of the existing LunaEKM.dll file located in the Luna Client installation directory to ensure the previous provider library can be restored if necessary.

C:\Program Files\SafeNet\LunaClient\EKM

Copy the new LunaEKM.dll file from the temporary installation directory and replace the existing DLL in the Luna Client EKM directory.

Register the Luna EKM Provider again using the updated LunaEKM.dll library while using the same provider name that was previously registered.

CREATE CRYPTOGRAPHIC PROVIDER <ProviderName>
FROM FILE = 'C:\Program Files\SafeNet\LunaClient\EKM\LunaEKM.dll'

Example:

CREATE CRYPTOGRAPHIC PROVIDER LunaEKMProvider
FROM FILE = 'C:\Program Files\SafeNet\LunaClient\EKM\LunaEKM.dll'

Recreate the credentials that were removed earlier using the same credential names and EKM user identities so that the SQL Server configuration remains unchanged.

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

Examples:

CREATE CREDENTIAL LunaEKMCredential
WITH IDENTITY='EKM', SECRET='userpin1'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

CREATE CREDENTIAL LunaEKMCredential_TDE
WITH IDENTITY='EKM', SECRET='userpin1'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider;

Map the recreated credentials back to the SQL Server logins that were altered earlier so that the logins regain access to the Luna EKM Provider.

ALTER LOGIN <LoginName>
ADD CREDENTIAL <CredentialName>

Examples:

ALTER LOGIN [WIN-GQOLVL7303G\Administrator]
ADD CREDENTIAL LunaEKMCredential;

ALTER LOGIN Administrator
ADD CREDENTIAL LunaEKMCredential_TDE;

If SQL Server Always On Availability Groups are configured, repeat steps 3 through 12 on all SQL Server nodes that participate in the availability group.

Restart the SQL Server service and verify that TDE, column-level encryption, and key encryption operations are functioning correctly using the upgraded Luna EKM Provider.

Verify Transparent Data Encryption status:

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;

Verify key encryption using an asymmetric key on the HSM:

USE master;
OPEN SYMMETRIC KEY key1 DECRYPTION BY ASYMMETRIC KEY SQL_EKM_RSA_2048_Key;
SELECT *,CONVERT(varchar(max), DecryptByKey(data)) FROM dbo.testA1;

Verify column encryption using an asymmetric key on the HSM:

USE master;
SELECT *, CONVERT(varchar(MAX),
DecryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), data))
FROM dbo.testA2;

Verify column encryption using a symmetric key on the HSM:

USE master;
SELECT *,CONVERT(varchar(max), DecryptByKey(data)) FROM dbo.test;

Re-encrypt the Database Encryption Key (DEK) for the TDE database using the existing asymmetric key stored in the HSM.

USE TDE;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE;

Check the status of the DEK and the Key Encryption Key (KEK).

GO
SELECT * FROM sys.dm_database_encryption_keys;
GO

Query the encryption state and completion percentage 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;

Open the symmetric key that is currently protected by the existing asymmetric key stored in the HSM.

USE master;
OPEN SYMMETRIC KEY key1 DECRYPTION BY ASYMMETRIC KEY SQL_EKM_RSA_2048_Key;

Create a new asymmetric key that will be used to re-encrypt the symmetric key.

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

Re-encrypt the symmetric key using the newly created asymmetric key.

ALTER SYMMETRIC KEY key1
ADD ENCRYPTION BY ASYMMETRIC KEY SQL_New_EKM_RSA_2048_Key;

Close the existing symmetric key session and reopen it using the newly created asymmetric key. Execute the following SQL statements sequentially:

CLOSE SYMMETRIC KEY key1;

OPEN SYMMETRIC KEY key1

DECRYPTION BY ASYMMETRIC KEY SQL_New_EKM_RSA_2048_Key;

Validate that encrypted data can still be decrypted successfully.

SELECT *, CONVERT(varchar(max), DecryptByKey(data)) FROM dbo.testA1;

Add a temporary column that will store the decrypted values from the existing encrypted column.

ALTER TABLE dbo.testA2
ADD DecryptedData varchar(max);

Populate the temporary column with decrypted values.

UPDATE dbo.testA2
SET DecryptedData = DecryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), data);

Remove the existing encrypted column from the table.

ALTER TABLE dbo.testA2
DROP COLUMN data;

Create a new column that will store the data encrypted using the RSA_OAEP mechanism.

ALTER TABLE dbo.testA2
ADD data varchar(max);

Re-encrypt the decrypted values using the asymmetric key stored in the HSM.

UPDATE dbo.testA2
SET data = EncryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), DecryptedData);

Remove the temporary column that stored decrypted data.

ALTER TABLE dbo.testA2
DROP COLUMN DecryptedData;

Verify that the encrypted column contains valid encrypted data.

SELECT * FROM dbo.testA2;

Validate that the encrypted column can be successfully decrypted.

SELECT *,CONVERT(varchar(MAX),
DecryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), data))
FROM dbo.testA2;

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.

Generate the key directly on the HSM using the CMU utility provided with the Luna Client, and then map the key in SQL Server.

Run the following command from a command prompt on the HSM client system:

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

After generating the key on the HSM, map the key in SQL Server using the following command:

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

Once the key is created on the Luna HSM, it can be referenced in SQL Server using its mapped name. In this example, SQL_EKM_RSA_2048_Key becomes the SQL Server identifier for the key stored on the HSM and can be used for encryption and decryption operations.