SQL Server Clustering with Utimaco HSM

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

To configure EKM Provider on the cluster nodes, refer to the Enable Extensible Key Management chapter.

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

1. On the Primary Cluster Node, use the following query for creating keys using the Utimaco HSM.

USE Testdb
GO
CREATE ASYMMETRIC KEY 'RSA2048Key1'
FROM PROVIDER utimaco
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'RSA2048Key1',
CREATION_DISPOSITION=CREATE_NEW;
GO

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'));
GO

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

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

USE Testdb 
GO
SELECT FirstName, SecondName, CONVERT (varchar, DECRYPTBYASYMKEY (ASYMKEY_ID('RSA2048Key1'), CardNumber))
AS 'CardNumber' FROM Customers;
GO