Key Rotation

Key Rotation

Rotate encryption keys for enhanced security with DuoKey EKM

Applies to:

SQL Server 2016+TDE Encrypted DBsSysadmin Required

Overview

Key rotation is a security best practice that involves replacing encryption keys periodically. In DuoKey SQL EKM, there are two types of keys that can be rotated:

Prerequisites

• Database encrypted with DuoKey EKM
• SQL Server configured with DuoKey EKM Provider
• SQL Server Management Studio (SSMS) installed
• Sysadmin privileges on SQL Server
• Understanding of key hierarchy in TDE

Key Types and Rotation Scenarios

Master Encryption Key (MEK)

Asymmetric key stored in DuoKey platform

Database Encryption Key (DEK)

Symmetric key that encrypts the database

Scenario	What Rotates	Complexity	Operation Type
DEK Rotation Only	Database Encryption Key	Low	Online, routine
MEK Rotation Only	Asymmetric Key (MEK)	Medium	Planned, coordinated
Full Rotation	Both DEK and MEK	High	Planned, coordinated

Master key rotation is a planned operation

Rotating the master (asymmetric) key is not a routine "no downtime" task. It must be planned and coordinated: bind the new master, re-encrypt each dependent DEK by the new key, and retain the old master until every dependent database and every backup that relies on it has been migrated. Do NOT delete, rotate, or move a bound master key while any TDE database or backup still depends on it, or the database will go suspect/offline because its DEK can no longer be unwrapped.

Why Rotate Keys?

Limit Exposure

Reduces risk of key compromise over time

Compliance

Meet regulatory requirements (PCI-DSS, HIPAA)

Incident Response

Part of security incident procedures

Best Practice

Industry standard security practice

Recommended Rotation Schedule

Data Classification	Rotation Interval
Critical Data	Every 3-6 months
Sensitive Data	Every 6-12 months
Standard Data	Annually
Incident-Based	Immediately upon suspected compromise

DEK Rotation Only

The simplest rotation - regenerates the Database Encryption Key while keeping the same MEK.

1

Verify Current Status

Check Encryption StatusSQL

SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  percent_complete,
  key_algorithm,
  key_length
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'EmployeeDB';
GO

2

Regenerate DEK

Regenerate Database Encryption KeySQL

USE EmployeeDB;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_256;
GO

3

Monitor Progress

Monitor RegenerationSQL

SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  CASE encryption_state
      WHEN 4 THEN 'Key change in progress'
      WHEN 3 THEN 'Key change complete'
      ELSE 'Unexpected state'
  END AS Status,
  percent_complete
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'EmployeeDB';
GO

Note

During DEK rotation, encryption_state = 4 indicates the rotation is in progress.

Full Key Rotation (MEK + DEK)

Complete key rotation by creating a new asymmetric key and rotating the DEK.

1

Create New Asymmetric Key

Create New Key in DuoKeySQL

USE master;
CREATE ASYMMETRIC KEY SQL_EKM_Key_Rotated
FROM PROVIDER DuoKeyProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_EKM_Key_Rotated',
CREATION_DISPOSITION = CREATE_NEW;
GO

2

Create Credentials for New Key

Create Key CredentialsSQL

CREATE CREDENTIAL SQL_EKM_Key_Rotated_CRED
WITH IDENTITY = '<identity-label>',
SECRET = '<enrolled-agent-key>'
FOR CRYPTOGRAPHIC PROVIDER DuoKeyProvider;
GO

3

Create Login for New Key

Create and Map LoginSQL

CREATE LOGIN SQL_EKM_Key_Rotated_LOGIN
FROM ASYMMETRIC KEY SQL_EKM_Key_Rotated;
GO

ALTER LOGIN SQL_EKM_Key_Rotated_LOGIN
ADD CREDENTIAL SQL_EKM_Key_Rotated_CRED;
GO

4

Enable Encrypt in DuoKey

Warning

The encryption operation MUST be enabled in DuoKey before proceeding.

1. Log in to DuoKey Cockpit
2. Navigate to Keys section
3. Search for SQL_EKM_Key_Rotated
4. Enable the Encrypt operation
5. Save changes

5

Re-encrypt the DEK by the New Master Key

Bind the existing DEK to the new asymmetric key. The DEK is re-encrypted (rewrapped) by the new master; its AES_256 algorithm is unchanged.

Change DEK ProtectionSQL

USE EmployeeDB;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_Key_Rotated;
GO

Important

There is no intermediate algorithm change. The DEK stays AES_256 throughout - only the master key that protects it changes. Retain the previous master key until every dependent database and backup has been migrated to the new key.

Tip

To also issue a fresh DEK under the new master, run ALTER DATABASE EmployeeDB ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_256; after the re-encryption completes.

6

Verify Rotation

Verify Key RotationSQL

SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  encryptor_thumbprint,
  encryptor_type
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'EmployeeDB';
GO

-- Compare with new key thumbprint
USE master;
SELECT
  name AS AsymmetricKeyName,
  thumbprint,
  algorithm_desc
FROM sys.asymmetric_keys
WHERE name = 'SQL_EKM_Key_Rotated';
GO

Post-Rotation Tasks

Test Database Operations

Verify Database AccessSQL

-- Test read access
USE EmployeeDB;
SELECT TOP 10 * FROM Employees;
GO

-- Test write access
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (999, 'Test', 'User', 50000.00);
GO

-- Verify and cleanup
SELECT * FROM Employees WHERE EmployeeID = 999;
DELETE FROM Employees WHERE EmployeeID = 999;
GO

Take New Backup

Important

Always backup databases immediately after key rotation.

Backup After RotationSQL

BACKUP DATABASE EmployeeDB
TO DISK = 'C:\Backups\EmployeeDB_AfterRotation.bak'
WITH
  COMPRESSION,
  CHECKSUM,
  STATS = 10;
GO

Automated Key Rotation

SQL Agent Job for Quarterly DEK RotationSQL

USE msdb;
GO

EXEC dbo.sp_add_job
  @job_name = N'QuarterlyKeyRotation',
  @enabled = 1,
  @description = N'Rotates DEK for encrypted databases';
GO

EXEC dbo.sp_add_jobstep
  @job_name = N'QuarterlyKeyRotation',
  @step_name = N'RotateDEK',
  @subsystem = N'TSQL',
  @command = N'
      USE EmployeeDB;
      ALTER DATABASE ENCRYPTION KEY
      REGENERATE WITH ALGORITHM = AES_256;

      -- Wait for completion
      WAITFOR DELAY ''00:01:00'';

      -- Backup after rotation
      BACKUP DATABASE EmployeeDB
      TO DISK = N''C:\Backups\EmployeeDB_PostRotation.bak''
      WITH COMPRESSION;',
  @retry_attempts = 2,
  @retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
  @schedule_name = N'Quarterly',
  @freq_type = 16, -- Monthly
  @freq_interval = 1,
  @freq_recurrence_factor = 3; -- Every 3 months
GO

EXEC dbo.sp_attach_schedule
  @job_name = N'QuarterlyKeyRotation',
  @schedule_name = N'Quarterly';
GO

Caution

Automated rotation should be thoroughly tested and monitored. Always include error handling and notifications.

Emergency Key Rotation

Warning

In case of suspected key compromise, perform emergency rotation immediately.

Immediate Actions

1. Isolate Systems: Restrict access to affected systems
2. Notify Security Team: Alert information security
3. Assess Impact: Determine scope of potential compromise
4. Execute Rotation: Perform expedited rotation

Emergency Rotation ScriptSQL

-- 1. Create emergency key
USE master;
CREATE ASYMMETRIC KEY Emergency_Rotation_Key
FROM PROVIDER DuoKeyProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'Emergency_Rotation_Key',
CREATION_DISPOSITION = CREATE_NEW;
GO

-- 2. Configure credentials (follow standard steps)
-- 3. Enable encrypt operation in DuoKey
-- 4. Re-encrypt the DEK by the emergency master key
--    (DEK stays AES_256 - no intermediate algorithm change)

USE EmployeeDB;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY Emergency_Rotation_Key;
GO

-- 5. Optionally issue a fresh DEK under the new master
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;
GO

-- 6. Verify and backup
SELECT * FROM sys.dm_database_encryption_keys;
GO

BACKUP DATABASE EmployeeDB
TO DISK = 'C:\Backups\EmergencyRotation.bak'
WITH COMPRESSION;
GO

Troubleshooting

Best Practices

Key Rotation Checklist

Backup FirstAlways backup before rotating

Test in DevTest rotation in non-production first

Monitor ProgressWatch encryption_state and percent_complete

Document ChangesRecord rotation in change management

Next Steps

Backup & Restore

Update backup procedures post-rotation

Monitor Encryption

Check encryption status and progress

Database Encryption

Encrypt additional databases

See Also

• Database Encryption
• Backup and Restore
• Configuration Guide