Encryption with SQL Server

When deploying to a new database (or an existing empty database without any tables), DataHub takes advantage of SQL Server Transparent Data Encryption (TDE) to encrypt sensitive data.

Deployment

The following objects are created to set up TDE:

Database Master Key (DMK) - Used to encrypt all certificates

• The DMK is encrypted by the Service Master Key (SMK)
  • If the SQL Server does not already have an SMK, one is automatically created when the DMK is created
  • The SMK is encrypted using the Windows Data Protection API (DPAPI) against the SQL Server Service Account's password

Certificates - Used to encrypt Symmetric Keys

• We currently deploy 7 Certificates, one for each Symmetric Key

Symmetric Keys - Used to encrypt sensitive data

• We currently deploy 7 Symmetric Keys
• Each key encrypts a different piece of data
• Each Symmetric Key uses its own Certificate
• All Symmetric Key and Certificate pairs are created with matching names

Service Master Key Management - Self-Managed SQL Server Installations

• SQL Server can only have one SMK, which is stored in the master database.
• By default, data encrypted by TDE in the DataHub database cannot be decrypted without access to the original SMK
• Original SMK means the SMK that was present (or created) during DataHub installation

Since the SMK is encrypted by the SQL Service Account's password, the following considerations apply:

Backups

• It is recommended to back up the SMK, and store with database backups
• New backups need to be taken after any changes to the SQL Server Service Account

Changing the SQL Service Account, or following a change to the existing Service Account's password

• These both require reconfiguring the service account using SQL Server Configuration Manager
• Do NOT reconfigure the SQL Server Service Account via Windows Services
• Once changes are made in Configuration Manager, the SMK will be re-encrypted
  • This change cascades down and causes all dependent objects (including data) to be re-encrypted, and may take some time

Fail-Over and High-Availability Clusters

• Different SQL Servers will have different SMKs by default
• SMKs can be synchronized between servers which use the same Service Account
• Alternatively, DMKs can be altered on each SQL Server to use that server's SMK
  • When adding a database to an Availability Group using the wizard in SQL Server Management Studio, this step will be performed automatically. It will prompt you for the DMK password, which must already exist.

Backup Service Master Key

Restore Service Master Key

Service Master Key Concerns for Hosted SQL Server

When using a hosted SQL Server environment (Azure SQL, AWS RDS, etc), managing the SMK is not possible

• It is not guaranteed that the database will always be on the same host; it can be moved at the hosting provider's discretion
• Due to this, access to the SMK cannot be taken for granted, and additional steps must be taken to ensure that the DMK can be accessed without it

Database Master Key Management

By default, DataHub creates a DMK which is only able to be decrypted using the SMK. 

Following installation, it is highly recommended to add a password to the DMK so that it can be decrypted without the SMK.

• Ensure that the password is stored in a safe location
• If the SMK becomes inaccessible, this password will become the only way to decrypt data.

Add Password to DMK

If the SMK changes, or the database needs to be restored on a server with a different SMK, run the following command to allow the new SMK to decrypt the DMK.

Add Password to DMK