SQL Server Migration – Common Issues with SQL Server DB Migration

sql serversql-server-2005

I have two instances of Microsoft SQL Server. One is running MSSQL 2005 and the other is running MSSQL 2019.

We recently migrated our DB engine from 2005 to 2019. We have a webserver that runs off IIS that makes a connection to this DB engine. We have a table called "Intranet" that contains encrypted columns (username, passwords, etc.) When a user tries to login to the webserver via our front-end, they need to use their username and password.

This worked fine on our MSSQL 2005 instance. However, now that we have pointed our IIS webserver at our MSSQL 2019 instance, we receive the following error when attempting to login:

Please create a master key in the database or open the master key in the session before performing this operation. .Net SqlClient Data Provider SQL Exception: SQL Execute Reader - Record Set

I have googled this error and have read that it may have to do with the symmetric key/certificate of the DB (in this case, the Intranet DB).

One post I read said to use the OPEN MASTER KEY command, but I do not know the password that the master key was encrypted with (or if it was even encrypted, for that matter)

So, I am at a complete loss here. Not sure what is going on or where to even start. Any suggestions?

Best Answer

I have googled this error and have read that it may have to do with the symmetric key/certificate of the DB (in this case, the Intranet DB).

You're running into the common issue of transparent decryption hierarchy failing due to a mismatch in the database master key (DMK) decryption for the database using the service master key (SMK).

It's telling you it can't open whatever symmetric/asymmetric/certificate used to encrypt the column because it wasn't able to decrypt (open) the database master key. This is due to the change in service master key between the instances (which is normal and expected).

One post I read said to use the OPEN MASTER KEY command, but I do not know the password that the master key was encrypted with (or if it was even encrypted, for that matter)

That's generally the case, and it's a bummer.

So, I am at a complete loss here. Not sure what is going on or where to even start. Any suggestions?

You have a few options:

  1. If the 2005 instance is still around, backup the DMK and restore it to the database on the 2017/2019 instance. (Recommended)
  2. If the 2005 instance is still around, backup the SMK and restore it to the 2017/2019 instance. This one is too heavy handed for me, it will affect all secrets at the server level (including other database master keys).

There are other possible avenues, but these are the supported operations.

Related Question