Database Encryption

Database Encryption

Enable Transparent Data Encryption (TDE) using DuoKey EKM

Applies to:

SQL Server 2016+TDE EnabledSysadmin Required

Overview

Transparent Data Encryption (TDE) encrypts the storage of an entire database using a Database Encryption Key (DEK). With DuoKey EKM, this DEK is protected by an asymmetric key stored in DuoKey's cloud platform, providing an additional layer of security.

Prerequisites

• SQL Server configured with DuoKey EKM Provider
• Asymmetric key created and encryption enabled in DuoKey
• SQL Server Management Studio (SSMS) installed
• Sysadmin privileges on SQL Server

Encryption Hierarchy

Layer stack

SQL Server TDE key hierarchy

The Master Encryption Key in DuoKey protects the asymmetric key in the master database, which protects the DEK that encrypts the data files.

Create Database and Enable TDE

1

Create or Select Database

Create a new database or use an existing one:

Create DatabaseSQL

-- Create a new database
CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(128),
  LastName VARCHAR(128),
  Salary DECIMAL(10, 2),
  SSN VARCHAR(11)
);
GO

2

Create Database Encryption Key

Create a DEK protected by the asymmetric key in DuoKey:

Create DEKSQL

USE EmployeeDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_Key;
GO

Algorithm	Key Size	Recommendation
AES_128	128-bit	Good security
AES_192	192-bit	Better security
AES_256	256-bit	Recommended - strongest

Note

Always choose an AES algorithm for the DEK. AES_256 is recommended.

Tip

Use AES_256 for the strongest encryption. It provides excellent security with minimal performance impact on modern hardware.

3

Enable Encryption

Enable TDE on the database:

Enable TDESQL

ALTER DATABASE EmployeeDB
SET ENCRYPTION ON;
GO

Note

Encryption is performed asynchronously. For large databases, this process may take some time.

Tip

Encrypted databases reopen automatically after a SQL Server restart. The EKM provider keystore lets the DEK be unwrapped by the DuoKey-held master key without manual intervention.

Monitor Encryption Progress

Check Encryption StatusSQL

SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  CASE encryption_state
      WHEN 0 THEN 'No database encryption key present'
      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'
      WHEN 6 THEN 'Protection change in progress'
  END AS Status,
  percent_complete,
  encryptor_type
FROM sys.dm_database_encryption_keys;
GO

Encryption States Reference

State	Value	Description	Action Required
0	No DEK	No database encryption key present	Create DEK with CREATE DATABASE ENCRYPTION KEY
1	Unencrypted	DEK exists but encryption not enabled	Enable with ALTER DATABASE SET ENCRYPTION ON
2	Encrypting	Encryption in progress	Wait for completion, monitor percent_complete
3	Encrypted	Database fully encrypted	None - normal operation
4	Key Change	Key rotation in progress	Wait for completion
5	Decrypting	Decryption in progress	Wait for completion
6	Protection Change	Protector change in progress	Wait for completion

Verify Encryption

Verify Database EncryptionSQL

SELECT
  d.name AS DatabaseName,
  d.is_encrypted,
  dek.encryption_state,
  dek.percent_complete,
  dek.key_algorithm,
  dek.key_length
FROM sys.databases d
LEFT JOIN sys.dm_database_encryption_keys dek
  ON d.database_id = dek.database_id
WHERE d.name = 'EmployeeDB';
GO

Verify Asymmetric Key UsageSQL

USE master;
SELECT
  ak.name AS AsymmetricKeyName,
  ak.algorithm_desc,
  ak.key_length,
  cp.name AS CryptoProviderName
FROM sys.asymmetric_keys ak
INNER JOIN sys.cryptographic_providers cp
  ON ak.cryptographic_provider_guid = cp.guid;
GO

Test Data Operations

After encryption is enabled, test that data operations work transparently:

Test Encrypted DatabaseSQL

USE EmployeeDB;

-- Insert test data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary, SSN)
VALUES
  (1, 'John', 'Doe', 75000.00, '123-45-6789'),
  (2, 'Jane', 'Smith', 82000.00, '987-65-4321'),
  (3, 'Bob', 'Johnson', 68000.00, '555-12-3456');
GO

-- Query data (works transparently)
SELECT * FROM Employees;
GO

Tip

The data is automatically encrypted at rest while remaining transparent to applications.

Performance Considerations

Initial Encryption

• CPU: Moderate increase
• I/O: Significant activity
• Speed: ~1-5 GB/minute
• Database remains online

Ongoing Impact

• Read operations: 3-5% overhead
• Write operations: 5-10% overhead
• CPU usage: 1-3% increase
• AES-NI reduces impact

Performance Tips

• Schedule initial encryption during maintenance windows
• Monitor CPU and I/O during encryption
• Use AES-NI capable processors for best performance
• Ensure adequate tempdb space

Encrypt Multiple Databases

Use a script to encrypt multiple databases at once:

Batch Encryption ScriptSQL

DECLARE @dbname NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND is_encrypted = 0;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'USE [' + @dbname + '];
               CREATE DATABASE ENCRYPTION KEY
               WITH ALGORITHM = AES_256
               ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_Key;
               ALTER DATABASE [' + @dbname + '] SET ENCRYPTION ON;';

  EXEC sp_executesql @sql;
  PRINT 'Encrypted database: ' + @dbname;

  FETCH NEXT FROM db_cursor INTO @dbname;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

Disable Encryption

Warning

Disabling encryption will decrypt the database, which may take considerable time for large databases.

Disable TDESQL

-- Disable encryption
ALTER DATABASE EmployeeDB
SET ENCRYPTION OFF;
GO

-- Monitor decryption progress
SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  percent_complete
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'EmployeeDB';
GO

-- After decryption completes (encryption_state = 1)
USE EmployeeDB;
DROP DATABASE ENCRYPTION KEY;
GO

Best Practices

Security

Encrypt databases early, protect backup keys, audit key access

Operations

Test before production, monitor performance, plan for growth

Maintenance

Regular backups, test restores, keep DuoKey accessible

Troubleshooting

Next Steps

Backup & Restore

Backup encrypted databases securely

Key Rotation

Rotate encryption keys regularly

Monitor Encryption

Check encryption status and progress

See Also

• Backup and Restore
• Key Rotation
• ↗TDE (Microsoft)