Data protection is critical for ensuring that your organization is compliant with regulatory compliance standards like the GDPR and for meeting the expectations of your clients and business partners. Not only can data breaches result in large fines, but the reputational damage can be just as great. To help, Microsoft SQL Server supports 5 different kinds of encryption for protecting data. This article explains each of them and where they should be used.
SSL Transport Encryption
Like websites that secure traffic between browser and server, SQL Server can be configured to use Secure Sockets Layer (SSL) to encrypt traffic as it travels between the server instance and client application. Additionally, the client can validate the server’s identity using the server’s certificate. SSL only protects data as it travels across the network, but, unlike most of the other forms of SQL Server encryption, SSL is available in all supported versions of SQL Server and in all editions.
Before enabling SSL, you’ll need to install a certificate on the SQL Server. The best way to do this is by requesting a certificate from your own enterprise certification authority (CA). Windows Server can be configured as a CA and you can set up clients so that they trust the certificates that it issues. Alternatively, it is possible to use self-signed certificates, although this is best suited to test environments.
SQL Server Transparent Data Encryption (TDE)
Transparent Data Encryption (TDE) in SQL Server protects data at rest by encrypting database data and log files on disk. It works transparently to client existing applications, so they don’t need to be changed when TDE is enabled. TDE uses real-time encryption at the page level. Pages are encrypted before they are written to disk, without increasing the size of your data and log files, and pages are decrypted when read into memory. TDE is available only in Enterprise editions of SQL Server. It also works for Azure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse.
TDE encryption has a hierarchical structure, with Windows Data Protection API (DPAPI) sitting on top of the hierarchy and being used to encrypt the service master key (SMK). You can use the SMK to encrypt credentials, linked server passwords and the database master keys (DMKs) residing in different databases. A SQL DMK is a symmetric key that protects the private keys of certificates and asymmetric keys stored in databases.
SQL Server can generate self-signed certificates for use with TDE or you can request a certificate from a CA (which is the more common approach). If you decide to enable TDE, you must back up the certificate and the private key associated with the certificate. You will need to restore or attach the database on a different SQL Server. If you enable TDE on any other SQL Server database, the tempdb system database is also encrypted. If you disable TDE, you should keep the certificate and private key because parts of the transaction log could remain encrypted until you perform a full backup.
TDE also requires a database encryption key (DEK), which is either a symmetric key that is protected using a certificate stored in the master database, or an asymmetric key that is protected by a service that uses Extensible Key Management (EKM), such as Microsoft Azure Key Vault. Backup files of TDE-enabled databases are encrypted using the DEK, so during restore operations, the certificate protecting the DEK must be available.
Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (public key) and a different password to decrypt data (private key). You can use the CREATE CERTIFICATE command to create certificates, and the CREATE SYMMETRIC KEY and CREATE ASYMMETRIC KEY Transact-SQL commands to create database encryption keys.
Backup Encryption
Backup Encryption works like TDE but encrypts SQL backups instead of the active data and log files. Backup Encryption is available in SQL Server 2014 and later. You can specify AES 128, AES 192, AES 256 or Triple DES encryption, and use either a certificate or asymmetric key stored in EKM. Additionally, it is possible to enable TDE and Backup Encryption simultaneously, although you should use different certificates or keys.
Just as with TDE, if you enable Backup Encryption, you must also back up the certificate or key. Without the key or certificate, the backup file can’t be used to restore data. Backups can also be encrypted when using SQL Server Managed Backup to Microsoft Azure.
It’s worth noting that if you are using a certificate to encrypt backups, you must have the original certificate when restoring data. That means the certificate must have the same thumbprint as when the backup was created. Renewing certificates or changing them in any way can cause the thumbprint to change.
Column/Cell-Level Encryption
Available in all editions of SQL Server, cell-level encryption can be enabled on columns that contain sensitive data. The data is encrypted on disk and remains encrypted in memory until the DECRYPTBYKEY function is used to decrypt it. Therefore, although the SQL data is encrypted, it is not secure beyond simply using a function in the user context to decrypt it. Additionally, because a function is needed to decrypt the data, client applications must be modified to work with cell-level encryption.
Encryption Key Management
As with TDE, you need to create a master key (DMK) before using cell-level encryption. There are four options for encrypting information using cell-level encryption:
- You can use a passphrase to encrypt and decrypt the data, but you must encrypt stored procedures and functions; otherwise, the passphrase can be accessed in the metadata.
- Asymmetric keys provide strong security but can have an impact on performance.
- Symmetric keys are usually strong enough and provide a good balance between security and performance.
- Certificates also provide a good balance between security and performance, and they can be associated with a database user.
Always Encrypted
Always Encrypted encrypts sensitive data in client applications without revealing the encryption keys to the database engine, providing separation between data owners and data managers. For example, with Always Encrypted enabled, you can be sure that your database administrators won’t be able to read sensitive data. As the name suggests, data is encrypted at rest and if used in a third-party system, such as Azure.
Always Encrypted can be configured for individual database columns. Two kinds of keys are used: column encryption keys and column master keys. Column encryption keys protect data in a column and column master keys are ‘key-protecting keys’ that encrypt one or more column encryption keys. Column master keys are stored in external trusted key stores, like Azure Key Vault.
The encryption process is transparent to client applications but requires a special driver on client computers. Always Encrypted is available in SQL Server 2016 and later, but only in Enterprise editions. Because of the extra client-side requirements, Always Encrypted is best suited to situations in which separation of data owners and managers is a primary requirement.
