mssqltips logo

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 | More > 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 Step


Last Updated: 2009-11-19


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

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 09, 2009 - 5:13:00 AM - dmining06 Back To Top

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 08, 2009 - 5:51:04 PM - EntiusGJ Back To Top

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 08, 2009 - 6:19:36 AM - dmining06 Back To Top

 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 01, 2009 - 9:56:17 AM - EntiusGJ Back To Top

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 01, 2009 - 8:56:27 AM - K. Brian Kelley Back To Top

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 01, 2009 - 4:42:19 AM - EntiusGJ Back To Top

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

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.

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools