Skip to content
Karthik Menon
6 min read

SQL Server Cell-Level Encryption and Key Recovery

Updated

SQL ServerSecurityCompliance

Cell-level encryption comes up a lot in environments that need to satisfy HIPAA or PCI compliance requirements, where specific columns - name, email, phone number, and similar personally identifiable fields - need to be encrypted at rest, rather than encrypting the entire database. This post is a practical look at how I set it up, how the key hierarchy works, and a real problem I ran into after managing this across a large number of databases, which depends on having a working key hierarchy and a tested recovery path. Encryption supports a security program; it does not establish HIPAA or PCI compliance by itself.

Why cell-level, and not just TDE?

It's worth being clear about the difference up front, since the two get confused:

  • Transparent Data Encryption (TDE) encrypts the entire database at the file level. It protects against someone stealing the physical .mdf/.bak file, but once the database is online and you have access to query it, the data is fully readable - TDE is transparent to queries by design.
  • Cell-level encryption encrypts specific column values, and stays encrypted even to someone querying the table directly, unless they explicitly decrypt it using the right key. A SELECT * against an encrypted column returns ciphertext, not the real value.

For compliance requirements that specifically call out protecting fields like name, email, or phone number - rather than "the whole database" - cell-level encryption is the more targeted tool, and sometimes both are used together (TDE for the file, cell-level for specific sensitive columns).

The key hierarchy

Cell-level encryption in SQL Server is built on a chain of keys, and understanding the chain matters, because it's exactly what causes the recovery problem I'll get to further down.

Each layer protects the layer below it. To decrypt a column value, SQL Server needs to open the symmetric key, which requires the certificate, which requires the database master key to be available and open.

Setting it up

This is the basic setup I use to create the key chain for a database that needs cell-level encrypted columns:

USE MyDB;

-- 1. Database Master Key - protects everything below it
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPasswordHere>';

-- 2. Certificate - protected by the master key
CREATE CERTIFICATE CellEncryptionCert
WITH SUBJECT = 'Certificate for cell level encryption on MyDB';

-- 3. Symmetric key - the key actually used to encrypt/decrypt column values
CREATE SYMMETRIC KEY CellEncryptionKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CellEncryptionCert;

A few notes on the choices here:

  • AES_256 is the algorithm I default to - it's the strongest symmetric option SQL Server supports for this feature, and there's rarely a good reason to choose a weaker one for PII data.
  • The password values above are placeholders - in practice these should be strong, randomly generated, and stored in whatever secrets management process your organization already uses for database credentials. Never hardcode them in a script that ends up in source control.

Encrypting and decrypting a value

Once the key chain exists, encrypting a column value looks like this:

OPEN SYMMETRIC KEY CellEncryptionKey
DECRYPTION BY CERTIFICATE CellEncryptionCert;

UPDATE dbo.Customers
SET EncryptedEmail = EncryptByKey(Key_GUID('CellEncryptionKey'), Email)
WHERE CustomerId = 1001;

CLOSE SYMMETRIC KEY CellEncryptionKey;

And reading it back out requires opening the same key and calling DecryptByKey:

OPEN SYMMETRIC KEY CellEncryptionKey
DECRYPTION BY CERTIFICATE CellEncryptionCert;

SELECT
    CustomerId,
    CONVERT(NVARCHAR(100), DecryptByKey(EncryptedEmail)) AS Email
FROM dbo.Customers;

CLOSE SYMMETRIC KEY CellEncryptionKey;

The example assumes an existing dbo.Customers table with a varbinary encrypted column. It does not remove the original plaintext Email value. A real migration also needs a plan for plaintext copies, access permissions, application changes, and backup retention.

Without opening the symmetric key first, EncryptedEmail just returns as unreadable binary data - which is exactly the point. Anyone querying the table without the key sees ciphertext, not PII.

Recovery across instances

The database master key (DMK) is included in the database backup. Its protection by the source service master key (SMK) does not automatically work on another instance. Test the destination key-opening procedure before cutover, including after an Availability Group failover.

With the known DMK password, a destination procedure can use:

USE MyDB;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PasswordFromSecretStore>';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;

This is an illustrative example, not a tested recovery runbook for every environment. Use an isolated restore and a known encrypted test value to verify decryption with the intended application identity.

Recovering from a lost password

Managing keys across roughly 300 encrypted databases exposed gaps in password records. A lost password is different from losing every usable copy of the key. If SQL Server can still open the DMK through the instance SMK, an administrator may add password protection or regenerate the DMK. If the existing hierarchy is inaccessible, regeneration cannot reconstruct it from nothing.

ALTER MASTER KEY REGENERATE is not a recent SQL Server feature; Microsoft's documentation already discusses it in the context of SQL Server 2012. Regeneration re-protects dependent keys and can be resource intensive. Back up the keys first, schedule the operation, and do not use FORCE as a routine recovery shortcut: inaccessible dependent keys can be lost. See ALTER MASTER KEY.

sp_control_dbmasterkey_password stores a credential on the instance where it runs. It does not transport credentials to a restore target. Provision and protect any required credential separately on each destination; see Microsoft's credential procedure documentation.

Migration and recovery checklist

  • Inventory encrypted databases and the secret-store references for their keys.
  • Back up master keys and certificates/private keys where applicable; protect those backups separately from database backups.
  • Restore to an isolated destination and validate decryption using least-privileged application access.
  • Record the SQL Server version, destination prerequisites, validation result, and recovery owner.
  • Keep a rollback path until application validation and recovery checks pass.

Example status: documentation reviewed in October 2026; examples were not executed against a SQL Server instance during this website update. Validate against your version and security model before use.