How to simulate transparent data encryption TDE with SQL 2000 and 2005
Encryption of data at rest is becoming more and more crucial in today's world. This tip is aimed to help businesses that do not have the budgets or resources to purchase hardware encryption tools or to upgrade to SQL 2008. It basically illustrates how to achieve transparent file encryption with SQL 2005 or SQL 2000.
TDE (transparent data encryption) is only available with SQL Server 2008 and later, so in this tip we go over a way you can achieve this with other versions of SQL Server.
SQL Server 2005 introduced a column-level encryption; this kind of encryption is a manual process that requires a re-architecture of the application in order to integrate encryption and decryption functionalities. The traditional limitations of encryption are inherent in this method as none of the automatic query optimization techniques can be used.
SQL Server 2000 does not offer any kind of encryption at all.
The only inexpensive alternative solution is to use Windows native Encryption File System (EFS). EFS is a file encryption feature introduced in Windows 2000. Like encryption in SQL Server, EFS relies on the Windows Cryptographic API (CAPI).
Here I show you how this can be done to encrypt the files at the Windows level.
- First you need to take the database offline or detach the database to change the attributes on the file
- Right click on the database data file, select properties and click on "Advanced" in the "General" tab. You will then get a screen with advanced options as shown below. Select "Encrypt contents to secure data". You should select both file and parent folder.
- Do the same thing for your log file and all other files related to this database.
- Once the files have been encrypted, the filenames will turn green as shown below
- Bring the database back online or attach the database
- Now you are ready to use the database with encrypted files
One thing that is important to clarify is that, if SQL Server is busy (OLTP Application) EFS encryption is not a good idea because EFS decreases performance in some features of Microsoft SQL Server such has read-ahead and checkpoint operations.
When EFS encryption is enabled, SQL Server I/O operations are synchronous therefore the worker thread has to wait until the current I/O operation on the EFS encrypted database file is complete.
Additionally, the SQL Server scheduler will be stalled until the current worker thread continues. Therefore, the SQL Server worker threads that remain on the SQL Server scheduler will be pending until the first worker thread continues the I/O operation causing performance degradation.
The only way to workaround the above stall situation is to use the I/O affinity mask option.
The affinity I/O mask option binds the SQL Server disk I/O to a specified subset of CPUs. In high-end SQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL Server threads issuing I/Os, moreover on EFS encrypted database files I/O operational requests can be assigned to a separate SQL Server scheduler. Although the I/O operations are still synchronous on EFS encrypted files, the SQL Server worker thread will continue without waiting for the current I/O operation to complete on the EFS encrypted database file
Things to note
- This encryption approach is good for small installations, where SQL is not busy and where there is not the budget to buy external encryption devices or to upgrade to SQL 2008.
- I have used it in a restaurant to encrypt the back end SQL data. POS (Point of Sale) applications are good examples to use such encryption technology because, they usually are not that busy (transactions/sec is low), they store confidential data, and in most cases, the business owner cannot afford SQL 2008 license cost or third party encryption hardware.
- I would not recommend it to be used it in a busy enterprise environment (for example e-commerce) with high level of concurrency because EFS will force SQL to handle I/O synchronously that will lead to performance degradation.
- If you backup the database to an unencrypted folder the backup is unencrypted. If you backup to an encrypted folder the backup is encrypted
- If you are using Windows Server 2008 / Vista and above, there is the option of using Bitlocker. Bitlocker tends to have lower latency see ( http://msdn.microsoft.com/en-us/library/cc278098.aspx) .
- Read more about EFS
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips