SQL Server Encryption – A problem solving journey

Working in Software Development you always tend to rely a bit on your search engine of choice to help you out from time to time. There’s no way anyone can remember every single piece of syntax that they might need so being able to quickly lookup how to do something is very useful. It’s also generally the case that someone else has already suffered through the same experience you currently are and took the time to post about it so that you can get the benefit of their experience.

But what happens when the particular problem you’ve come across doesn’t seem to have a solution that you can find? That’s when you need to reach out to a community and try and get some additional help.

This happened to me recently – we were trying to produce a proof of concept to migrate a database from an on-premise SQL Server 2008R2 instance to Azure SQL Database. Ordinarily this is a fairly straightforward task but this particular database used column level encryption and no matter what we did we were unable to get the encrypted data to decrypt correctly at the Azure destination. I posted a question on SQL Server Central and soon found myself engaging with the site owner, Steve Jones. Steve’s help and a couple of seemingly unrelated articles lead us to a solution and when I explained it to him Steve asked me to write it up as an article for the website.

I did so and now maybe documenting my pain will prevent someone else having the same issue. The long and short of the solution is that the encryption algorithm used by SQL Server changed from version 2017 onwards which means you can’t recreate a certificate on a version prior to that on those versions. Here’s my full article on the topic.