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

Certificate

Symmetric Key

Encrypted Column(s)

AccountMapsLdapCredentials AccountMapsLdapCredentials AccountMaps.LdapCredentials
Connections_Details Connections_Details Connections.Details
DataProtectionKeys_KeyXml DataProtectionKeys_KeyXml DataProtectionKeys.KeyXml
GroupMaps_LdapCredentials GroupMaps_LdapCredentials GroupMaps.LdapCredentials
License_Data2 License_Data2 License.Data2
PersonalDriveConventions_LdapCredentials PersonalDriveConventions_LdapCredentials PersonalDriveConventions.LdapCredentials
SecurityApplications_ClientSecretHash SecurityApplications_ClientSecretHash SecurityApplications.ClientSecretHash

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

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'  
    ENCRYPTION BY PASSWORD = 'TempPsw1!'

Restore Service Master Key

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'  
    DECRYPTION BY PASSWORD = 'TempPsw1!'

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

USE SkySyncV4;
ALTER MASTER KEY
 ADD ENCRYPTION BY PASSWORD = 'TempPsw1!';

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

USE SkySyncV4;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TempPsw1!'
GO
ALTER MASTER KEY
 ADD ENCRYPTION BY SERVICE MASTER KEY

Related Links