Delete and Re-create Encryption Keys (SSRS Configuration Manager) Explains how you can replace a symmetric key with a new version, and how to start over if symmetric keys cannot be validated. Add and Remove Encryption Keys for Scale-Out Deployment (SSRS Configuration Manager) Explains how to add and remove encryption keys to control which. The long story here is that when changing the SQL Server Reporting Services account, you need to do that from the SQL Server Services Reporting Manager as that will prompt you to back up the Symmetric encryption key that SQL Server Reporting Services uses and restore it with the new service account user. I've set the machineKey tag in RSPortal.exe.config, web.config and in rsreportserver.config.I've tried deleting the encryption key's and restarting it and it did not help. Any ideea how to. How to Move a TDE Encryption Key to Another SQL Server Instance. If you want to restore an encrypted database backup to a new instance you need to import the certificate from the source instance where the encrypted backup was created. GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Provide Strong Password Here For Database Master Key'; GO.
- Ssrs Generate A New Encryption Key In Computer
- Ssrs Machine Key
- Ssrs Generate A New Encryption Key Iphone
- Ssrs Restore Encryption Key Hangs
- Ssrs Encryption Key
By: Mohammed Moinudheen | Updated: 2018-08-17 | Comments (6) | Related: More >Restore
Problem
We need to restore a database using a backup from a different SQL Server where TDE is enabled for that database. On disabling TDE and then performing a backup, the database backup was copied on to the new SQL Server instance and then restored. The restore failed with this error even though TDE was disabled before performing the database backup on the source server - “Cannot find server certificate with thumbprint '0xA8BE3232B9572D9A57158A7BFE0D69C4A72D589E”. Is there a way by which we can still perform the restore successfully on the new SQL Server instance?
Solution
In order to simulate this error, we can perform a demo by referring to this excellent article authored by Brian Kelly on www.mssqltips.com. In Brian’s article, there is a demo for restoring the database backup of a TDE enabled database from one server to a different server. In our tip, we will perform a couple of demos to complete the process of restoring the database on the destination server. In the first demo, we will just disable TDE and in the next demo, we will also delete the database encryption key associated with the database. The source SQL Server instance is a SQL Server 2016 Developer Edition whereas the destination SQL Server instance is a SQL Server 2017 Developer edition on a different windows server.
Create a TDE Enabled database
For both our demos, we could refer to the scripts that were used in Brian’s article.
Use this script for creating a TDE enabled database on your source server, in our case, it is a SQL Server 2016 Developer Edition. Make sure, you provide correct file paths.
The following script creates the test database and enables TDE.
Once the above scripts are run, a database [RecoveryWithTDE] gets created and has TDE enabled.
You can confirm TDE is enabled by running this query on the source SQL server.
This query will provide the result as shown. Here it shows that the database [RecoveryWithTDE] is enabled.
Demo 1: Restore the database on destination after disabling TDE on the source database
In this demo, we will perform the following steps to simulate the error.
- Disable TDE for the source database on SQL Server 2016
- Perform a backup of the source database and copy backup to SQL Server 2017
- Restore this database on the destination SQL Server 2017 to generate the error
Disable TDE on the source database
In this step, we will disable TDE on the source database. In order to disable TDE, run this command on the database.
Ensure the process has completed by reviewing the SQL Server logs. From the logs, you can confirm that the process has completed.
Ssrs Generate A New Encryption Key In Computer
From the SQL Server logs, we can confirm that the process to disable TDE on the database has completed from this message – “Database encryption scan for database ‘RecoveryWithTDE’ is complete”.
Perform backup of the source database and copy to destination
After confirming that the TDE is disabled, you can start the source database backup. For performing the backup, use this script, update the folder path as required.
Once the backup completes, copy the backup across to the other SQL Server. In our case, it is a SQL Server 2017 server.
Restore Database on Destination
In order to restore the database, use this restore command on the destination SQL instance. Update the folder locations appropriately.
This restore command will fail with the following error – “Cannot find server certificate with thumbprint '0xAF9A21DC478129443FE50F32FAF77DAE7B07EAA3”
You just noticed that the restore of the database is not possible on a different server even after disabling TDE. This is because the database encryption key that you used before is still existing on the database and SQL server is looking for the certificate that is used for encrypting the database encryption key. In order to complete a successful restore of the database, you need to copy the certificate and the private key that you backed up earlier on the source server to the destination server. However, you need to make sure that a master key is already created on the destination server. If a database master key doesn’t exist on the destination server, you will get this error message when you try to create the certificate.
You can query the sys.symmetric_keys catalog view to confirm if a database master key exists on the SQL Server instance. Output of this catalog view is shown. This is from the source server.
Steps to make restore successful
When I ran the same command on the destination server, it returned only one output and there was no row for database master key.
Run this command to create the database master key on the destination server if it doesn’t exist already.
Once the database master key is created, you can run the command to create the certificates and the private keys on the destination server. But first, make sure that you copied the certificates and the private key from the source server to the appropriate folders on the destination server. This command will create the certificate and the private key on the destination server. In this demo, the certificate and private key was copied to ‘C:SQLBackups’ folder on the destination server.
After this, try to restore the database again. The restore will complete successfully.
With this method, you were able to successfully restore the database from the source to destination. You can check the status of TDE on the restored database on the destination server by using this command.
Encryption_state of 1 is “Unencrypted”. You can refer to this link for more details on this view. You can get details on the encryption status from the encryption_state value in this view.
Demo 2: Steps to restore the database after disabling TDE and deleting the database encryption key
First, we will drop the databases on the source and destination servers to start all over again if they were created from demo 1. The master key and certificate already exist on the source server, so we don't need to recreate.
Run this on source server.
Run on destination server.
In the previous demo, you noticed that you had to restore the certificates and private key associated with the source database even after disabling TDE on the source database in order to perform a successful restore of the database on the destination server. In this demo, we will perform the following steps and complete the restore of the database on the destination.
- Disable TDE for the source database on SQL Server 2016
- Drop the source database encryption key on the source database on SQL Server 2016
- Perform a backup of the source database and copy backup to the destination SQL Server 2017
- Restore this database on the destination SQL Server 2017
Disable TDE on the source database
In this step, we will disable TDE on the database. In order to disable TDE, run this command on the database.
Drop Database Encryption Key on source
In order to delete the database encryption key, run this script on the source database.
This will delete the database encryption key that was still present on the source database.
Perform backup of the source database and copy to destination
Once done, perform a backup of the source database again using the command that was used previously.
Copy the backup file over to the destination server.
Restore Database on Destination
Ssrs Machine Key
On the destination server, perform the restore of the database using the command used previously.
Ssrs Generate A New Encryption Key Iphone
You will see that the restore command completed successfully.
This restore was just like a normal restore from one server to another as you had already disabled TDE on the database and deleted the associated database encryption key.
Ssrs Restore Encryption Key Hangs
Next Steps
- In this tip, in the first demo, you saw in detail the steps to follow in order to complete the restore of a SQL database on a different server after disabling TDE.
- In the second demo in the tip, you saw how to complete the restore of a SQL database on a different server after disabling TDE and deleting the database encryption keys.
- Try this tip on your own test environments.
- Just reuse the scripts from this tip on your own test servers.
- As shown in this tip, you could try this tip on a source SQL Server 2016 server and perform the restore on a destination SQL Server 2017 server.
- For more information regarding TDE, you may refer the numerous tips available at this link.
Last Updated: 2018-08-17
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.
View all my tips
View all my tips