DevForce Community Forum : Encrypt data and store in SQL server http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : Encrypt data and store in SQL server Wed, 10 Jun 2026 15:46:22 -700 Fri, 27 Mar 2009 12:48:16 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=1143 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Encrypt data and store in SQL server : SQL 2005 is a hard requirement.... http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4203#4203 Author: alejandro
Subject: 1143
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...
]]> Fri, 27 Mar 2009 12:48:16 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4203#4203 Encrypt data and store in SQL server : Alejandro, which version of SQL... http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4176#4176 Author: kimj
Subject: 1143
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/
 
]]> Wed, 25 Mar 2009 09:44:16 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4176#4176 Encrypt data and store in SQL server : What about creating a property... http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4173#4173 Author: alejandro
Subject: 1143
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?]]> Wed, 25 Mar 2009 05:56:29 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4173#4173 Encrypt data and store in SQL server : Stored procs are probablyyour... http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4125#4125 Author: kimj
Subject: 1143
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.
]]> Thu, 19 Mar 2009 15:35:13 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4125#4125 Encrypt data and store in SQL server : Would basing my entire model on... http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4111#4111 Author: alejandro
Subject: 1143
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?]]> Thu, 19 Mar 2009 08:15:30 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4111#4111 Encrypt data and store in SQL server : As a requirement from a client... http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4091#4091 Author: alejandro
Subject: 1143
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.



]]> Mon, 16 Mar 2009 16:25:05 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1143&PID=4091#4091