Infrastructure at your Service

Nathan Courtine

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 = '[email protected]$$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  

Certificate

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

tde

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:

Restore_tde_witout_certificate_impossible

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:
Certificate_Copied

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

 

5 Comments

  • Jiten says:

    Hello Nathan,

    Thanks for the explanation. I have doubt while you try to restore database during second try. Do you mean that you are able to restore the database without certificates? You have used “With Replace” option, can you please elaborate on it.

    Regards,
    Jiten Pansara

  • Nathan Courtine says:

    Hello Jiten,
    Thank you for your interest.
    In the second try, the master holding the certificate is backed up and restored on an other instance (by using WITH REPLACE option).
    In this manner, the second instance obtains the certificate. So any restore of encrypted database “dummy” is now possible.
    Thereby, the second try shows that storing the certificate in the master offers the possibility to bypass TDE, if someone steals also the master database.
    Using Extensible Key Management is more secure, as the key used for decryption is stored outside the instance.
    Regards,
    Nathan

  • Jiten Pansara says:

    Thanks a lot for the Explanation.

    I need one more help. As TDE is licensed, i am working on how many Enterprise license we need to purchase if we want to opt this technology.

    Our environment is like below:
    Windows 2016
    Intel 2.10GHz(2 Processors).
    16 cores
    32 logical processors
    2 sockets

    Can you confirm how many licenses we should purchase? I have gone through the guideline of Microsoft, but they have mentioned “Core licenses are sold in packs of two, so customers must divide the number of licenses required by two to determine the actual number of line items (licensing SKUs) to order” which is not clear to me. My humble request if you can resolve my query.

    Regards,
    Jiten Pansara

  • Nathan Courtine says:

    Jiten,
    Cores to license are the visible cores by SQL Server. To retrieve this information, use the following query:
    SELECT cpu_count FROM sys.dm_os_sys_info
    Assuming you are speaking of an On-Premise Virtual Machine and 32 is displayed as cpu_count, you must purchased 16 “2-packs” of core licenses. SQL Server core licenses are sold through pack of 2 licenses.
    As a reminder, 4 cores can be licensed on a VM as a minimum.
    Regards,
    Nathan

  • Jiten Pansara says:

    Thanks a lot.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nathan Courtine
Nathan Courtine

Senior Consultant