Migrating Symmetric Keys Between SQL 2016 and SQL 2017/Azure

  • Article

There has been a recent discussion about migrating symmetric keys between instances and version of SQL Server. It doesn't seem to be a widely known process and there have been some changes between versions.

Background

There are many ways to migrate between versions of SQL Server, some of these ways have more moving parts than others. Sometimes unforeseen issues arise in our given migration methods, one of these might have to do with symmetric key encryption.

Symmetric keys in SQL Server are not exportable, so we can't back them up and restore them outside of the database backup or restore. There is, however, a way to create a duplicate key in another database if we know the inputs to the KEY_SOURCE, ALGORITHM, and IDENTITY_VALUE options. Unfortunately, if these are unknown or lost there is no way to recreate the same key.

Migrating to SQL Server 2017

Like many others, look at the list of breaking changing is probably one of the first actions completed when looking to upgrade. One of the changes made in SQL Server 2017 was the algorithm used to compute hashes of some of the input values. Since the hash output will change, the actual key will also change. This means you cannot create a duplicate key between SQL Server 2012-16 and SQL Server 2017. It is still possible to complete a database backup and restore operation to migrate the database with the key, but generating a new one will not work. The same issue will arise when migrating to Azure SQL DB.

Solution

If you need to create a duplicate symmetric key on Azure SQL DB, please open a support ticket explaining such. The creation of a duplicate symmetric key can be done through support.

If you want to create a duplicate symmetric key on SQL Server 2017 and you have the requisite input information, use trace flag 4631 (requires 2017 CU2). This trace flag can be used to change the hashing algorithm back to the 2012-2016 algorithm and create a duplicate key. Since this trace flag is instance wide, only enable it for the small window of time needed to duplicate they key then turn it off immediately after.