Experience with SQL2014 to SQL2022 where table column data has been encrypted.

Question

Experience with SQL2014 to SQL2022 where table column data has been encrypted.

Brian Chandler 0

Have a SQL2014 Express database (instance=COMP) where some columns in a table have been encrypted (using Master Key, Certificate, Symmetric etc).

Have created a SQL2022 database (instance=COMP2022) and Restored into SQL2022 a backup of SQL2014.

Queries that work in SQL2014 which decrypt using (using Master Key, Certificate, Symmetric etc) return no data in SQL2022.

ChatGPT has not been able to help.

I have DROPPED and CREATED keys and certificate, Backed them from SQL2014 and Restored into SQL2022. With no success.

Any suggestions welcomed.

Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2025-10-21T21:25:54.66+00:00

How is the database master key protected? If it is only protected by the service master key, you must export the service master key from the old server.

If I remember correctly, some encryption algorithms are no longer supported in SQL 2022, because they are too weak. In that case, you may have decrypt an re-encrypt. Or at least stay at compatibility level 120.
Lakshmi Narayana Garikapati 1,315 Reputation points Microsoft External Staff Moderator

2025-10-24T10:30:55.6033333+00:00

Hi Brian Chandler

Thank you for reaching out to the SQL Forum.

@Erland Sommarskog Thanks for your prompt responses!

@Brian Chandler,

I’m following up to check if the response provided below was helpful.

Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.

We appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi
Brian Chandler 0 Reputation points

2025-10-26T20:20:19.6+00:00

Thanks for comment.

Picking up from previous developer.

Not sure I can export service key.

My understanding is that the service key is linked to service account and since both will have different Windows service account (NT Service\MSQLSERVER vs NT Service\MSSQL$SQL2022) this will not work.

So far what I have done is to create come temporary columns in SQL2014 table, unencrypt data to those columns. Backed up and Restored database from SQL2014 to SQL2022 and the re-encrypted those columns.

This appears to work at the moment. Now going through the process of using product Advanced Installer to install my application on another PC.

While this a bit cumbersome I feel I am making progress.

I suppose I was a bit naïve (after working with Microsoft products for a little while) thinking it would have quick and painless!!!!

Brian
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2025-10-26T20:24:23.16+00:00

You can export the service master key with the command BACKUP SERVICE MASTER KEY and then import it with REMOVE SERVICE MASTER KEY.
Lakshmi Narayana Garikapati 1,315 Reputation points Microsoft External Staff Moderator

2025-10-28T09:18:47.68+00:00

Hi @Brian Chandler,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community

Thanks,

Lakshmi.

Answer recommended by moderator

0 additional answers

Your answer

Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2025-10-21T21:25:54.66+00:00

How is the database master key protected? If it is only protected by the service master key, you must export the service master key from the old server.

If I remember correctly, some encryption algorithms are no longer supported in SQL 2022, because they are too weak. In that case, you may have decrypt an re-encrypt. Or at least stay at compatibility level 120.
Lakshmi Narayana Garikapati 1,315 Reputation points Microsoft External Staff Moderator

2025-10-24T10:30:55.6033333+00:00

Hi Brian Chandler

Thank you for reaching out to the SQL Forum.

@Erland Sommarskog Thanks for your prompt responses!

@Brian Chandler,

I’m following up to check if the response provided below was helpful.

Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.

We appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi
Brian Chandler 0 Reputation points

2025-10-26T20:20:19.6+00:00

Thanks for comment.

Picking up from previous developer.

Not sure I can export service key.

My understanding is that the service key is linked to service account and since both will have different Windows service account (NT Service\MSQLSERVER vs NT Service\MSSQL$SQL2022) this will not work.

So far what I have done is to create come temporary columns in SQL2014 table, unencrypt data to those columns. Backed up and Restored database from SQL2014 to SQL2022 and the re-encrypted those columns.

This appears to work at the moment. Now going through the process of using product Advanced Installer to install my application on another PC.

While this a bit cumbersome I feel I am making progress.

I suppose I was a bit naïve (after working with Microsoft products for a little while) thinking it would have quick and painless!!!!

Brian
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2025-10-26T20:24:23.16+00:00

You can export the service master key with the command BACKUP SERVICE MASTER KEY and then import it with REMOVE SERVICE MASTER KEY.
Lakshmi Narayana Garikapati 1,315 Reputation points Microsoft External Staff Moderator

2025-10-28T09:18:47.68+00:00

Hi @Brian Chandler,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community

Thanks,

Lakshmi.

Answer 1

Remember that when you have a DATABASE MASTER KEY and copy or move the database to another server, you will need to OPEN MASTER KEY DECRYPTION BY PASSWORD='' in order for the service master key to encrypt that key and be able to open it.

You will have problems if you do not know the master key password. These passwords for the keys should be put in a safe place to be able to recall them. This holds the key to the encryption hierarchy in SQL Server. The service master key is created by the service and therefore will not be able to open the master key in a database until you open it with the password.

Share via

Experience with SQL2014 to SQL2022 where table column data has been encrypted.

0 additional answers

Your answer