SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encrypt the whole database


Encrypt the whole database

Author
Message
SQL Dev-938873
SQL Dev-938873
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 425
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24324 Visits: 1917
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
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23456 Visits: 5314
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
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13368 Visits: 4588
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 - by Jeff Moden
SQL Dev-938873
SQL Dev-938873
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 425
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

GaNeSH
Colin.Kish
Colin.Kish
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 58
Not sure this is possible without a 3rd Party Tool?

I found this one http://www.netlib.com/sql-server-encryption.asp
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23456 Visits: 5314
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
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70082 Visits: 40923
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
petert 63278
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 0
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
Steve Jones
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: Administrators
Points: 143622 Visits: 19424
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search