Encrypt the whole database

Question

Encrypt the whole database

SQL Dev-938873

Ten Centuries

Points: 1280
More actions
April 20, 2010 at 5:12 am

#223551

Hi Techies,
I have requirement to encrypt the whole database ( all data in all tables )
so that if anybody get the database backup, they cannot get the data at any cost until they have the decrypt key or wat ever.
Is there any possibility to have like this Security implementation.
please provide your thoughts and suggestions
Thanks & Regards
Ganesh
GaNeSH

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The only option that doesn't require a 3rd party product is SQL Server 2008 Enterprise Edition. You're looking for Transparent Data Encryption.

Third party backup products like Red Gate's SQL Backup and Quest's SQL LiteSpeed are capable of producing encrypted backups.

K. Brian Kelley
@kbriankelley

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

While I agree with Brian, I challenge the requirement.

This is a lazy requirement in my book. In my experience it comes from fear and from someone reading a whitepaper or something saying you should do this without any true understanding of WHY they should and WHAT the ramifications are.

In most databases there is usually very little data that is trully restricted or confidential. That which is should be identified, and the individual fields that are restricted should potentially be encrypted. Also there is a cost in terms of performance (in particular) for whole database encryption.

Also, you are mixing two very different requirements, the first is database encryption and the other is backup encryption. Which is it?

Also what is your strategy for tapes? Do they go offsite? Do you have a service for it? Are they bonded?

As you can see there is a lot more than encrypt vs. don't..

CEWII

John Rowan SSC Guru Points: 56440 More actions · Answer 3

In addition to what Brian and Elliot have said, here's Microsoft's opinion on backup security:

The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQL Dev-938873 Ten Centuries Points: 1280 More actions · Answer 4

Thanks for all for ur replies.

Dear Elliott W.

I want make data saving in encrypted format. then even though backup also having encrypted data. So both will come under this.

SO i want to encrypt my whole database. Even though if anybody access my database, if they open the data in any table. it wil be in encrypted mode. So they can'd find any information which they can understand.

Hope you understand now.

Thanks & Regards

GaNeSH

Colin.Kish Valued Member Points: 69 More actions · Answer 5

Not sure this is possible without a 3rd Party Tool?

I found this one http://www.netlib.com/sql-server-encryption.asp

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

SQL Dev-938873 (4/20/2010)
I want make data saving in encrypted format. then even though backup also having encrypted data. So both will come under this.
SO i want to encrypt my whole database. Even though if anybody access my database, if they open the data in any table. it wil be in encrypted mode. So they can'd find any information which they can understand.

I do understand, but I don't think you fully understand the ramifications of what you are asking for. You lose the ability to do many kinds of selects because you have to decrypt information to search on it. I have spent a LOT of time dealing with encryption in and out of the database and my experience shows that very little data needs this level of protection or the headaches associated with it. There is a performance hit that is very likely going to be massive.

The SQL 2008 feature Transparent Data Encryption is effectively encryption of the connection not the data in the database.

Whole disk encryption like PGP or bitlocker encrypt the data on the disk, but when the system is running it is presented in clear text.

I have seen this attempted a few times, in all but one case they abandoned it pretty quickly, that single case they went for a while and then abandoned it. I have yet to see anybody go to production with anything close to what you are asking about.

So instead of taking the full database encryption approach with all of its pitfalls why don't you take this approach:

1. Implement access controls to the database. IF you don't need access you don't have access.

2. Encrypt the columns that are truly sensitive.

3. Encrypt the data at the time it is written to tape or use a bonded company to handle your tapes offsite.

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 7

solid advice so far; I agree with everyone above that encryption should be selective to specific items.

think it through a little though:

Is it not true that if EVERY field is encrypted, The entire all the scherma/current table structure gets thrown out and replaced.

I think that you could no longer use ANY reporting tool to generate reports. NONE. no SSMS, crystal, excel, nothing. everything has to go thru your applications biz layer to decrypt all the fields.

if you got carried away and also encrypt the Primary keys/foreign keys, you obfuscate the data so your developers can't even find the data easily.

if you don't get so carried away, and you only change VARCHAR columns to varbinary columns so they can be encrypted, that's going to at least leave stuff in place os developerss can see the relational schema.

since everything is encrypted, all queries are going to be poorly performing table scans, since the old index on "Lastname" would be dropped because you wouldn't/couldn't index the encrypted values. most indexes get thrown away.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

petert 63278 Valued Member Points: 65 More actions · Answer 8

You can check out this company, http://www.vormetric.com.

They can encrypt the whole SQL database and allows backup to be encrypted as well. It's pretty easy to setup. Just guard a directory and anything that you put in the directory will get encrypted. They also provide access control so that the IT admin can do the backup but they can not decrypt the database.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

TDE encrypts the data at rest, on disk, and the backups. There is CPU overhead for this.

Encrypting the data, so that a DBA or unauthorized user who accesses the database, but doesn't have the decryption routine, is another story. That can seriously impact performance, and you should understand the implications before you do this.

If you just want encrypted backups, there are numerous third party tools to do this. This isn't done natively without TDE.

NOTE that whichever method you choose, you better understand how to decrypt things if you server explodes. In TDE you need to back up some keys, with third parties, you need to be sure you have copies of the passphrases.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

Man, I hate it when I mis-state things.. TDE is DB encryption, and you can encrypt the data in motion using certs.

However, I stand by my postion about whole vs. partial encryption..

Also, as a side note, I absolutely do not recommend using the EFS (Encrypted File System) manner of encryption, it is my understanding that this is effectively single threaded and it sounded like you would experience SEVERE performance degradation when using it.

CEWII

NetLibSecurity Old Hand Points: 318 More actions · Answer 11

I hope that this is not out of line, but I would like to point out some differences between SQL TDE and NetLib TDE:

http://www.netlib.com/tde-sql-encryptionizer.asp

dan.henwood Newbie Points: 9 More actions · Answer 12

What about using Bitlocker? I have my test database on a drive that is BitLocker encrypted, since I regularly do DEV on a copy of the Production database that has HIPAA data in it.

What is the downside to using this method?