3

I've done this in the past, and now I need to do it again. activate TDE.

I remember that I didn't need to restore the master key on the secondary server to restore the database.

And now, as I have bad memory, I'm doing dozens of tests again with sintax, backup and restore and etc to be sure we will not mess with anything after enabling TDE in the database.

after creating the MASTER KEY, then CERTIFICATE, then the DATABASE ENCRYPTION KEY , I just backup the CERTIFICATE as below:

USE MASTER
GO
BACKUP CERTIFICATE CERTIFICATE_I_HATE_TDE
    TO FILE ='C:\tde_backups\CERTIFICATE_I_HATE_TDE.CER'
    WITH PRIVATE KEY ( FILE ='C:\tde_backups\CERTIFICATE_I_HATE_TDE_PRIVATE_KEY.PVK',
    ENCRYPTION BY PASSWORD='123superHardPassword')

then, I enable the encryption with ALTER DATABASE SET ENCRYPTION ON.

After this I backup the database, and to restore in the other server, I create a certificate FROM FILE pointing the old certificate (that was CTRL+C / CTR+V to the new server).

After this I'm able to restore the database, without restoring the MASTER KEY or the SERVICE MASTER KEY.

I'm asking this because I'm reading this redgate link and it says to backup the master key, but where would I use it?

and another question. whats the difference between creating the master key and certificate in the MASTER database, and creating the master key and certificate in the database itself ( the one i'm going to use TDE)?

Edit: Deleted by mistake.

Improve this question

3 Answers 3

Reset to default
4

The Master Key is used to protect all your certificates' private and asymmetric keys of each TDE database. It's pertinent it exists on your server for best security and prevention of someone gaining access to the keys of one of your TDE databases, and by Microsoft's design the Master Key lives in both the TDE database and master database.

The certificate also gets created in the master database so that it can be used by the user databases that you want to apply TDE to. The database encryption key gets created in the user database you're applying TDE to and uses that global certificate from the master database.

For more information, these Microsoft Docs - SQL Server and Database Encryption Keys (Database Engine) clarify the purposes of the Master Key and where it's stored, specifically the Database master key section. Additionally the documentation on Transparent Data Encryption provides good details and shows an example of creating the Master Key in the master database but the certificate in the user database they're applying TDE to.

Improve this answer
3
  • Thanks J.D. But following microsoft steps, it creates the master key and certificate, both on master, and then the database encryption key in the database that I want to enable the TDE. it doesnt says to create the certificate in the user database. USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; go CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go and then it enters the user database to create the database encryption key.
    – Racer SQL
    Commented Jan 28, 2021 at 13:24
  • @RacerSQL Sorry you're correct, I misspoke. I've updated my answer to fix my mistake.
    – J.D.
    Commented Jan 28, 2021 at 14:11
  • 1
    wow thanks, you almost gave me a heart attack !
    – Racer SQL
    Commented Jan 28, 2021 at 16:36
3

There is a rare occurrence where you really need to restore the SMK.

  • Somehow it got corrupted.

  • You are rebuilding your SQL server and planning to restore every database including system databases from backup. Usually in this case also you might not need to restore the SMK if you are using the same SQL service account and password.

Improve this answer
6
  • Another occurrence is setting up an Availability Group (AG) and not wanting the headache of dealing with mismatched keys after a failover. The easiest way, imo, is to restore the same SMK to all replicas in the AG.
    – John Eisbrener
    Commented Jan 28, 2021 at 13:57
  • @JohnEisbrener I have AG, and one of the user databases is TDE-encrypted. I did not restore SMK to all replicas to make it same, and after failover everything is fine, SMK does not matter. It is the SERVER cert has to be the same on all replicas
    – Aleksey Vitsko
    Commented Jan 28, 2021 at 14:00
  • The easiest is to create a credential in every secondary with the stored procedure sp_control_dbmasterkey_password like the AG wizard do
    – MBuschi
    Commented Jan 28, 2021 at 14:00
  • @AlekseyVitsko If any of your user databases contain encrypted fields that depend on the SMK (e.g. SSIS catalog or any user databases with column-level encryption), you would run into issues after a failover. This isn’t the only solution to that challenge, but I find it the easiest in regards to setup and long-term support.
    – John Eisbrener
    Commented Jan 28, 2021 at 14:06
  • In a rebuilding scenario when I install the SQL server should I restore the original server's SMK first before the system dbs or the other way round?
    – variable
    Commented Jul 8, 2022 at 5:33
2

You are right that TDE-encrypted database can be restored to another server, without restoring SMK and MASTER KEY (in the master database) to the other server

Creating SERVER certificate on another server (in master), from a cert backup file, is enough to be able to restore TDE-encrypted database' backup to another server

Backing up SMK and MASTER KEY is just a recommendation, you will need it only if you want to rebuild the server exactly as it was, which is not required for TDE

and another question. whats the difference between creating the master key and certificate in the MASTER database, and creating the master key and certificate in the database itself ( the one i'm going to use TDE)?

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-encryption-key-transact-sql?view=sql-server-ver15

according to this link, for creating the DEK you would need SERVER certificate (the one in master), not a certificate in a user database

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.