SQL Server Clustering with Utimaco HSM

After the setup of Microsoft SQL Server cluster, one or more Utimaco HSMs can be used (along with internal/external keystore). For the illustration purpose one HSM is configured in this SQL Server Cluster setup.

To configure EKM Provider on the cluster nodes, refer section Enable Extensible Key Management

The Keys can be used from internal keystore or the external keystore, for creating keys refer section Creating Keys

RSA algorithm is not supported in FIPS mode.

1. On Primary Cluster Node use below query for creating keys using Utimaco HSM.

PROVIDER_KEY_NAME = 'RSA2048Key1',

CREATION_DISPOSITION=CREATE_NEW;

2. Insert and encrypt the data using Utimaco HSM keys.

USE Testdb GO

CREATE TABLE Customers (FirstName varchar (MAX), SecondName varchar(MAX), CardNumber varbinary(MAX));

GO

INSERT INTO Customers (FirstName, SecondName, CardNumber)

VALUES ('Kyle', 'Hood', ENCRYPTBYASYMKEY (ASYMKEY_ID('RSA2048Key1'), '2048204820482048'));

3. Follow the steps from Verify Failover Cluster Configuration to perform the Failover Scenario.

4. Now as the primary Cluster Node 1 is down, SQL Failover configuration will make Cluster Node 2 as primary. On the new Primary SQL Server, run the below query to decrypt values.

USE Testdb GO

SELECT FirstName, SecondName, CONVERT (varchar, DECRYPTBYASYMKEY (ASYMKEY_ID('RSA2048Key1'), CardNumber))

AS 'CardNumber' FROM Customers; GO