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).