Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Recently, in Massachusetts a law was passed (rather silently) that data containing personally identifiable information, must be encrypted. PII is defined by the state, as containing the residents first and last name, in combination with either,


B. drivers license or ID card #

C. Debit or CC #

Due to the nature of the software we make, all of our clients use SQL as the backend. Typically servers will be running SQl2005 Standard or above, sometimes SQL 2008. Almost all client machines use SQL2005 Express. We use replication between client and server. Unfortunately, to get TDE you need to have SQL Enterprise on each machine, which is absolutely not an option. I'm looking for recommendations of products that will encrypt a DB. Right now, I'm not interested in whole disk encryption at all.

share|improve this question
up vote 0 down vote accepted

This is also a PCI requirement of when working with Credit Card info. You're probably looking at encrypting the actual data in the tables themselves. When I went through this I had to create new columns in the table(s), generate the encrypted data of their respective source columns, then removing the source (unencrypted) columns. The problem is then your app(s) have to change to be able to work with the encrypted data.

You will also want to turn on Force Protocol Encryption on the server.

share|improve this answer
I'm trying to avoid the certificate route. I'm really looking for something to bundle with the software, that the client will manage. We don't really want any part other than providing the means. – DanBig Apr 30 '10 at 14:48

With regard to the regulation, there are two clauses implying when to use encryption:

  • Encryption of all transmitted records and files containing personal information that will travel across public networks, and encryption of all data containing personal information to be transmitted wirelessly.
  • Encryption of all personal information stored on laptops or other portable devices.

Thereby, unless the database is on a laptop or portable device, it is not required that you encrypt the database itself (although there are other pieces of the regulation which apply to the data in that state). Furthermore, encryption the data in that state would not cover you for the intended purpose; when the data is in transit. I would recommend that you look into transport level security (ex:HTTPS) for adherence to that.

However, if the nature of your question is truly about getting the data encrypted at the database level, I would certainly recommend using a product like TrueCrypt, which will let you encrypt a virtual disk represented as a file in the standard file system. From a practicality standpoint, however, I would recommend that you consider field level encryption. That is, encrypting/decrypting the fields at the application/service level and storing the encrypted values in the data fields.

share|improve this answer

TDE only encrypts data at rest. That's an important point. So if someone breaks into the SQL Server while it is up and running, data is unencrypted. So Squillman's approach is the only way to ensure the data is actually encrypted unless you know what keys to decrypt with. If this is sufficient (data at rest), Encrypting File System (EFS) provided by the OS is an option. This can be set on individual files, or at the folder level (better to do it at the folder level). One difference is that EFS will not encrypt the backups. Backups of TDE-enabled databases are encrypted. You would need to use a third party product like SQL LiteSpeed or Red Gate SQL Backup to ensure the backups are secured.

And with respect to Force Protocol Encryption, I wouldn't go this route. I'd rather use an IPSEC policy at the OS level using Kerberos to generate the secure tunnel. This removes the need for an SSL certificate to be installed for SQL Server to use.

share|improve this answer
Data at rest is fine, the main concern is a laptop being lost/stolen, or the HDD yanked out. – DanBig Apr 30 '10 at 19:35
Then EFS is an option. Just make sure the local Administrator (or no local account is a recovery agent). There are drawbacks to that... meaning if the computer ever has to be removed/rejoined to the domain, you've lost those DBs. BTW, if you're using laptops, you don't have the option of using Enterprise Edition. Why rule out full disk encryption? – K. Brian Kelley Apr 30 '10 at 20:56

If TDE is not an acceptable then your only option is BitLocker volume level encryption. To work with SQL Server in a performant manner the encryption has to be able to encrypt/decrypt a page in a file, which means it has to be able to set the encryption key in the corresponding state for accessing the offset of the page in the file. This rules out all file-level encryption solutions, because they cannot decrypt/encrypt the block X of the file w/o first decrypting/encrypting the block X-1 (to set the key in proper state).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.