Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. 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'm helping a friend with setting up encryption of data on SQL Server 2008 R2 Standard edition. Upon original research I thought I could use TDE but did not realize that it was only available for Enterprise or DataCenter versions of SQL Servers. Upon further research into SQL Server 2008 R2 features I saw that it does allow for "Data encryption and key management" but I'm not sure what it means or how to implement it.

What is the most efficient and low cost method for data encryption? Should I do something through SQL Server or just use third party tools to encrypt the whole volume where DB and backups are? Also if someone can point to a way to use "Data encryption" that comes as feature in standard edition I would really appreciate. Every time I search for encryption on sql server I keep ending up on how to use TDE and in current scenario it is not feasible for the size of business to purchase Enterprise Edition.

reason for encryption - HIPAA Compliance

Solution Used:
After presenting different options to System Admin in charge of the project, decision was made to go with BitLocker drive Encryption. I stopped the SQL Instance during the encryption process and had no problem restarting it after encryption was complete.

share|improve this question
Which attack vectors are you trying to eliminate via encryption? – vonPryz Sep 23 '13 at 18:56
If access is gain to file system for database or backups, data is encrypted and can not be of any use. This could include physical access to the machine or access through local admin. Two word answer: HIPAA Compliance. – Vladimir Oselsky Sep 23 '13 at 19:22
I hope it is not off topic to point out the differences between our transparent database encryption for SQL Server, and SQL's built-in TDE: – Neil Weicher Sep 27 '13 at 17:50
up vote 3 down vote accepted

Since you are using Standard Edition, you cant use TDE. So other options are

Using encryption keys at instance/database level :

SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.

An example of how this can be done is described at Encrypt a Column of Data

Reference : SQL Server and Database Encryption Keys (Database Engine)

At Drive level :

Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.

Note: The most important bit is ALWAYS backup your encryption keys.

You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.

Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.

share|improve this answer

First, ask the question "why am I encrypting this database?"

If the answer is "to hide data from the database administrators" then SQL Server encryption of any kind is not the answer. In that case the client application should be encrypting data and storing the ciphertext in the database. That way neither the database server nor the database administrators (nor the network administrators, as well) ever see unencrypted data or encryption keys.

See Laurentiu Cristofor's blog article Who needs encryption?

(I know I'm editing an old post, but I thought these links might be helpful.),

share|improve this answer
I believe the OP stated in the comment why he needed encryption... – Shawn Melton Sep 24 '13 at 5:17
Ahh, I see the comment now, cheers Shawn. I still stand by my suggestion, however. Encrypting the data on the client before sending it to the server means that the database files and database backups are all encrypted and are protected against someone stealing a backup tape or hard disk and are also protected against rogue or compromised administrator accounts. – Greenstone Walker Sep 24 '13 at 20:53

protected by Paul White May 27 '14 at 13:55

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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