My SQL Server setup looks like the following

  • Publisher : SQL Server 2017 - 14.0.3026.27
  • Subscribers : SQL Server 2008 R2 - 10.50.6220.0 & two more SQL Server 2017 servers
  • Replication Type : Merge

Issue:

Replication contains a few Encrypted tables which uses column level encryption. Encrypted data entered at the subscriber can be decrypted just fine at the publisher and rest of the subscribers after replication. Issue happens when the data is entered at the publisher, When i try to decrypt this data at the SQL 2008 R2 server, The retrieved data shows Null, Whereas i was able to view the encrypted data at the SQL Server 2017 subscribers just fine.

I have verified the following:

Publishers and all subscribers are using the same symmetric Key(Verified by Key_Guid).

the below link from Microsoft details a similar issue and the fix but it's the other way around when it comes to versions.

https://support.microsoft.com/en-us/help/4053407/sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions

Has anyone faced similar issues in their replication setups, any help is appreciated

  • 1
    Replication is not supported between 2017 and 2008/R2, so you might be out of luck here. Subscriber can be any version within two versions of the Publisher version. – vonPryz Aug 28 at 10:16
  • Thanks VonPryz, i did check the link and it say "A Subscriber to a merge publication can be any version less than or equal to the Publisher version" for merge replication, So it indeed is officially supported by Microsoft. Your comments is applicable only for Transactional replication. – Midhun C N Aug 28 at 10:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.