| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-05-19 : 18:41:26
|
| I have a column of data about the current user which is sensitive and I want to protect (possibly encrypt?) it.I can encrypt it in the applicationOr, much easier (in terms of programming and deployment effort), I could do something in SQL.I've read a bit about encryption in SQL 2008.Seems like with SQL 2008 encryption once logged in (with appropriate permission to use an encryption certificate) I have free access to the data in the encrypted field.Is that right, or have I got completely the wrong end of the stick?So ... my app can log in, just do an INSERT or UPDATE on that column, and the data is saved. Ditto for the App doing a SELECT.But anyone logging in directly (with insufficient Certificate permission) won't see the data fer that column, even if they have SELECT access to the tableIs that right?And if I want to transfer that column to another SQL box? (main box is on the web, second box is in the office, via a VPN) Can I just do an OPENQUERY or similar to push the data to the remote box?Someone stealing a Backup file won't see the data. But if they get the Application's password they will be able to see the data? (well, more correct EXEC SProcs that will show them the data?)Hoping I haven't got that all completely wrong! thanks for your help. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 04:28:11
|
| Anyone got any suggestions please? |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-06-17 : 07:00:17
|
| Why don't you stored calc a hashvalue in the front-end or a SQL function and then just store the hashvalue in the database? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 07:10:59
|
| Yes, could do, but we have to modify APP for that, and the QA and rollout consequences of that are that client can only have it as part of formal upgrade release (sadly not scheduled for any-time-soon).We've stopped storing CC details "Full Stop" and use 3D Secure etc. now, so the problem had gone away.However, we have a client who thinks that 3D Secure is hurting their business and is refusing to implement it (big enough to argue with the CC companies!!) and thus we need to start taking, and holding, CC details to pass to back-end system.Thus if we could just encrypt in database we have minimal change in SProcs and QA / Rollout is doable using bug-fix process, rather than much bigger application-rollout process. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-17 : 09:07:47
|
quote:
Thus if we could just encrypt in database we have minimal change in SProcs and QA / Rollout is doable using bug-fix process, rather than much bigger application-rollout process.
One thing you should consider is the application sending unencrypted CC details to the SQL Server. Even if they are on the same server, it may not satisfy certain audit or certification requirements (PCI, SAS70, etc.) |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-06-17 : 10:15:26
|
| You can try looking at ENCRYPTBYKEY in books online (for a starting point).I agree with rob about sending the data unencrypted from the app to begin with, but it seems like that is not possible at the present time.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2010-06-17 : 10:51:05
|
| Take a look at this subject in Online Books:CREATE ASYMMETRIC KEYStonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 18:10:42
|
| The PCI compliance between APP and Server is a good point which I hadn't thought of (and wish you hadn't either!)I have read CREATE ASYMMETRIC KEY in BoL, but that's only really the beginning and its not quite the same as hearing from people who've done it and have experienced / suggestions.Perhaps I should ask the question: has anyone reading this, today (not in a year from now!), actually used SQL 2008 encryption to protect data in a column? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 18:14:08
|
| Don, overlooked your post, sorry. Off to look at ENCRYPTBYKEY in BoL now ... I don't think I have seen that one as yet. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 18:34:18
|
| I don't remember reading about the EncryptByKey function in BoL, thanks for that. It has the benefit that I can do it all within the Sprocs that Save/Retrieve the data, and looks easily doable - famous last words! |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-06-18 : 10:06:20
|
| WE use ENCRYPTBYKEY and CREATE ASYMMETRIC KEY together right now in our production system to protect "sensitive" data. We have not seen any adverse performance impacts because of this (yet).http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-18 : 10:39:23
|
| Back-of-envelope calculation suggests that at peak we might get one transaction every 10 seconds, assuming we "handle" the data 10 times (which is excessive - but allows for customer to go back and do it again,a s well as a GET and a SAVE for each user-action, and the need to do a GET for the transfer to back office), so 1 a second at worst I suppose.Glad to hear you are using it ... I was worried I might be the first! |
|
|
|
|
Encrypting a Column containing Sensitive Data