Configuring Microsoft SQL Server Transparent Database Encryption (TDE) key management with IBM Guardium Cryptography Manager for multinode cluster setup

ALTER LOGIN "<domain>\<login>" ADD CREDENTIAL GCMEKMCredential;

Where, "<domain>\<login>": Microsoft SQL Server administrator's login (replace <domain> with your domain name and <login> with your username).

1. Create a dedicated credential for TDE operations.

   CREATE CREDENTIAL GCMEKM_TDECredential
       WITH IDENTITY = 'gcm-identity',
       SECRET = 'gcm-secret'
   FOR CRYPTOGRAPHIC PROVIDER GCMEKMProvider;
   GO

   Where,

   • GCMEKM_TDECredential: Name for the SQL Server credential object.
   • IDENTITY: Placeholder value (SQL Server requirement; not used for Guardium Cryptography Manager authentication).
   • SECRET: Placeholder value (SQL Server requirement; not used for Guardium Cryptography Manager authentication).
   • GCMEKMProvider: Name of the registered EKM provider.
2. Create MSSQL Server login mapped to the asymmetric key for database encryption key operations.

   CREATE LOGIN GCMEKM_TDELogin
   FROM ASYMMETRIC KEY AsymmetricKeyReference;
   GO

   Where, GCMEKM_TDELogin: Name of the login for TDE operations.

3. Link the TDE credential to the TDE login.

   ALTER LOGIN GCMEKM_TDELogin
   ADD CREDENTIAL GCMEKM_TDECredential;
   GO

1. To test TDE functionality, you can either create a new database or use an existing one.
   • Create a new database.

     CREATE DATABASE TestDB
     GO 

     OR

   • Use an existing database.

     USE TestDB
     GO

2. Create the Database Encryption Key (DEK) for TDE.

   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER ASYMMETRIC KEY AsymmetricKeyReference;
   GO 

   Where,

   • ALGORITHM = AES_256: Uses AES 256-bit encryption (strongest available).
   • ENCRYPTION BY SERVER ASYMMETRIC KEY: The DEK is encrypted by the asymmetric key from Guardium Cryptography Manager.
3. Enable TDE on the database.

   ALTER DATABASE TestDB
   SET ENCRYPTION ON;
   GO

1. Verify encryption status.

   SELECT * FROM sys.dm_database_encryption_keys;

   Where,

   • Output shows database_id, encryption_state, key_algorithm, and other encryption details.
   • encryption_state values:
     • 0 = No encryption
     • 1 = Unencrypted
     • 2 = Encryption in progress
     • 3 = Encrypted
     • 4 = Key change in progress
     • 5 = Decryption in progress
2. View detailed encryption status query.

   SELECT (SELECT name FROM sys.databases WHERE database_id = k.database_id) as name,
       encryption_state, key_algorithm, key_length,
       encryptor_type, encryption_state_desc, encryption_scan_state_desc
   FROM sys.dm_database_encryption_keys k; 

   Where,

   • name: Database name.
   • encryption_state: Numeric encryption state.
   • key_algorithm: Encryption algorithm used (e.g., AES).
   • key_length: Key length in bits (e.g., 256)
   • encryptor_type: Type of encryptor (asymmetric key, certificate, etc.)
   • encryption_state_desc: Human-readable encryption state.
   • encryption_scan_state_desc: Current scan status during encryption.

ALTER LOGIN "<domain>\<login>" ADD CREDENTIAL GCMEKMCredential;

Where, "<domain>\<login>": Microsoft SQL Server administrator's login (replace <domain> with your domain name and <login> with your username).

1. Create backup directory (if it doe not exist):
   1. Create a folder on the primary replica, for example: C:\Backup\
   2. Ensure SQL Server service account has write permissions to this directory
2. Take database backups

   USE master;
   GO
   
   ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
   
   -- Replace 'C:\Backup\' with your actual backup directory path
   BACKUP DATABASE [YourDatabaseName] TO DISK = 'C:\Backup\YourDatabaseName.bak';
   BACKUP LOG [YourDatabaseName] TO DISK = 'C:\Backup\YourDatabaseName.trn';

   Note:

   The backup files (.bak and .trn) is created in the specified directory on the primary replica.

1. Locate the backup files on the primary replica (e.g., C:\Backup\YourDatabaseName.bak and C:\Backup\YourDatabaseName.trn)
2. Create a backup directory on each secondary replica (e.g., C:\Backup\)
3. Copy both files (.bak and .trn) from the primary replica to the same directory on each secondary replica
4. Ensure SQL Server service account on secondary replicas has read permissions to these files

CREATE ASYMMETRIC KEY AsymmetricKeyReference_Secondary
FROM PROVIDER GCMEKMProvider
WITH
CREATION_DISPOSITION = OPEN_EXISTING,
PROVIDER_KEY_NAME = 'PUBLIC-KEY--UUID';

1. Create a dedicated credential for TDE operations.

   CREATE CREDENTIAL GCMEKM_TDECredential_Secondary
       WITH IDENTITY = 'gcm-identity',
       SECRET = 'gcm-secret'
   FOR CRYPTOGRAPHIC PROVIDER GCMEKMProvider;
   GO 

   Parameters:

   • GCMEKM_TDECredential_Secondary: Name for the SQL Server credential object.
   • IDENTITY: Placeholder value (SQL Server requirement; not used for Guardium Cryptography Manager authentication).
   • SECRET: Placeholder value (SQL Server requirement; not used for Guardium Cryptography Manager authentication).
   • GCMEKMProvider: Name of the registered EKM provider.
2. Create MSSQL Server login mapped to the asymmetric key for database encryption key operations.

   CREATE LOGIN GCMEKM_TDELogin_Secondary
   FROM ASYMMETRIC KEY AsymmetricKeyReference_Secondary;
   GO

3. Link the TDE credential to the TDE login.

   ALTER LOGIN GCMEKM_TDELogin_Secondary
   ADD CREDENTIAL GCMEKM_TDECredential_Secondary;
   GO

RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\BACKUP\YourDatabaseName.bak' 
WITH NORECOVERY;

RESTORE LOG [YourDatabaseName]
FROM DISK = 'C:\BACKUP\YourDatabaseName.trn' 
WITH NORECOVERY;

ALTER DATABASE [YourDatabaseName]
SET HADR AVAILABILITY GROUP = [YourAGName];

Expected result: encryption_state should be 3 (Encrypted) and encryption_state_desc should show ENCRYPTED.

CREATE ASYMMETRIC KEY AsymmetricKeyReference_2
FROM PROVIDER GCMEKMProvider
WITH
CREATION_DISPOSITION = OPEN_EXISTING,
PROVIDER_KEY_NAME = 'PUBLIC-KEY--UUID';

ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;

-- Replace 'C:\Backup\' with your actual backup directory path
BACKUP DATABASE [YourDatabaseName] TO DISK = 'C:\Backup\YourDatabaseName_after_Encryption.bak';
BACKUP LOG [YourDatabaseName] TO DISK = 'C:\Backup\YourDatabaseName_after_Encryption.trn';
 

ALTER DATABASE [YourDatabaseName] SET HADR OFF;

1. Copy backup files
   1. Copy YourDatabaseName_after_Encryption.bak from primary replica to secondary replica (e.g., to C:\Backup\).
   2. Copy YourDatabaseName_after_Encryption.trn from primary replica to secondary replica.
2. Restore database

   RESTORE DATABASE [YourDatabaseName]
   FROM DISK = 'C:\BACKUP\YourDatabaseName_after_Encryption.bak' 
   WITH NORECOVERY;
   
   RESTORE LOG [YourDatabaseName]
   FROM DISK = 'C:\BACKUP\YourDatabaseName_after_Encryption.trn' 
   WITH NORECOVERY;
   
   ALTER DATABASE [YourDatabaseName]
   SET HADR AVAILABILITY GROUP = [YourAGName];

The database should now show as synchronized in the AG dashboard.

1. Verify encryption status on secondary replica.

   SELECT * FROM sys.dm_database_encryption_keys;

2. View detailed encryption status.

   SELECT (SELECT name FROM sys.databases WHERE database_id = k.database_id) as name,
       encryption_state, key_algorithm, key_length,
       encryptor_type, encryption_state_desc, encryption_scan_state_desc
   FROM sys.dm_database_encryption_keys k;

   Expected result: encryption_state should be 3 (Encrypted) and the database should show as synchronized.

USE master
GO

CREATE ASYMMETRIC KEY RotatedAsymmetricKeyReference
FROM PROVIDER GCMEKMProvider
WITH
CREATION_DISPOSITION = OPEN_EXISTING,
PROVIDER_KEY_NAME = 'PUBLIC-KEY--UUID'; 

CREATE CREDENTIAL GCMEKM_RotationCredential
    WITH IDENTITY = 'gcm-identity',
    SECRET = 'gcm-secret'
FOR CRYPTOGRAPHIC PROVIDER GCMEKMProvider;
GO

CREATE LOGIN GCMEKM_RotationLogin
FROM ASYMMETRIC KEY RotatedAsymmetricKeyReference; 

Where, GCMEKM_RotationLogin: Name of the login for the rotation operation (can be customized).

ALTER LOGIN GCMEKM_RotationLogin
ADD CREDENTIAL GCMEKM_RotationCredential;
GO 

USE YourDatabaseName;
GO 

Where, YourDatabaseName: Name of the database with TDE enabled (replace with your actual database name).

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY RotatedAsymmetricKeyReference; 

1. Check encryption key details.

   SELECT
       DB_NAME(database_id) as DatabaseName,
       encryption_state_desc,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
   FROM sys.dm_database_encryption_keys
   WHERE DB_NAME(database_id) = 'YourDatabaseName'; 

2. Verify the asymmetric key.

   SELECT
       name,
       thumbprint,
       algorithm_desc,
       pvt_key_encryption_type_desc
   FROM sys.asymmetric_keys
   WHERE name = 'RotatedAsymmetricKeyReference';

USE master
GO

CREATE ASYMMETRIC KEY RotatedAsymmetricKeyReference
FROM PROVIDER GCMEKMProvider
WITH
CREATION_DISPOSITION = OPEN_EXISTING,
PROVIDER_KEY_NAME = 'PUBLIC-KEY--UUID';
GO

CREATE CREDENTIAL GCMEKM_RotationCredential
    WITH IDENTITY = 'gcm-identity',
    SECRET = 'gcm-secret'
FOR CRYPTOGRAPHIC PROVIDER GCMEKMProvider;
GO

CREATE LOGIN GCMEKM_RotationLogin
FROM ASYMMETRIC KEY RotatedAsymmetricKeyReference;
GO

ALTER LOGIN GCMEKM_RotationLogin
ADD CREDENTIAL GCMEKM_RotationCredential;
GO

1. On each secondary replica, verify encryption status.

   SELECT
       DB_NAME(database_id) as DatabaseName,
       encryption_state_desc,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
   FROM sys.dm_database_encryption_keys
   WHERE DB_NAME(database_id) = 'YourDatabaseName';

2. Verify whether the asymmetric key exists.

   SELECT
       name,
       thumbprint,
       algorithm_desc,
       pvt_key_encryption_type_desc
   FROM sys.asymmetric_keys
   WHERE name = 'RotatedAsymmetricKeyReference';