0

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?

Improve this question
4
  • Did you backup and restore the database master key from the SQL 2005 database to the SQL 2018 database? That is needed if the SQL Server service accounts are different. Commented Oct 26, 2022 at 22:38
  • 2
    There is no such thing as SQL Server 2018, do you mean 2017 or 2019? Commented Oct 27, 2022 at 0:14
  • "We recently migrated" how did you do that? More detail is needed on what your encryption setup is. See also stackoverflow.com/a/46413312/14868997 Commented Oct 27, 2022 at 0:36
  • @J.D. Sorry, my folder said "Sql Tools 18" so I figured it was 2018, lol. Commented Oct 27, 2022 at 14:08

1 Answer 1

Reset to default
1

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.

Improve this answer
4
  • Wasn't TDE introduced in 2008? Since the migration is from 2005, I have a feeling that the application is using any of those EncryptByCert() (etc) functions. I believe that the principal is the same with how to handle the keys, though... Commented Oct 27, 2022 at 8:20
  • :) I didn't mention TDE, I called out column level encryption. Transparent description hierarchy isn't TDE, it comes into play if all encryptors can be transparently decrypted starting with the SMK in order to open the keys for you, aka not manually OPEN [blah]. Commented Oct 27, 2022 at 10:41
  • Worked perfectly! Thank you! :) Commented Oct 27, 2022 at 15:23
  • Thanks Sean! I sort of mentally checked out at the word "Transparent". :-) Commented Oct 28, 2022 at 9:24

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.