SQL 2014: Investigating Microsoft's latest database release
A comprehensive collection of articles, videos and more, hand-picked by our editors
SQL Server security is a vital and growing concern for many organizations due to the increasing loss and unauthorized disclosure of confidential data. Regulatory requirements often require robust SQL Server encryption for data such as credit card and Social Security numbers, and features in SQL Server 2014 can help.
SQL Server provides database administrators with several options to encrypt data when transmitted through the network, while creating a backup, or when stored on the server or network. These encryption options include: transparent data encryption, column-level encryption, encryption of SQL Server objects definitions, backup encryption, encryption of SQL Server connections, database file-level encryption through windows EFS and BitLocker drive encryption. In this article, I will guide you through the different encryption options that are available in SQL Server 2014 to encrypt confidential data in the SQL Server database.
Transparent data encryption
Transparent data encryption (TDE) was first introduced in SQL Server 2008. TDE is the primary SQL Server encryption option to prevent potential attackers from bypassing database security and reading sensitive data from the disk. It enforces real-time I/O encryption and decryption of data-at-rest in the database layer. TDE encrypts every page of your database and automatically decrypts each page as needed during access. TDE does not require additional storage space or change in the underlying database schema, application code or process. Moreover, it is totally transparent to the user or application because it's performed at the SQL Server service layer.
TDE encryption uses a Database Encryption Key (DEK), which is an asymmetric key that is encrypted using the server certificate stored in the master database. The DEK is stored in the database boot record, which is why it is available during database recovery. The server certificate is encrypted using Database Master Key (DMK), which itself is encrypted using Server Master Key (SMK). Both DMK and SMK are asymmetric keys. SMK is automatically created the first time you encrypt something on SQL Server, and is tied to the SQL Server Service account. SMK is also encrypted by the Windows Data Protection API.
TDE also encrypts database backups and snapshots, making it the best choice for complying with regulatory compliance and corporate data privacy laws.
Column-level encryption (aka cell-level encryption) allows you to encrypt and decrypt confidential data in the database. You can encrypt or decrypt data using:
- Passphrase: A passphrase is the least secure option. It requires you to use the same passphrase when encrypting and decrypting the data. If stored procedures and functions aren't encrypted, the passphrase is accessible through metadata.
- Asymmetric key: Offers strong protection by using a different key to encrypt and decrypt the data. However, it provides poor performance and shouldn't be used to encrypt large values. It can be signed by the database master key (DMK) or created using a password.
- Symmetric key: Offers good performance and is strong enough for most requirements. Uses the same key to encrypt and decrypt the data.
- Certificate: Offers strong protection and good performance. Can be associated with a user. The certificate must be signed by the database master key.
To support the encryption-decryption operation, column-level encryption requires application code and database schema changes because all encrypted data must be stored using the varbinary data type. The column-level encryption provides a more granular level of SQL Server encryption, giving you the means to encrypt a single cell within a table. In addition, the data is not decrypted until it is used, which means the data is not in plain text when the page is loaded into memory.
Encrypting connections to SQL Server
IPSec is implemented by the operating system and supports authentication using Kerberos, certificates or pre-shared keys. IPsec provides advanced protocol filtering to block traffic by protocol and port. You can configure IPsec by local security policy or through Group Policy. IPsec is compatible with Windows 2000 or later. To use this option, both the client and server operating system must support IPSec.
SSL is implemented by SQL Server. It is most commonly used to support Web clients, but it can also be used to support native SQL Server clients. SSL verifies the server when the client requests an encrypted connection. If the instance of SQL Server running on a computer that has a certificate from a Public Certificate Authority (PCA), the identity of the computer and the instance of SQL Server ensures that the certificate chain leads to the trust root authority. This server-side validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate used by the server. Encrypting with a self-signed certificate is possible, but a self-signed certificate does not offer much protection.
The main two advantages to SSL over IPSec are minimal client configuration and straightforward configuration on the server.
Other SQL Server encryption options
Encrypting SQL Server object definitions: You can create stored procedures, views, function and triggers using WITH ENCRYPTION clause, to encrypt the definition text of these objects. Once encrypted, you cannot decrypt the objects' text.
Encryption for backups: The database master key (DMK) for the master database and certificate or asymmetric key is a prerequisite for encrypting a backup. To encrypt backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key.
File-level encryption through Windows EFS: Beginning with Windows Server 2000, Microsoft introduces an Encrypting File System (EFS) feature that allows you to encrypt files on the operating system. You can use this EFS feature to encrypt the entire SQL Server data directory. Like the SQL Server native encryption option, EFS also relies on the Windows DPAPI. Unlike transparent data encryption, it does not encrypt database backups automatically. Before SQL Server 2008, EFS was the only option to encrypt database files stored on NTFS disks. This is because SQL Server I/O operations are synchronous when EFS is enabled. The worker thread has to wait until the current I/O operation in the EFS-encrypted database file has completed.
BitLocker drive encryption: BitLocker Drive Encryption is a full-disk encryption feature available in Ultimate and Enterprise editions of Windows Vista and Windows 7, Pro and Enterprise editions of Windows 8 desktop operating systems, Windows Server 2008, Windows Server 2008 R2 and Windows Server 2012. It also encrypts data-at-rest using an AES encryption algorithm. BitLocker does not have the same performance issues that are associated with EFS.
About the author:
Basit Farooq is a lead database administrator, trainer and technical author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms.