While I was online on Skype last night, one of my old customers pinged me and wanted to know my opinion of a special issue which he was facing when Encrypted Column Show NULL on Subscriber.
He said that they have a replication setup between SQL Server 2016 instances. One of the servers is the subscriber. They have a table that is part of the publication where a column level encryption is used with symmetric key. As per him the replication is working fine, but the encrypted column is showing as NULL when they run queries on subscriber database.
I searched on the internet and shared this article with them. How to: Replicate Data in Encrypted Columns (SQL Server Management Studio
As per my understanding, in order for column level encryption to work in replication topology, at the subscriber, we need to execute CREATE SYMMETRIC KEY using the same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUEÂ which was created at publisher.
A symmetric key is uniquely identified using the Key_guid property of the key. When the same Key_source and Identity_value are used, we will get the same Key_guid regardless of the server where the symmetric key is created.
So, we checked the Key_guid property of the symmetric Keys by running sys. symmetric_Keys on both server and found that Key_guid property was NOT the same.
SELECT name, key_algorithm, key_guid FROM sis. symmetric_Keys
As we can see above value is E9CCD100-1A77-55E7-4D47-9F7B93A45611
On the subscriber value was 14C25100-DC28-6D3F-67E2-809F9FB441AF
The above means that the symmetric key that was used to encrypt the data on the publisher is different from the one we are using to decrypt on the destination. This explained the behavior which they were seeing?
Later, when we checked the script, the value was different for Identity_value while creating symmetric key and that’s why Key_guid was not matched.
SOLUTION
Based on the above analysis, it was clear that symmetric key was not created correctly on the subscriber. Once we followed article correctly, we were able to see data on subscriber as well.
How to: Replicate Data in Encrypted Columns (SQL Server Management Studio
Reference: Pinal Dave (https://blog.sqlauthority.com)
