New Posts New Posts RSS Feed: Encrypt data and store in SQL server
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Encrypt data and store in SQL server
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
alejandro View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: Netherlands Antilles
Posts: 10 		Post Options Post Options   Quote alejandro Quote  Post ReplyReply Direct Link To This Post Topic: Encrypt data and store in SQL server
    Posted: 16-Mar-2009 at 4:25pm
As a requirement from a client we have that all data should be stored encrypted in the database. Is there a best way to do this?
Since DevForce's PersistanceManager is generating the sql statements I am wondering what would be the most efficient way to do this.
Back to Top
alejandro View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: Netherlands Antilles
Posts: 10 		Post Options Post Options   Quote alejandro Quote  Post ReplyReply Direct Link To This Post Posted: 19-Mar-2009 at 8:15am
Would basing my entire model on stored procs (where I get/decrypting and insert/encrypting) be my only workaround?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 19-Mar-2009 at 3:35pm
Stored procs are probably your only workaround.  You'd need stored procs, and a custom IAdapterProvider, for all inserts/updates/deletes to encrypted data; and either, or both, stored procs and views to retrieve encrypted entities.  You'll also have the issue that the StoredProcRdbQuery, and PassthruRdbQuery if you use that, are DataSourceOnly queries, so you'd need to provide separate logic for DataSource vs. cache queries.
 
We haven't yet looked at encryption in SQL Server 2008, so that might offer some easier ways of handling this issue.
Back to Top
alejandro View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: Netherlands Antilles
Posts: 10 		Post Options Post Options   Quote alejandro Quote  Post ReplyReply Direct Link To This Post Posted: 25-Mar-2009 at 5:56am
What about creating a property per required encrypted field and encrypting in the setter and decrypting in the getter? I would need to build my searchers, but would be able to use cache queries. Right?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 25-Mar-2009 at 9:44am
Alejandro, which version of SQL Server are we talking about? 
 
SQL Server 2008 offers "transparent data encryption" and requires no changes to an application.  Here's a really interesting article: http://msdn.microsoft.com/en-us/library/cc278098.aspx
 
As for using encryption in SQL Server 2005, I'm not sure that you can do the encrypt/decrypt yourself in the property, at least I haven't seen any examples of this.  I believe the cryptographic functions - EncryptByKey, DecryptByKey, etc - are needed in the SQL statements upon data access, and the key must be opened first.  You also have the issue of where clauses and how they will work.  It might be worthwhile asking Microsoft directly whether this is possible, since we have no experience with SQL encryption here.  Their SQL forum may be a good resource:  http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/threads/
 
Back to Top
alejandro View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: Netherlands Antilles
Posts: 10 		Post Options Post Options   Quote alejandro Quote  Post ReplyReply Direct Link To This Post Posted: 27-Mar-2009 at 12:48pm
SQL 2005 is a hard requirement. Must comply with cell level encryption and not DB level.
We tested with getters and setters using the .net crypto functions and varchar datatypes. It works fine. Some queries are not possible: like, between, etc...
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down