SQL Server 2008 introduced transparent data encryption (TDE) to providethe ability to encrypt entire databases, data, and log files without the requirement of application changes and with minimal performance impact. TDE protects data files at rest by encrypting the data and log files for a specific database on disk. When a transaction requires data from data or log files, the specific data pages are decrypted in memory. Once a TDE is enabled on a database, all the database’s backups are encrypted. Also, tempdb will be encrypted.
In the past, it was necessary to either use a third-party tool, or – if you were using
cell-based encryption – you would need to change the data type of the column to varbinary. This would, on occasion, require changes to foreign keys and make searching more complex. The application needed to make an additional call to encrypt and decrypt the data. Transparent data encryption will allow you to apply encryption to a database without having to change the application that accesses the database. All data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk when using transparent data encryption.
TDE Architecture
At its core, TDE uses the Windows Data Protection application programinterface (API) to encrypt and decrypt keys and data. In Microsoft Windows 2000, the Data Protection API (DPAPI) was introduced to encrypt and decrypt data. Since SQL Server 2005, DPAPI is used to generate a key for the database instance, also known as the service master key (SMK). At the time the database instance is created, the SMK is generated by using the DPAPI functions and the Windows credentials of the SQL Server service account. The SMK is then encrypted using local machine credentials. The SMK can only be decrypted by the service account used during the SQL Server setup processing at installation or by an account that has access to the SQL Server service account’s Windows credentials or belongs to the same security group. The following figure from MSDN explains the architecture succinctly:
Fig 1. TDE architecture
The Process of Encryption
Using the DEK and encryption algorithm, TDE encrypts the database files at thedatabase page level. Before the data page is written to disk, each page is encrypted and decrypted when the page is read into memory. The page is encrypted and decrypted using the DEK.
When TDE is enabled for a database, the is_crypted column in the sys.databases view is changed to 1, indicating the database is set for encryption. The encryption scan process then starts and scans all the database files associated to the database and encrypts the database files using the specified encryption algorithm specified when creating the DEK. The encryption process takes a shared lock on the database and encrypts each page in the specified database. During this process, detaching the database or file structure changes is denied. Data existing in the log file is not encrypted. Entries in the log file are encrypted entries after the encryption process is complete.
Once the database encryption is complete, all database encryption and decryption is performed in memory. Each page is decrypted when the page is read into memory and each page is encrypted before the data page is written to disk. The page is encrypted and decrypted using the DEK.
Cell- and Column-Level Encryption
Introduced in SQL Server 2005, cell-level encryption provides developers with a granular level of encryption for their applications that have specific data security requirements. While this provides the application development much flexibility, it has additional performance and space costs.
First of all, cell- and column-level encryptions require that the column to be encrypted in the table schema be stored as a varbinary object. This requires additional processing and disk space overhead. Specifically, most data is usually a character or numeric field. There is an additional performance cost of converting the data to and from a varbinary type in addition to the processing required to encrypt and decrypt each cell in a table.
Column-level encryption is established in the same manner as cell-level encryption. The main difference between cell- and column-level encryption is that the expense of column-level encryption is magnified by the number of rows in the table.
EFS Encryption (Through the Operating System)
Windows 2000 introduced Encrypting File System (EFS), which provides file and folder-level encryption using the Windows operating system. Generally, EFS is used to protect user data on a single computer used by a single user. Both EFS and TDE protect data at rest. Data at rest refers to data on a disk. The security issues arise when files containing personal (or organizational) identifiable information are susceptible to access to non-authorized users.
Currently TDE does not support encryption of the system databases (master, model, msdb, and resource). However, in some scenarios EFS can be used to encrypt the system databases. One of the disadvantages of using EFS is that SQL Server will experience decreased performance. Since EFS does not support asynchronous input/output (I/O), I/O will be serialized and become the bottleneck. Synchronous I/O is a key database management system requirement to support high-concurrent transactions on enterprise database and applications. In most cases, EFS is not a good idea; certainly with SQL Server 2008, TDE will be a better choice.
Another issue with using EFS on database files is in the area of administration. Although key management is simpler with the use of the Windows certificate store and offers a data recovery path if keys are lost, database administration would require file administration privileges on the operating system level. Most organizations do not provide this operating system privilege to a database administrator.
Should I go for TDE?
Which form of encryption is the best for my situation? Well the answer is: it depends!
Data encryption provides a great security benefit, but it also has performance, disk, and administrative costs. Asymmetric key encryption algorithms are more compute intensive than symmetric key algorithms. In general, it is more efficient to encrypt large datasets using a symmetric key, while encrypting the symmetric key with an asymmetric key.
Although TDE encryption overhead is low, there is a small overhead cost in the operational performance and additional disk space for the encrypted database. Most of the encryption impact is at the time of encrypting the database.
TDE encryption over cell- or column-level encryption yields various benefits. First, no additional application code or application code changes are required for encryption.
The major benefit with TDE is that the database data at rest—specifically any copies of the database’s data, log, and backup files—cannot be accessed without the proper security permissions via the TDE keys in the database. Backups cannot be recovered to a different SQL Server 2008 instance without the proper encryption keys and certificates in the master database of a different SQL Server instance.
Summary
Before ending this article, here are few important points that should be kept in mind,
- Currently TDE does not support encryption of the system databases (master, model, msdb, and resource).
- Once a SQL database is encrypted via TDE, all the backups are also encrypted.
- Backups and secure storage of the SMK, certificates, and DMKs are critical. If these keys are lost, the backups are not recoverable.As part of the database backup process, store the certificate and private key backup files in a physically safe location but separate from the database backup files.
- When a database is encrypted the tempdb will also be encrypted. Also, the transaction log will be encrypted. But, filestream datatypes will not be encrypted.
- Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files. During transactional or merge replication, encryption can be enabled to protect the communication channel.
- If any of the filegroups in a database are marked read only, the TDE encryption operation will fail. All filegroups must be writeable during the encryption process
No comments:
Post a Comment