How to simulate transparent data encryption TDE with SQL 2000 and 2005

By:   |   Updated: 2009-11-19   |   Comments (9)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Encryption


Problem

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.

Solution

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
advanced attributes

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
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-11-19

Comments For This Article




Tuesday, February 17, 2015 - 2:34:50 AM - Activecrypt Back To Top (36259)

You may also consider using DbDefence - Transparent Database Encryption solution. It works for SQL Server 2005 and higher.

Thursday, December 10, 2009 - 9:55:37 AM - EntiusGJ Back To Top (4541)

Matteo,

There is a web application where data is gathered and the consultants get a subset of it. Their subset is used to give presentations to their customers. Basically it is a one way stream of data. Wireless connection is nice for the 'civilised' world, the application have to work on the African continent also.

I can not immagine I am the first with this 'problem'.

Regards,

Gerard

Wednesday, December 9, 2009 - 5:13:00 AM - dmining06 Back To Top (4531)

Gerard,

I do not want to say obvious things however; my recommendation is for your independent consultants to use a wireless internet access offered by any of the major wireless carrier like AT&T, T-Mobile, Verizon and so on.

How do you plan to keep your 100+ databases in sync?

Thanks

Matteo


Tuesday, December 8, 2009 - 5:51:04 PM - EntiusGJ Back To Top (4529)

Hi Matteo,

Unfortunately we have no control over the laptops. They are owned by independent consultants using the application. Connection to the internet is not always available while using the application.
Based on that I am stuck to the solution I am looking for.

Tips and/or links are welcome. 

Thanks, 

Gerard

 

Tuesday, December 8, 2009 - 6:19:36 AM - dmining06 Back To Top (4525)

 Gerard,

I am the author of the article. When I wrote the article I used Windows Server 2003 and SQL 2005. If you want to secure your 100+ sales computer (I guess laptops), y'd be better to encrypt the entire hard disk and set a boot password at BIOS level so, if the laptops are stollen, they will not be able to access the data.

Anyway, I agree with Brian,  you should consider unsing a centralized SQL Server and plan for a DR (Disaster Recovery) site. Using 100+ SQL experss instances is not wise, and you soon will have a maintenance nightmare.

Possible solution: You may have your user to access the app. to a centralized srver via terminal server (RDP session) and have the app communicate with your centralized SQL as long as SQL and apps are on different servers.

Hope it helps

Thanks

Matteo

 

Tuesday, December 1, 2009 - 9:56:17 AM - EntiusGJ Back To Top (4504)

Hi Brian,
thanks for the response. It is not my idea to distribute multiple copies but the application is used by consultants at customer sites. There is not a 100% guarantee that an internet connection is available. That's why, life would be very easy using a internet based database or web based application.

The maintenance headache is for later, now I have to distribute the application. As mentioned before we do not want readable user names and passwords, competition is always interested in know how. I am not able to install myself all the SQL server instances so an script has to do the trick. If it is possible to install it not from a CD but from a website it might be less vulnerable to unwanted hacking.

If you can help me with some tips and/or links I am very grateful. I found the tool FineBuild and looking into it now but seems to be complicated to create the install script.

Thanks in advance,

Gerard

 

 

Tuesday, December 1, 2009 - 8:56:27 AM - K. Brian Kelley Back To Top (4503)

I know Windows Server 2003 supports multiple folks with access. Not 100% sure about Windows XP, Vista, or 2007. I know 2000 did not. Is there a reason that you're not centralizing the data rather than installing 100+ instances of SQL Server Express? Keep in mind that it means 100+ instances that have to be patched, creating a maintenance headache.

 

Tuesday, December 1, 2009 - 4:42:19 AM - EntiusGJ Back To Top (4501)

Hi Brian,

I am wondering around on the SQL server related sites to find a solution for my challenge.
 
I have to distribute a VB.net application including an SQL 2008 Express database to 100+ computers (mix of XP and Vista). To get a high level of security I wanted to implement a file encryption as mentioned in the article. I tested it and noticed the encryption is ‘locked’ to the user who encrypted the file? An other user was not able to open the file (not under Windows or MSSMS), so I assume it does not work for me. If I overlooked something please let me know.

I am struggling also with the unattended installation of SQL Express. What I found so far it seems not very secure. In scripts are readable user names and passwords, for security reasons I do not want to distribute this info to 100+ users. Do you have knowledge about this or can you provide me with some links?

Thanks in advance,

Gerard

 

Thursday, November 19, 2009 - 9:05:17 AM - K. Brian Kelley Back To Top (4464)

Make sure the SQL Server service account is one of the ones which can access the files and folders, otherwise SQL Server won't be able to open the database files and start up.

 














get free sql tips
agree to terms