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


Encrypting the entire database.


Encrypting the entire database.

Author
Message
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3286 Visits: 3200
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.
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3286 Visits: 3200
Will 'transparent data encryption' in SQL-20008 help i this case..?

Please guide

Thanks.
MostInterestingMan
MostInterestingMan
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 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
jncarter
jncarter
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 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
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29172 Visits: 39985
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

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

Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3286 Visits: 3200
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..!!!


:-D
ThomThom
ThomThom
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
John Magnabosco
John Magnabosco
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 385
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.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15254 Visits: 9006
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ianT
ianT
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 1299
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.
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