Why an Encrypted Column Shows NULL on a Subscriber

An encrypted column shows NULL on a subscriber when the subscriber has no key that matches the publisher’s key. The data arrives, and the decryption fails without an error. The tests below reproduce the symptom and then fix it.

What Replication Copies and What It Leaves Behind

Whenever an encrypted column shows NULL on a subscriber, the first suspect is the key. Column level encryption stores a value as bytes. Replication copies those bytes to the subscriber like any other column. It doesn’t copy the symmetric key that can decrypt them. Keys, certificates and passwords stay on the server where they were made. The subscriber has to get its own key, built from the same recipe.

That recipe has three parts: the algorithm, the key source and the identity value. The key source feeds the key material. The identity value decides the key’s unique identifier, the key_guid. The encrypted bytes carry that identifier, and DECRYPTBYKEY looks for an open key with the same one. If none matches, it returns NULL. There’s no error.

Build a Publisher and a Subscriber

The demo uses two databases on one instance, with the names EncryptPublisherDemo and EncryptSubscriberDemo. They stand in for two servers. The first script creates them.

IF DB_ID(N'EncryptPublisherDemo') IS NULL CREATE DATABASE EncryptPublisherDemo;
IF DB_ID(N'EncryptSubscriberDemo') IS NULL CREATE DATABASE EncryptSubscriberDemo;

Now create the keys. The publisher gets DemoKey. The subscriber gets a key with the same recipe, and a second key named WrongIdentityKey with the identity text changed. That second key stands for a typo in a subscriber script. The passwords here are placeholders. Use your own long passphrase and keep it out of scripts you share.

USE EncryptPublisherDemo;
GO
CREATE SYMMETRIC KEY DemoKey
    WITH ALGORITHM = AES_256, KEY_SOURCE = 'nursery demo key source', IDENTITY_VALUE = 'nursery demo identity'
    ENCRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
GO
USE EncryptSubscriberDemo;
GO
CREATE SYMMETRIC KEY DemoKey
    WITH ALGORITHM = AES_256, KEY_SOURCE = 'nursery demo key source', IDENTITY_VALUE = 'nursery demo identity'
    ENCRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
CREATE SYMMETRIC KEY WrongIdentityKey
    WITH ALGORITHM = AES_256, KEY_SOURCE = 'nursery demo key source', IDENTITY_VALUE = 'a different identity'
    ENCRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';

Compare the key_guid on Both Sides

The first check costs nothing. Read the unique identifier of each key. On a real pair of servers, run the same query on both and compare the values by eye.

SELECT N'Publisher' AS Side, name, key_guid FROM EncryptPublisherDemo.sys.symmetric_keys WHERE name NOT LIKE N'##%'
UNION ALL
SELECT N'Subscriber', name, key_guid FROM EncryptSubscriberDemo.sys.symmetric_keys WHERE name NOT LIKE N'##%'
ORDER BY name, Side;

DemoKey has the same identifier on both sides, because the recipe is the same. WrongIdentityKey differs, though only the identity text changed. The key_guid depends on the identity text, so the same text gives the same value on any server.

Encrypt, Copy and Decrypt

The next script encrypts a gate code in the publisher. It then copies the encrypted bytes into a subscriber table, which is what replication does for you. The column holds only bytes, so nothing readable crosses over.

USE EncryptPublisherDemo;
GO
CREATE TABLE dbo.Customer (CustomerID int NOT NULL PRIMARY KEY, FullName nvarchar(60) NOT NULL, GateNote varbinary(256) NULL);
OPEN SYMMETRIC KEY DemoKey DECRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
INSERT dbo.Customer VALUES (1, N'Maya Collins', ENCRYPTBYKEY(KEY_GUID(N'DemoKey'), N'Gate code 4821'));
CLOSE SYMMETRIC KEY DemoKey;
GO
USE EncryptSubscriberDemo;
GO
CREATE TABLE dbo.Customer (CustomerID int NOT NULL PRIMARY KEY, FullName nvarchar(60) NOT NULL, GateNote varbinary(256) NULL);
INSERT dbo.Customer SELECT CustomerID, FullName, GateNote FROM EncryptPublisherDemo.dbo.Customer;

Now decrypt on the subscriber, twice. First open DemoKey, the key with the matching recipe. Then open WrongIdentityKey. The script collects both answers in one list.

DECLARE @answers TABLE (KeyUsed nvarchar(40), Decrypted nvarchar(60));
OPEN SYMMETRIC KEY DemoKey DECRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
INSERT @answers SELECT N'DemoKey, same identity', CAST(DECRYPTBYKEY(GateNote) AS nvarchar(60)) FROM dbo.Customer;
CLOSE SYMMETRIC KEY DemoKey;
OPEN SYMMETRIC KEY WrongIdentityKey DECRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
INSERT @answers SELECT N'WrongIdentityKey', CAST(DECRYPTBYKEY(GateNote) AS nvarchar(60)) FROM dbo.Customer;
CLOSE SYMMETRIC KEY WrongIdentityKey;
SELECT KeyUsed, Decrypted FROM @answers;

The matching key returns the gate code. The key with another identity returns NULL, with no error and no warning. The same happens when no key is open at all. That is the symptom. The subscriber has the bytes and a key, and the key isn’t the one that made them.

Find Out Which Key a Value Needs

The encrypted bytes name their key. The first 16 bytes hold the key_guid, so one query shows which key a value needs. It also looks for that key in the current database. When the key is missing, the name column is NULL, and you know the subscriber lacks the right key.

SELECT c.CustomerID,
       CAST(SUBSTRING(c.GateNote, 1, 16) AS uniqueidentifier) AS KeyNeeded,
       k.name AS KeyName
FROM dbo.Customer AS c
LEFT JOIN sys.symmetric_keys AS k ON k.key_guid = CAST(SUBSTRING(c.GateNote, 1, 16) AS uniqueidentifier);

In the demo, the subscriber has DemoKey, so the name appears. When a subscriber returns NULL for an encrypted column, run this check first. A NULL in the last column proves that the key is missing. A NULL can also mean that the key isn’t open in the session. It can mean that the login has no permission on it, too. Open the key and test before you compare identifiers.

A Matching key_guid Is Not Proof

A matching identifier shows that the identity value is the same. It doesn’t prove that the key source is the same. Only a successful decrypt proves that. The next script builds a third database. Its key has the same identity text and a different key source. The key_guid values are equal, and the decryption still returns NULL.

IF DB_ID(N'EncryptSourceDemo') IS NULL CREATE DATABASE EncryptSourceDemo;
GO
USE EncryptSourceDemo;
GO
CREATE SYMMETRIC KEY DemoKey
    WITH ALGORITHM = AES_256, KEY_SOURCE = 'a different key source', IDENTITY_VALUE = 'nursery demo identity'
    ENCRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
CREATE TABLE dbo.Customer (CustomerID int NOT NULL PRIMARY KEY, FullName nvarchar(60) NOT NULL, GateNote varbinary(256) NULL);
INSERT dbo.Customer SELECT CustomerID, FullName, GateNote FROM EncryptPublisherDemo.dbo.Customer;
GO
SELECT N'Publisher' AS Side, key_guid FROM EncryptPublisherDemo.sys.symmetric_keys WHERE name = N'DemoKey'
UNION ALL
SELECT N'Other subscriber', key_guid FROM sys.symmetric_keys WHERE name = N'DemoKey';
GO
OPEN SYMMETRIC KEY DemoKey DECRYPTION BY PASSWORD = 'ReplaceWith-A-Long-Passphrase-1!';
SELECT CustomerID, CAST(DECRYPTBYKEY(GateNote) AS nvarchar(60)) AS Decrypted FROM dbo.Customer;
CLOSE SYMMETRIC KEY DemoKey;

Both sides show the same identifier. The decrypt returns NULL for Maya’s gate code anyway. A comparison of identifiers is a quick first check, and a successful DECRYPTBYKEY is the proof.

The Fix

First check that nothing in the subscriber was encrypted with the key you are about to drop. The KeyNeeded query above shows it.

Then drop the wrong key on the subscriber and create it again with the exact values of the publisher’s script. The algorithm, the key source and the identity value must all match. A single changed character in the identity text gives a different identifier. A change in the key source gives different key material. Store the creation script in a safe place, because the key source is a secret.

Then compare the identifiers again, as in the earlier query. When they match and the decrypt returns the text, the NULL is gone. For a replicated database, create the key before the snapshot reaches the subscriber. Then the first query after initialization already works. The password or certificate that protects the key can differ on each server. The recipe can’t.

Is It Safe to Copy a Key Recipe?

You could argue that sharing a key source between servers weakens the protection. It does spread the secret. The subscriber must then be as well protected as the publisher. Anyone who can read the recipe and the bytes can decrypt them. Keep the recipe in a secret store. Don’t paste it into tickets, chats or shared scripts.

What to Remember

An encrypted column shows NULL on a subscriber when the key’s identifier doesn’t match the one stored in the data. Compare key_guid on both sides first. Recreate the subscriber’s key with the same algorithm, key source and identity value. Remember that replication copies bytes and never keys.

When you finish, run the cleanup script. It drops the three demo databases and their keys.

USE master;
GO
ALTER DATABASE EncryptPublisherDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE EncryptPublisherDemo;
ALTER DATABASE EncryptSubscriberDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE EncryptSubscriberDemo;
ALTER DATABASE EncryptSourceDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE EncryptSourceDemo;

A NULL is not a missing value, it is a key that doesn’t match the lock.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.