SQL Server Encryption
- Andrea Harvey
- Lindsey Byers (Deactivated)
Summary
When deploying to a new database (or an existing empty database without any tables), DryvIQ 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 and 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 DryvIQ database cannot be decrypted without access to the original SMK. Original SMK means the SMK that was present (or created) during DryvIQ 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. Therefore, 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.
By default, DryvIQ 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.