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

I am dealing with electronic protected health information (ePHI or PHI) and HIPAA regulations require that only authorized users can access ePHI. Column-level encryption may be of value for some of the data, but I need the ability to do like searches on some of the PHI fields such as name.

Transparent Data Encryption (TDE) is a feature of SQL Server 2008 for encrypting database and log files. As I understand it this prevents someone who gains access to the MDF, LDF, or backup files from being able to do anything with the files because they are encrypted. TDE is only on enterprise and developer versions of SQL Server and enterprise is cost-prohibitive for my particular scenario. How can I get similar protection on SQL Server Standard? Is there a way to encrypt the database and backup files (is there a third-party tool)? Or just as good, is there a way to prevent the files from being used if the disk were attached to another machine (linux or windows)?

Administrator access to the files from the same machine is fine, but I just want to prevent any issues if the disk were removed and hooked up to another machine. What are some of the solutions for this that are out there?

share|improve this question
BitLocker and Least Privilidge ACLs is sufficient for HIPPA (at the time of writing this). You probably want more advanced controls, but cell level encryption is not required if access controls are properly configured. (Generic advice given without detailed knowledge of your environment and does not imply indemnification). On a more serious note; if you don't know SQL security, please don't be the only person configuring ePHI security, get someone in there who really knows their stuff. – Chris S Jun 23 '11 at 20:02
@Chris s, Thanks much. I had heard of BitLocker, but didn't know what it was. I now do and it is what I was looking for. – Quesi Jun 23 '11 at 23:30
up vote 6 down vote accepted

The general suggestion for HIPAA is to follow the PCI Data Security Standard (PCI-DSS), except everywhere they say "Cardholder Information" or "Account Information" you say "PHI". My company (Healthcare industry, dealing with PHI) uses the PCI-DSS as our primary starting point, along with a healthy dose of common sense (e.g. making sure the data STAYS encrypted (or confined to secure networks) at all times).

Column-level encryption of some kind is almost always a good idea when dealing with sensitive data, and given the potential cost of a lawsuit it's high up there with things to consider.

share|improve this answer
While I prefer the comment on my question to use BitLocker as the answer, he didn't post it as an anwer so I am marking yours because you have pointed me to a great document with more information on the topic. While column-level encryption would be a good idea though, it just isn't practical in all cases such as when you need to search on it. – Quesi Jun 23 '11 at 23:50

You need to protect the PHI which would require that you encrypt the data in the database table. Encrypting the data within the column level if your best bet. Searching on these fields is going to be expensive, but that's the cost of high security.

I talk about a variety of data encryption options in chapter 2 of my book "Securing SQL Server"

share|improve this answer
Is it even possible to search on encrypted fields using a "like" query? – Quesi Jun 23 '11 at 23:51
Sure, you have to decrypt the entire column, search it then return the needed rows. – mrdenny Jun 24 '11 at 16:54
decrypt the entire column. yikes! That is what you meant by "expensive". – Quesi Jun 24 '11 at 17:10
Yep, security isn't done to make life easy. Security of the data is the first consideration, easy access to the data is second. If possible when making data searchable, you can hash the data and store the hash as hashes are much easier to search against than encrypted data. Although neither will work well for using LIKE. If you don't encrypt the PHI and you are audited odds are you'll fail the audit. – mrdenny Jun 24 '11 at 17:16

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.