January 10, 2022 at 2:37 pm
So the question is....
Say you have an unknown sql server in which you need to restore all the user databases on. You don't know much about the server but you go ahead and delete the databases to clear the path to restore from backup.
Is there a quick and easy way to see which databases will need certificates exported/transferred?
Will these need to be exported and then reimported if you restore the database on the same machine? Even if you delete the databases first?
Essentially, I am trying to avoid a situation where we try to restore a database and don't realize that there is something like TDE or column level encryption involved etc.
Thanks!
January 10, 2022 at 4:34 pm
Certificates are backed up along with the database, so when you restore, all certificates are restored along with the database. You will likely need to re-map the master key to the service master key (if that is how it is set up on live) as the master key gets overwritten as well when you do the restore.
My opinion though - all encryption requirements for a restore should be both backed up (back up your keys to disk) and should be documented! At a MINIMUM, you should have a documented process for doing restores in the event of disaster recovery (DR). This process should tell you how to restore your databases and get things up and running again with encryption if required. If your DR plan doesn't have this information, I would look into it.
Now, as for which databases need certificates and have them, if you select from sys.certificates, it will tell you which ones are currently using certificates. You will need to be in the context of that particular database as sys.certificates is a per-database table. That is, if you run it on master, you will get different results than model or tempdb or any user database.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 10, 2022 at 10:06 pm
Run this for all DBs ?
exec sp_MSforeachdb 'use [?] select ''?'' as Database, * from sys.certificates'
January 11, 2022 at 8:20 pm
Certificates used for TDE and Certificates used to encrypt a database backup will be in the master database. They are not included in the user database backup.
Certificates that have been created in the database (for use by EncryptByCert(), etc.) are backed up and restored with the database.
If the backups are encrypted and you don't know what encrypted them, then try restoring them anyway. The error message will include the Thumbprint value of the Certificate it needs. You can then query master.sys.certificates WHERE thumbprint = <thumbprint_hex_value_from_error> in the source of the backups to see what cert encrypted the backup.
You can restore a TDE database without its Certificate or Asymmetric Key. When you attempt to RESTORE ... WITH RECOVERY, the database will go into RECOVERY_PENDING state, and the SQL Error Log will display the Thumbprint of the Certificate or Asymmetric Key it cannot find. Repeat the above query on master.sys.certificates to find that Certificate (if it's a Certificate... are you using Asymmetric Keys with an EKM system?). Once you load the correct Certificate into the master database, just issue an ALTER DATABASE <your_db_name> SET ONLINE; and the instance will attempt to recover the database again. If you grabbed the wrong Certificate, then DB will simply throw the same errors (there's no risk to repeated attempts to bring the database online).
You can restore a database with column encryption, but I think you are on your own as far as looking for Certs. Those certs would normally be in the database, which would include them in the database backup and restore with no addition effort.
If you are going to set up AGs or log shipping, then ensure the same set of Certificates (and/or Asymmetric Keys) have been loaded on all AG replica partners/log-shipping targets before you start any of that stuff up.
If you are using Always On Encryption with Secure Enclaves, then what's above doesn't apply; it uses Certificates in the Windows Certificate Store, so the resolution is quite different.
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply