Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Encrypt the whole database Expand / Collapse
Author
Message
Posted Tuesday, April 20, 2010 5:12 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 23, 2013 9:32 AM
Points: 90, Visits: 418
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
Post #906716
Posted Tuesday, April 20, 2010 10:02 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: 2 days ago @ 7:41 AM
Points: 6,621, Visits: 1,852
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #907010
Posted Tuesday, April 20, 2010 11:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #907086
Posted Tuesday, April 20, 2010 11:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
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
Post #907108
Posted Tuesday, April 20, 2010 10:52 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 23, 2013 9:32 AM
Points: 90, Visits: 418

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
Post #907431
Posted Wednesday, April 21, 2010 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 9:13 AM
Points: 1, Visits: 46
Not sure this is possible without a 3rd Party Tool?

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

Post #907585
Posted Wednesday, April 21, 2010 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #907809
Posted Wednesday, April 21, 2010 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 12,755, Visits: 31,121
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #907885
Posted Wednesday, February 01, 2012 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 11:15 AM
Points: 1, 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.
Post #1245239
Posted Wednesday, February 01, 2012 11:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:02 PM
Points: 32,834, Visits: 14,974
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
Post #1245244
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse