Encrypt the Data
SQL Server supports the encryption of data through a number of mechanisms. These include:
- Cryptographic functions for the encryption and signing of individual values,
- The Transparent Data Encryption (TDE) feature through which the data and log files associated with a database are encrypted, and
- Support for SSL and IPSec to encrypt data as it is transmitted between the server and clients.
In addition, SQL Server supports an internally managed cryptographic key infrastructure but may also integrate with an externally managed infrastructure through its Extensible Key Management (EKM) interface.
To get a more in-depth and complete overview of the SQL Server cryptographic capabilities, please refere to this white paper.
Cryptographic Functions
With SQL Server 2005, a collection of cryptographic functions were introduced into the database product for the encryption and signing of individual values. Prior to the 2005 release, applications could store encrypted values and signatures within the database but relied on external functions to perform the encryption and signing work. By moving the functions into the database engine, SQL Server provides greater and more consistent access to cryptographic functionality and allows an application to more easily leverage a centrally managed encryption key infrastructure.
The Encryption Functions
The encryption functions support encryption using either symmetric or asymmetric keys. Symmetric key encryption has less performance overhead while asymmetric key encryption provides stronger protection.
To perform symmetric key encryption, SQL Server provides two functions: EncryptByKey() and EncryptByPassPhrase(). The EncryptByKey() function leverages a symmetric key registered in advance with SQL Server. With the EncryptByPassPhrase() function, a temporary symmetric key is generated using a passphrase supplied with the function. The choice of which function to employ comes down to the needs of the application and the availability of key management support within the organization.
NOTE Every cryptographic function discussed in this post has a counterpart. For example, EncryptByKey() encrypts a value while DecryptByKey() decrypts that value. Instead of enumerating each function-pair, I’ll just identify the leading function.
To perform asymmetric key encryption, SQL Server provides another two functions: EncryptByAsymKey() and EncryptByCert(). The EncryptByAsmyKey() function leverages an asymmetric key registered with SQL Server. The EncryptByCert() function leverages the asymmetric key associated with a certificate registered with SQL Server. Between the two options, encryption by certificate, when the certificate is generated by a certificate authority, is considered the more secure of the two options as the certificate is verifiable through the digital signature associated with the certificate. Some of the metadata in the signature is accessible through the CertProperty() function.
DEMONSTRATION To review a demonstration of encryption using one of the cryptographic functions, check out this post.
The Digital Signing Functions
Digital signing allows an application to confirm a value has not been altered since the signature was generated. It works by using an asymmetric key and a signing algorithm in combination with the value to protect to generate a new value, the signature. To confirm a value is unaltered, the same key and algorithm are used with the value to confirm to generate a new signature. If the new signature matches the old signature, the value is unaltered. SQL Server supports digital signing through the SignByAsymKey() and SignByCert() functions.
DEMONSTRATION To review a demonstration of encryption using one of the cryptographic functions, check out this post.
Transparent Data Encryption
Transparent Data Encryption (TDE) protects data by ensuring users do not bypass SQL Server to obtain data directly from database files or through backups. This form of encryption is transparent in that applications do not have any awareness of this protection. Instead, it’s part of an internal mechanism managed by SQL Server itself.
NOTE The cryptographic functions and TDE are used to protect against different security concerns so that the use of one does not negate the need for the other. Use the cryptographic functions when you need to ensure sensitive data is not read when users have access to the database through SQL Server. Use TDE when you need to ensure folks do not bypass SQL Server to obtain access to any data within a database.
TDE works by encrypting the individual pages within the data and log files within a database. These are encrypted using a symmetric key stored within the database which itself is protected using an asymmetric key (or the asymmetric key associated with a certificate) registered at the instance-level. When the TDE encrypted database starts up, the asymmetric key unlocks the symmetric key, allowing the database to become available. The symmetric key is then used to decrypt and encrypt pages as they are read and written, respectively.
The protection provided by TDE is extended to anywhere data pages are transferred. As a result, backup files and log shipped or mirrored copies of the database inherit TDE protection and must have access to a copy of the asymmetric key in order to be useable.
NOTE This is critically important and so needs to be explicitly stated: You will not be able to recover a backup of a TDE-protected database unless you have first recovered the asymmetric key or certificate on which it depends. If you use TDE, keep the asymmetric key or certificate backups separate from the database backups as TDE-protection is of no use if a malicious user is able to obtain this object. At the same time, be sure personnel with responsibility for restores understand how to access and employ the asymmetric key or certificate.
In addition, when a database within an instance is enabled for TDE, the TempDB database will employ TDE to ensure any data associated with the database is not inadvertently exposed through it. This same protection DOES NOT extend to the distribution database, replication snapshot files, or any other database to which you replicate data using SQL Server replication. If those databases require protection, TDE must be explicitly enabled for them. (To protect the snapshot files, have them deposited in a secure location on the file system.)
Finally, when TDE is first enabled, SQL Server must read, encrypt, and then write each page in the newly encrypted database. This occurs as part of a background process and can take quite a bit of time to complete. Until completed, the database is not fully protected. When enabling (or disabling) TDE, monitor the state of the database through the encryption_value field in the sys.dm_database_encryption_keys data management view.
NOTE Before enabling TDE, please be sure to read the complete documentation on this feature. A good starting point for this is found here.
SSL & IPSec
Outside the authentication process, data transmitted between SQL Server and its clients is transmitted in clear text. To protect this data, encryption of the packet can be configured using either SSL encryption or IPSec.
IPSec encryption is configured at the operating system and protects all communications between a server and its clients. SQL Server receives the benefits of IPSec with no additional configuration. However, IPSec is dependent upon client, server, and network configuration and therefore is most appropriate for securing transmissions within a LAN.
For communications that extend beyond the LAN or for LANs within which IPSec is not available, SQL Server can be configured to encrypt its data packets using SSL encryption. This is done through the network configuration properties accessible through the SQL Server Configuration Manager. For detailed instructions on configuring SSL encryption with SQL Server, please review the instructions provided here.
When configuring SSL, there are two options available for the source of the encrypting certificate. If an appropriate external certificate has been registered with the server computer’s certificate store, this can be employed to encrypt the data. If no certificate has been registered, SQL Server will generate its own certificate, referred to as a self-signed certificate. Self-signed certificates are considered far weaker than certificates signed through an external certificate authority. It is recommended that certificates obtained through an external authority be used whenever possible.
Encryption Key Management
The cryptographic functions and TDE are dependent upon keys and certificates registered with SQL Server. SQL Server provides the ability to generate, secure, backup, and restore these objects but doing so across a large enterprise can be burdensome and problematic. Furthermore, regulatory compliance may require these objects to be generated and protected in very precise ways.
SQL Server therefore provides an API, referred to as Extensible Key Management (EKM), through which third-party vendors can register their products with SQL Server to offload key management responsibilities. From Books Online, EKM provides the following benefits:
- Additional authorization check (enabling separation of duties).
- Higher performance for hardware-based encryption/decryption.
- External encryption key generation.
- External encryption key storage (physical separation of data and keys).
- Encryption key retrieval.
- External encryption key retention (enables encryption key rotation).
- Easier encryption key recovery.
- Manageable encryption key distribution.
- Secure encryption key disposal.
The full document is available here.
If encryption is to be used extensively or regulations necessitate, consider EKM and work with third-party vendors to implement it appropriately.