Transparent Data Encryption – Certificate in master database: is it really a good practice?

As encryption solution in SQL Server, Transparent Data Encryption (TDE) is simple and quick to set up. That’s why this is a common encryption mechanism.

TDE encrypts data with a certificate at the page level, before SQL Server writes on the disk. It is supposed to protect your environment from some scenarios, where SQL Server files (backups or data) are stolen.
By default the certificate used for encryption is stored in the master database. But is it really a good practice?

Let’s see with a common scenario:
– One MSSQL instance where TDE is enabled for one database
– One MSSQL instance without any certificate

Configuring TDE for a database

Create a master key:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd'
GO

Create a certificate to use for TDE:

USE master
GO
CREATE CERTIFICATE CertinMaster
   WITH SUBJECT = 'Self-Signed Certificate in Master',   
   EXPIRY_DATE = '20241231';  
GO  

Create a database encryption key in the database you want to encrypt, protected by the certificate:

USE dummy
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertinMaster
GO

Enable encryption on the database:

USE master
GO
ALTER DATABASE dummy SET ENCRYPTION ON
GO

At this step:
– TDE is configured for dummy database
– Certificate used for encryption is located in the master database

Restoring encrypted database to an another instance – first try

After backing up dummy database and copying the backup to another MSSQL instance, an error is displayed when trying to restore this database with required certificate:

This example shows how TDE protects data from a scenario where someone has robbed your backup file.
A similar error appears if an attach file procedure is used.

Restoring encrypted database to an another instance – second try

After backing up master database, let’s try to restore it on the other MSSQL environment.
Restoring a master database is a bit more complicated than a user database, but not impossible.

First it is mandatory to set the instance in Single User mode, by adding option ‘-m’ at startup parameters (for example).
PowerShell code executed on target server:

$Path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.ARCTURUS\MSSQLServer\Parameters\";
New-ItemProperty -Path $Path -PropertyType String -Name SQLArg3 -Value "-m"

Note: depending on how many startup parameters you have (default is 3), adjust accordingly the item property to SQLArgX.

A restart of the instance is necessary to apply this option. Do not start the agent service, otherwise it will connect to the instance in single user mode.

As only one connection to the instance is authorized in this mode, PowerShell is more suited to do the job for the restore:

$Query= "RESTORE DATABASE master FROM DISK = 'C:\Mountpoints\Backup\master.bak' WITH REPLACE";
Invoke-Sqlcmd -ServerInstance KERRIGAN\ARCTURUS -Query $Query"

The SQL Server is automatically stopped.

First remove the Single user mode:

$Path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.ARCTURUS\MSSQLServer\Parameters\";
Remove-ItemProperty -Path $Path -Name SQLArg3"

After disabling Single User mode, SQL Server services (Engine + Agent) can start again.

After querying the new master, it is effectively holding the certificate:

Finally after trying to restore the dummy database on this instance, it works without any trouble: data are freely accessible.

Conclusion

For a complete protection, I do not recommend to use a simple form of Transparent Data Encryption. As a best practice, I do recommend to store the encryption key on a third-party system using Extensible Key Management (EKM).