Cell-Level Encryption in SQL Server: What I Use for HIPAA/PCI Columns, and the Key Recovery Problem That Bit Us

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 later releases of SQL Server actually fixed.

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 KEY_SOURCE = '<A memorable but not-guessable phrase>',
IDENTITY_VALUE = '<An identity value used internally by the algorithm>',
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 and key source 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;

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.

Where this gets tricky: backup and restore

Here's the part that matters most operationally, and the reason I'm writing this post.

If you take a backup and restore it to the same server (or same instance where the master key is already open/available), the encrypted values can still be decrypted normally, because the key chain is already present.
If you restore that backup to a different server - a new environment, a DR site, a migration target - the master key doesn't automatically come with it in a usable state. You have to explicitly load/open the master key on the new server before any decryption will work.

That distinction is easy to overlook until the day you actually need to restore somewhere new and discover the data won't decrypt.

The problem I actually ran into

Managing this across a single database is manageable. Managing it across 300+ encrypted databases, over years, is a different story. Keys get rotated, documented in different places by different people, or in a few unfortunate cases, simply lost track of over time. When that happens, a database that needs to move to a new environment - which is exactly when you need the key most - becomes stuck. The data was still safely encrypted (which is the point), but without the right master key password, it couldn't be decrypted anywhere new, and in older SQL Server versions there wasn't a clean way to recover from that.

The fix: regenerating the master key

This is what changed things for me. In more recent SQL Server versions, you can regenerate the database master key with a new password, without needing the old one, and the existing certificate and symmetric key layers remain intact underneath it:

USE MyDB;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '<NewStrongPassword>';

In practice, this meant that even in situations where a master key password had been lost or forgotten, I could regenerate it with a new, properly documented password and get the database back into a known, recoverable state - without having to re-encrypt every column from scratch or lose the data. From what I've seen, this capability wasn't available in earlier releases (SQL Server 2014 and similar generations), which made key loss a much more serious problem back then.

Once the master key is regenerated, the next step is making sure the new key can travel with the database to wherever it needs to be restored:

USE master;
EXEC sp_control_dbmasterkey_password
    @db_name = N'MyDB',
    @password = N'<NewStrongPassword>',
    @action = N'add';

This registers the master key password against the server-level service master key, so that when the database is restored elsewhere, SQL Server has a documented, working path to open the master key automatically (or with the password on hand), instead of the encrypted columns becoming unreadable on the new server.

What I'd recommend if you're setting this up today

Document the master key password the moment it's created, in your organization's actual secrets management system - not in a script file, a spreadsheet, or a ticket comment.
Treat ALTER MASTER KEY REGENERATE as your safety net, not your first move - it's there for recovery scenarios, and it's good to know it exists, but a lost key event is still disruptive enough that prevention (proper key documentation) is worth far more than the cure.
Before any migration or DR restore involving encrypted columns, confirm the master key password is known and tested before the restore, not after - that's the point where a missing key turns from an inconvenience into an incident.
If you're managing this across many databases, keep a simple inventory of which databases have cell-level encryption enabled and where their key documentation lives. It sounds obvious, but at scale this is exactly the kind of thing that quietly falls out of date.

More posts on SQL Server performance, security, and AI-assisted DBA automation are on the way.