9. DATABASE ENCRYPTION

Question

9. DATABASE ENCRYPTION

Gurdeep Vaidya 0

Tell me all i need to know about encryption in mssql - keys, certificates, password etc...

Trevor Waldenburg 0

Encryption

Why encrypt: protect against physical disk theft, GDPR compliance, protect sensitive data (card numbers, SSN).

Encryption hierarchy:

Windows DPAPI
    └── Service Master Key (SMK) - auto-created at install, server level
            └── Database Master Key (DMK) - one per database, created manually
                    └── Certificate / Asymmetric Key
                            └── Symmetric Key
                                    └── Data (columns, files)

TDE - Transparent Data Encryption:

Encrypts physical database FILES (.mdf, .ldf, .bak)

Does NOT encrypt data in RAM or network traffic

Logged-in user sees data normally

Backup certificate is CRITICAL - without it you cannot restore the database

sql

USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterPass123!'
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate'

-- Backup certificate (critical!):
BACKUP CERTIFICATE TDE_Cert TO FILE = 'C:\keys\TDE_Cert.cer'
    WITH PRIVATE KEY (FILE = 'C:\keys\TDE_key.pvk',
    ENCRYPTION BY PASSWORD = 'CertPass123!')

USE Northwind
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert

ALTER DATABASE Northwind SET ENCRYPTION ON

SELECT db.name, dek.encryption_state_desc, dek.percent_complete
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id

Column-level encryption:

sql

CREATE SYMMETRIC KEY ColumnKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'ColPass123!'

OPEN SYMMETRIC KEY ColumnKey DECRYPTION BY PASSWORD = 'ColPass123!'

UPDATE Customers
SET Phone_Encrypted = ENCRYPTBYKEY(KEY_GUID('ColumnKey'), Phone)

SELECT CONVERT(nvarchar(24), DECRYPTBYKEY(Phone_Encrypted)) AS Phone
FROM Customers

CLOSE SYMMETRIC KEY ColumnKey

1 answer

Your answer

Trevor Waldenburg 0 Reputation points

2026-05-16T01:57:41.2666667+00:00

Encryption

Why encrypt: protect against physical disk theft, GDPR compliance, protect sensitive data (card numbers, SSN).

Encryption hierarchy:

Windows DPAPI └── Service Master Key (SMK) - auto-created at install, server level └── Database Master Key (DMK) - one per database, created manually └── Certificate / Asymmetric Key └── Symmetric Key └── Data (columns, files)

TDE - Transparent Data Encryption:

Encrypts physical database FILES (.mdf, .ldf, .bak)

Does NOT encrypt data in RAM or network traffic

Logged-in user sees data normally

Backup certificate is CRITICAL - without it you cannot restore the database

sql

USE master CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterPass123!' CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate' -- Backup certificate (critical!): BACKUP CERTIFICATE TDE_Cert TO FILE = 'C:\keys\TDE_Cert.cer' WITH PRIVATE KEY (FILE = 'C:\keys\TDE_key.pvk', ENCRYPTION BY PASSWORD = 'CertPass123!') USE Northwind CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Cert ALTER DATABASE Northwind SET ENCRYPTION ON SELECT db.name, dek.encryption_state_desc, dek.percent_complete FROM sys.dm_database_encryption_keys dek JOIN sys.databases db ON dek.database_id = db.database_id

Column-level encryption:

sql

CREATE SYMMETRIC KEY ColumnKey WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'ColPass123!' OPEN SYMMETRIC KEY ColumnKey DECRYPTION BY PASSWORD = 'ColPass123!' UPDATE Customers SET Phone_Encrypted = ENCRYPTBYKEY(KEY_GUID('ColumnKey'), Phone) SELECT CONVERT(nvarchar(24), DECRYPTBYKEY(Phone_Encrypted)) AS Phone FROM Customers CLOSE SYMMETRIC KEY ColumnKey

Answer 1

SQL Server uses a hierarchical encryption and key management model built from symmetric keys, asymmetric keys, certificates, and passwords. Understanding this hierarchy is essential for designing and operating secure database encryption.

Core concepts

Encryption obfuscates data using a key or password so that it is useless without the corresponding decryption key or password.
Encryption does not replace access control; it limits data exposure if access controls are bypassed.
SQL Server supports multiple encryption mechanisms: Transact-SQL functions, symmetric keys, asymmetric keys, certificates, and Transparent Data Encryption (TDE).

Encryption hierarchy

SQL Server encrypts data using a layered hierarchy where each layer protects the one below it:

Service Master Key (SMK) – root of the hierarchy at the instance level.
Database Master Key (DMK) – per-database key protecting certificates and asymmetric keys.
Certificates / Asymmetric keys / Symmetric keys – used to protect data or other keys.
Data – columns, credentials, connection info, or entire databases (TDE).

Key points:

For best performance, data should be encrypted with symmetric keys rather than certificates or asymmetric keys.
SMK and all DMKs are symmetric keys.
Keys can be stored in SQL Server or externally in an Extensible Key Management (EKM) module.

Service Master Key (SMK)

Automatically generated the first time a SQL Server instance starts.
Root of the SQL Server encryption hierarchy.
Used to encrypt:
- Linked server passwords
- Credentials
- Database master keys in each database
Protected by the Windows Data Protection API (DPAPI), using a key derived from:
- The SQL Server service account’s Windows credentials
- The computer’s credentials
Can only be opened by:
- The Windows service account under which it was created, or
- A principal with access to both the service account name and its password.
SQL Server uses AES to protect the SMK (and DMK). After upgrading SQL Server, the SMK should be regenerated to upgrade to AES.

Important security behavior:

Accessing objects secured by the SMK requires either the original SQL Server service account or the original machine account.
Changing both the service account and the computer account breaks access to the SMK; encrypted data and objects cannot be decrypted without it.
There is no back door: if all access to the SMK is lost, objects and data secured by it cannot be recovered.

Database Master Key (DMK)

A symmetric key defined per database.
Used to protect:
- Private keys of certificates
- Asymmetric keys in the database
- It can encrypt data directly, but length limitations make it less practical than using symmetric keys.
For automatic decryption:
- A copy of the DMK is encrypted by the SMK and stored in both the user database and the master database.
- This copy in master is silently updated whenever the DMK changes, unless disabled.
If DMK encryption by SMK is dropped (for example, via ALTER MASTER KEY ... DROP ENCRYPTION BY SERVICE MASTER KEY):
- The DMK must be opened explicitly with a password using OPEN MASTER KEY.

Key management operations (conceptually):

Regenerate DMK: decrypts all keys encrypted by the DMK and re-encrypts them with a new DMK.
Drop service key encryption for DMK: removes SMK-based protection; requires explicit password-based opening.
Add service key encryption: creates a copy of the DMK encrypted by the SMK and stores it in the current database and master.

Symmetric keys

Single key used for both encryption and decryption.
Fast and suitable for routine encryption of sensitive data.
SQL Server stores symmetric key material encrypted with an asymmetric RSA key.
- Historically used PKCS#1 v1.5 padding.
- Starting with database compatibility level 170, uses OAEP-256 padding.
Typical usage:
- DMK or certificate encrypts a symmetric key.
- Symmetric key encrypts data (for example, column values).

Asymmetric keys

Consist of a private key and a corresponding public key.
Each key can decrypt data encrypted by the other.
More resource-intensive than symmetric keys but provide higher security.
Often used to encrypt symmetric keys for storage, rather than encrypting large data directly.

Certificates

Public key certificates (X.509) bind a public key to an identity (person, device, or service).
Issued and signed by a Certification Authority (CA).
Contain:
- Subject’s public key
- Subject identifier (for example, name, email)
- Validity period (Valid From / Valid To)
- Issuer identifier
- Issuer’s digital signature
SQL Server-created self-signed certificates follow X.509 and support X.509 v1 fields.
Primary benefits:
- Hosts trust a certificate issuer (trusted root authority) instead of managing many passwords.
- Certificates can be used to protect symmetric keys or to support features like TDE.
Certificate access is controlled via permissions (grant, revoke, deny).

Transparent Data Encryption (TDE)

Special case of encryption using a symmetric key.
Encrypts the entire database using a database encryption key (symmetric).
The database encryption key is protected by:
- A certificate protected by the DMK of the master database, or
- An asymmetric key stored in an EKM module.
TDE is independent per database; each database has its own encryption key.

Operationally (from context examples):

When restoring TDE-encrypted databases between servers:
- The TDE certificate (with private key) must be backed up from the source and restored on the target before restoring the database.
- If the certificate exists, restore works; if not, restore fails.

Transact-SQL encryption functions

Individual items can be encrypted as they are inserted or updated using T-SQL functions.
Example functions:
- ENCRYPTBYPASSPHRASE – encrypts data using a passphrase.
- DECRYPTBYPASSPHRASE – decrypts data using the same passphrase.
These functions provide password-based encryption without explicitly managing keys and certificates, but still require a password management strategy.

Key and password management practices

From the provided guidance:

Always maintain secure backups of:
- Service Master Key
- Database Master Keys
- Certificates (including private keys)
Use passwords to protect DMKs and ensure they are stored safely; losing DMK passwords can prevent opening the DMK on new servers.
When moving or copying databases that use encryption:
- Open the DMK with its password (OPEN MASTER KEY DECRYPTION BY PASSWORD = '...') so the SMK on the new instance can encrypt it and enable automatic access.
Regularly:
- Back up keys and certificates.
- Regenerate keys periodically as a security best practice.
- Plan for key rotation and re-encryption of data if keys are compromised.

Managing keys (high-level capabilities)

SQL Server tools and APIs allow:

Backing up server and database keys for recovery or migration.
Restoring previously saved keys to enable new instances to access existing encrypted data.
Deleting encrypted data if keys are irretrievably lost.
Re-creating keys and re-encrypting data if a key is compromised.
Managing keys in scale-out deployments where multiple servers share a database and encryption key.

In SMO (SQL Server Management Objects):

MasterKey.Regenerate – regenerates the DMK and re-encrypts dependent keys.
MasterKey.DropServiceKeyEncryption – removes SMK-based encryption of the DMK.
MasterKey.AddServiceKeyEncryption – adds SMK-based encryption of the DMK and stores copies in the database and master.

Critical loss scenarios

Losing access to the SMK and DMKs (and their passwords) means:
- Inability to decrypt data, credentials, and connections protected by those keys.
- No back door exists; access cannot be recovered without the keys.
Therefore, a robust backup and password management strategy is mandatory for any SQL Server encryption deployment.

References:

Share via

9. DATABASE ENCRYPTION

Encryption

1 answer

Core concepts

Encryption hierarchy

Service Master Key (SMK)

Database Master Key (DMK)

Symmetric keys

Asymmetric keys

Certificates

Transparent Data Encryption (TDE)

Transact-SQL encryption functions

Key and password management practices

Managing keys (high-level capabilities)

Critical loss scenarios

Your answer