Recently, I received a query from one of my old customers regarding a peculiar issue they were facing with encrypted columns in SQL Server replication. They explained that they had a replication setup between two SQL Server 2016 instances, with one server acting as the subscriber. However, when they ran queries on the subscriber database, the encrypted column was showing as NULL. Intrigued by this problem, I decided to investigate further and provide a solution.

After conducting some research, I discovered that in order for column level encryption to work in a replication topology, the subscriber needs to execute the CREATE SYMMETRIC KEY statement using the same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE as the publisher. The Key_guid property of the symmetric key is used to uniquely identify it. When the same Key_source and Identity_value are used, the Key_guid will be the same regardless of the server where the symmetric key is created.

To verify this, I ran the following query on both the publisher and subscriber servers:

SELECT name, key_algorithm, key_guid
FROM sys.symmetric_keys;

Upon comparing the Key_guid values, I found that they were different. This discrepancy indicated that the symmetric key used to encrypt the data on the publisher was not the same as the one being used to decrypt on the subscriber.

Upon further investigation, I discovered that the issue was caused by a difference in the Identity_value while creating the symmetric key. The script used to create the symmetric key on the subscriber had a different value for Identity_value, resulting in a mismatched Key_guid.

To resolve this issue, I recommended following the steps outlined in the article “How to: Replicate Data in Encrypted Columns (SQL Server Management Studio)” carefully. By ensuring that the symmetric key was created correctly on the subscriber, we were able to successfully replicate the data and view the encrypted column values on the subscriber database.

It is important to note that when working with encrypted columns in SQL Server replication, maintaining consistency in the creation of symmetric keys is crucial. Any discrepancies in the ALGORITHM, KEY_SOURCE, or IDENTITY_VALUE can lead to unexpected behavior and NULL values in the encrypted columns.

In conclusion, understanding the intricacies of encrypted columns in SQL Server replication is essential for ensuring data integrity and consistency. By following the correct steps and maintaining consistency in the creation of symmetric keys, you can successfully replicate data and avoid issues such as NULL values in encrypted columns.