Filesystem security for TDE Keys and Certificates
What is TDE, and why is filesystem security important?
Transparent Database Encryption, or TDE, is used to encrypt an entire database. The encryption is “transparent” since once the database is loaded by SQL Server, anyone who has permission to access the database can see all the data in its unencrypted state. This is useful to protect the data “at rest”, since the database files themselves, and all backups of the database, are encrypted. Unless you have the key used to encrypt the database, you cannot successfully load a copy of the database onto another SQL Server instance and access the data. This implies that you need to backup the keys and certificates used to encrypt the database to enable disaster recovery. It’s not enough to just backup a TDE-encrypted database, you must backup the certificates and keys. This post shows how SQL Server uses filesystem security to protect those key and certificate backups.
Poor Dr. Gachet lost his TDE keys due to poor filesystem security.
How to backup Keys and Certificates
The typical process for backing up the keys and certificates for a TDE-encrypted database consists of the following commands. Note, the passwords used are just an example, you should replace them with values known only to you (and record them in a trusted password management system, such as LastPass, 1Password, or KeePass, or for the truly paranoid, Trezor Password Manager):
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BACKUP SERVICE MASTER KEY TO FILE = N'D:\SQLServer\Backups\Service_Master_Key.key' ENCRYPTION BY PASSWORD = N'#GobsNicuTestaSweden7'; BACKUP MASTER KEY TO FILE = N'D:\SQLServer\Backups\Master_Key.key' ENCRYPTION BY PASSWORD = N'#BlotchOwenLonganOrly8'; BACKUP CERTIFICATE TDETestCert TO FILE = N'D:\SQLServer\Backups\TDETestCert.cert' WITH PRIVATE KEY ( FILE = N'D:\SQLServer\Backups\TDETestCert.key' , ENCRYPTION BY PASSWORD = N'=BebopAcetalCareReims1' ); |
The commands above are designed to backup:
- The Service Master Key, which is the root of the SQL Server encryption hierarchy. This key is generated automatically whenever SQL Server needs to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password. If you lose the password to the service account, and do not have a backup of the service master key, the other keys that are protected by the service master key will be unavailable. Using Active Directory Computers and Users to reset the Service Account password changes the local machine key, rendering the SQL Server Service Master Key unusable.
-
The database master key, which is a symmetric key used to protect the private keys of certificates and asymmetric keys stored in the database. The database master key is protected by encrypting it with the service master key.
-
The TDE certificate used to actually encrypt the database. The TDE certificate is stored inside the TDE-encrypted database, and is itself encrypted using the database master key.
Filesystem Security
After those commands have been used to export each key or certificate, SQL Server modifies the Access Control List (ACL) on each file1. This ensure no-one other than the following Windows principals have access to the file:
- OWNER_RIGHTS has Full Control. OWNER_RIGHTS is a well-known security identifier,
S-1-3-4that represents the current owner of the object. When an Access Control Entry that carries this SID is applied to an object, the system ignores the implicit READ_CONTROL and WRITE_DAC permissions for the object owner. - Members of the local “Administrators” group have Full Control
- The Virtual Account or Managed Service Account used to run SQL Server has Full Control. Note this is not the SQL Server Service Account you configure in SQL Server Configuration Manager. For a default SQL Server instance, the account is typically
NT SERVICE\MSSQLSERVER. For a named instance, the account is namedNT SERVICE\MSSQL$INSTANCE_NAME. - Inheritance is removed from the object’s ACL to prevent access by any other principal.
The filesystem security ACLs are modified in that way to ensure the security of those keys. The security of the keys is of paramount importance to the encryption process used by TDE databases. If the keys are obtained by an unauthorized party along with a copy of a database backup, that party will enjoy unfettered access to the encrypted data.
This post is part of our series on Data Security. You may also be interested in our prior post on what happens when you try to use a password to encrypt a database.
1 – assuming the SQL Server Service has “full control” rights to the target folder in the backup statement.
The TDE certificate is stored inside the [master] database and it is not used to encrypt user database. DATABASE ENCRYPTION KEY is used to actually encrypt user database and is protected by the TDE certificate.