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»»

Encrypting the entire database. Expand / Collapse
Author
Message
Posted Tuesday, March 30, 2010 1:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 1,507, Visits: 2,536
Dear Experts,

One of our clients has the following requirement.

Data in database should be displayed only through front end application.I mean, none should be able to see the proper data even if they can login into the database
and open the tables. (Data should be in encrypted format..).

How can I achieve it (in SQL 2005/2008).? Please guide.

Thanks in advcance.

Post #892494
Posted Tuesday, March 30, 2010 3:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 1,507, Visits: 2,536
Will 'transparent data encryption' in SQL-20008 help i this case..?

Please guide

Thanks.
Post #892574
Posted Tuesday, March 30, 2010 7:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, Visits: 601
The only solution that I am aware of is transperant data encryption in MSSQL2008. This solution performs real-time i/o encryption and decryption of data and log files. The msdn link below covers most everything you need to know about TDE.

http://msdn.microsoft.com/en-us/library/bb934049.aspx

costa


MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Post #892773
Posted Tuesday, March 30, 2010 8:18 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Saturday, February 12, 2011 6:05 AM
Points: 601, Visits: 443
SQL really does not have anything that will do what you are asking but you could probably get close. As mentioned there is TDE but that is just meant to prevent someone from walking off with the physical database files or backups and using them on another server. The entire database is encrypted on the disk, however any login with rights to that db can login and view the decrypted data using any method they choose. Another option is cell level encryption. This will encrypt the data in a particular column and is useful for encrypting certain data within the database. Here the data in those columns will not be decrypted until they are actually used. The problem with cell level encryption though is that it does require code and schema changes to implement.


This is a nice technical paper that describes what is available in SQL 2008 and how each option can be used alone or together.
http://msdn.microsoft.com/en-us/library/cc278098.aspx
Post #892818
Posted Tuesday, March 30, 2010 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
dang; i followed the fine article cos_ta393 posted; at the very end, i found out only enterprise or developer supports TDE; my 2008 Standard doesn't support that feature.

That's something to keep in mind...Enterprise version is required


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 #892835
Posted Thursday, April 1, 2010 5:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 1,507, Visits: 2,536
Is there anything like that in Oracle.? Anyone aware.?
Just curious....comparing both..

When I told some of my friends that now I have become an SQL Server DBA (certified as well), they suggest me to be an ORACLE DBA...I don't understand why..!!!


Post #894613
Posted Wednesday, April 28, 2010 1:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 11:20 AM
Points: 1, Visits: 7
Yes, Oracle has TDE via thier Advanced Security Option.
It is implemented similiar to SQL Server, but it also includes encrypting data across the network.

FYI... I don't know why but our Oracle DBA's are paid more than our SQL Server DBA's.


Post #911674
Posted Thursday, April 29, 2010 4:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:03 PM
Points: 62, Visits: 379
Many of the prior replies noted Transparent Data Encryption as an option. It was also noted that TDE encrypts the physical data files and not the data itself. The intent of this feature is to prevent someone from stealing your backup files or .mdf/.ldf files and restoring them on another server.

Your other option is cell-level encryption, which can be accomplished by individually encrypting each column; but your data types for your columns will need to be varbinary. Also note that for each read you will have to decrypt this data which adds overhead to your database.

I would recommend asking more about this requirement that you client has put upon you. It may be a misinterpretation of a regulation or a fear-based requirement. It may also be simply a miscommunication of what they actually want... avoiding the "I got what I asked for, not what I wanted" scenario.
Post #912663
Posted Thursday, April 29, 2010 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,133, Visits: 6,295
I agree with thoughts that this could be a misinterpretation of the situation. If an entire database needs encrypting, my thought is that NO ONE should have access to it. All logins should be DENIED.

Or, is this a situation of Production being restored down to Dev and trying to protect the data from developers? If so, a data-scrubbing tool would be more appropriate than encryption. Or scripting out the DB & Tables, then creating faux-data for development / testing.

In my POV (and perhaps mine alone), Encryption should ideally only occur on HIPAA or PPI related data. The whole encrypt-decrypt routine is too processor heavy to implement for every record across the board.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #912725
Posted Friday, April 30, 2010 12:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 312, Visits: 1,116
I think that we can rule out TDE in your case, as it will make data visible to someone who has login permissions. Encrypting columns will work but there could be a performance hit (and I've always found it a pain to put together).

Can the application encrypt the data? there are a lot of security assemblies available...and this seems to fit your requirements.
Post #913481
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse