Sql-server – Should I backup Master key / Service master key if I’m using TDE

sql servertransparent-data-encryption

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.

Best Answer

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.

Related Question