SQL Server Column Level Encryption
46 views
Skip to first unread message
Sal
Aug 25, 2010, 10:20:40 AM8/25/10
to nhusers
OK, here goes. This topic has come up several times over the past few
years, with no real solid answer. I'm hoping maybe this time there we
will be some help.
I have the unfortunate experience of dealing with a client who insists
on using column level encryption in sql server, not just the newer
TDE. This usually means using a sql function to encrypt/decrypt the
column during queries and inserts/updates. Is there any guidance
around implementing this with Nhibernate....without sprocs???
years, with no real solid answer. I'm hoping maybe this time there we
will be some help.
I have the unfortunate experience of dealing with a client who insists
on using column level encryption in sql server, not just the newer
TDE. This usually means using a sql function to encrypt/decrypt the
column during queries and inserts/updates. Is there any guidance
around implementing this with Nhibernate....without sprocs???
John Davidson
Aug 25, 2010, 10:38:37 AM8/25/10
to nhu...@googlegroups.com
Potentially you did not get an answer because the question is too open ended.
Encrypting and decrypting values in an object can be easily handled in a number of ways, with or without NHibernate. The real issues are how are you going to query the data and how do you manage relations.
If you are encrypting the Id values as they are stored that creates one set of problems, but as this data is not business data does it need to be encrypted?
Encrypting the business data becomes a problem when you want to search for partial string values. This can only be done if all rows are held in a cache in unencrypted format. Can your system handle this kind of caching? Is it allowed by the business?
Answering these questions will begin to put bounds on the problem space and then you may get better answers.
John Davidson
--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
Sal
Aug 25, 2010, 10:47:47 AM8/25/10
to nhusers
It's not really open ended. SQL Server 2005 introduced column level
encryption. The typical way for working with encrypted columns is to
use a sql function for encryption and decryption.
Take a simple example. I have a property "Address" which is a string.
It needs to be encrypted in the DB using column level encryption. When
I retrieve it, I somehow need to use a sql function to decrypt, and
likewise encrypt when inserting it. It's horrendously ugly because it
bleeds through and affects the application. The app has to deal with
SQLs encryption....crappy!.
I 'could' propose that the encryption is done in the app. Using that
approach, it would be easier working with NH for sure.
On Aug 25, 10:38 am, John Davidson <jwdavid...@gmail.com> wrote:
> Potentially you did not get an answer because the question is too open
> ended.
>
> Encrypting and decrypting values in an object can be easily handled in a
> number of ways, with or without NHibernate. The real issues are how are you
> going to query the data and how do you manage relations.
>
> If you are encrypting the Id values as they are stored that creates one set
> of problems, but as this data is not business data does it need to be
> encrypted?
>
> Encrypting the business data becomes a problem when you want to search for
> partial string values. This can only be done if all rows are held in a cache
> in unencrypted format. Can your system handle this kind of caching? Is it
> allowed by the business?
>
> Answering these questions will begin to put bounds on the problem space and
> then you may get better answers.
>
> John Davidson
>
> > .
encryption. The typical way for working with encrypted columns is to
use a sql function for encryption and decryption.
Take a simple example. I have a property "Address" which is a string.
It needs to be encrypted in the DB using column level encryption. When
I retrieve it, I somehow need to use a sql function to decrypt, and
likewise encrypt when inserting it. It's horrendously ugly because it
bleeds through and affects the application. The app has to deal with
SQLs encryption....crappy!.
I 'could' propose that the encryption is done in the app. Using that
approach, it would be easier working with NH for sure.
On Aug 25, 10:38 am, John Davidson <jwdavid...@gmail.com> wrote:
> Potentially you did not get an answer because the question is too open
> ended.
>
> Encrypting and decrypting values in an object can be easily handled in a
> number of ways, with or without NHibernate. The real issues are how are you
> going to query the data and how do you manage relations.
>
> If you are encrypting the Id values as they are stored that creates one set
> of problems, but as this data is not business data does it need to be
> encrypted?
>
> Encrypting the business data becomes a problem when you want to search for
> partial string values. This can only be done if all rows are held in a cache
> in unencrypted format. Can your system handle this kind of caching? Is it
> allowed by the business?
>
> Answering these questions will begin to put bounds on the problem space and
> then you may get better answers.
>
> John Davidson
>
> On Wed, Aug 25, 2010 at 10:20 AM, Sal <salbass...@hotmail.com> wrote:
> > OK, here goes. This topic has come up several times over the past few
> > years, with no real solid answer. I'm hoping maybe this time there we
> > will be some help.
>
> > I have the unfortunate experience of dealing with a client who insists
> > on using column level encryption in sql server, not just the newer
> > TDE. This usually means using a sql function to encrypt/decrypt the
> > column during queries and inserts/updates. Is there any guidance
> > around implementing this with Nhibernate....without sprocs???
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > OK, here goes. This topic has come up several times over the past few
> > years, with no real solid answer. I'm hoping maybe this time there we
> > will be some help.
>
> > I have the unfortunate experience of dealing with a client who insists
> > on using column level encryption in sql server, not just the newer
> > TDE. This usually means using a sql function to encrypt/decrypt the
> > column during queries and inserts/updates. Is there any guidance
> > around implementing this with Nhibernate....without sprocs???
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > .
Jason Dentler
Aug 25, 2010, 10:54:46 AM8/25/10
to nhu...@googlegroups.com
Use an encrypted string IUserType.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
Frans Bouma
Aug 25, 2010, 11:14:28 AM8/25/10
to nhu...@googlegroups.com
Isn't that client-side (right before the save) ?
Column encryption has to take place on the server side you can use the key
pair stored inside SQLServer, so other apps can decrypt the same data
FB
> Use an encrypted string IUserType.
>
>
http://code.google.com/p/unhaddins/source/browse/trunk/uNhAddIns/uNhAddIns/U
> serTypes/EncryptedString.cs?r=365
>
>
<http://code.google.com/p/unhaddins/source/browse/trunk/uNhAddIns/uNhAddIns/
> UserTypes/EncryptedString.cs?r=365>
> <mailto:nhusers%2Bunsu...@googlegroups.com>
> <nhusers%2Bunsu...@googlegroups.com
> <mailto:nhusers%252Buns...@googlegroups.com> >
>
> > > .
> > > For more options, visit this group at
> > >http://groups.google.com/group/nhusers?hl=en.
>
> --
> You received this message because you are subscribed to the Google
> Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to
> nhusers+u...@googlegroups.com
> <mailto:nhusers%2Bunsu...@googlegroups.com> .
Sal
Aug 25, 2010, 11:23:56 AM8/25/10
to nhusers
Frans,
Yes. My point was, how do I get NH to use a sql function to perform
the encryption/decrypted when generating sql. There doesn't seem to be
an easy way, shy of using stored procs instead of NH generated SQL.
On Aug 25, 11:14 am, "Frans Bouma" <fr...@sd.nl> wrote:
> Isn't that client-side (right before the save) ?
>
> Column encryption has to take place on the server side you can use the key
> pair stored inside SQLServer, so other apps can decrypt the same data
>
> FB
>
> > Use an encrypted string IUserType.
>
> http://code.google.com/p/unhaddins/source/browse/trunk/uNhAddIns/uNhA...> serTypes/EncryptedString.cs?r=365
>
> <http://code.google.com/p/unhaddins/source/browse/trunk/uNhAddIns/uNhA...
>
> > UserTypes/EncryptedString.cs?r=365>
Yes. My point was, how do I get NH to use a sql function to perform
the encryption/decrypted when generating sql. There doesn't seem to be
an easy way, shy of using stored procs instead of NH generated SQL.
On Aug 25, 11:14 am, "Frans Bouma" <fr...@sd.nl> wrote:
> Isn't that client-side (right before the save) ?
>
> Column encryption has to take place on the server side you can use the key
> pair stored inside SQLServer, so other apps can decrypt the same data
>
> FB
>
> > Use an encrypted string IUserType.
>
>
> <http://code.google.com/p/unhaddins/source/browse/trunk/uNhAddIns/uNhA...
>
> > UserTypes/EncryptedString.cs?r=365>
Tuna Toksoz
Aug 25, 2010, 11:25:54 AM8/25/10
to nhu...@googlegroups.com
I hate to say that, but you can perhaps use a trigger on insert, and use an iusertype on update.
Tuna Toksöz
Eternal sunshine of the open source mind.
http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike
Tuna Toksöz
Eternal sunshine of the open source mind.
http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike
Sal
Aug 25, 2010, 11:28:15 AM8/25/10
to nhusers
Don't think I've entirely rules that out ;) What about selects?
On Aug 25, 11:25 am, Tuna Toksoz <tehl...@gmail.com> wrote:
> I hate to say that, but you can perhaps use a trigger on insert, and use an
> iusertype on update.
>
> Tuna Toksöz
> Eternal sunshine of the open source mind.
>
> http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comhttp://twitter.com/tehlike
> > > > > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > > > <mailto:nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>
>
> > > > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>
> > > > <mailto:nhusers%252Buns...@googlegroups.com<nhusers%25252Bun...@googlegroups.com>>
> > > > <mailto:nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>>
On Aug 25, 11:25 am, Tuna Toksoz <tehl...@gmail.com> wrote:
> I hate to say that, but you can perhaps use a trigger on insert, and use an
> iusertype on update.
>
> Tuna Toksöz
> Eternal sunshine of the open source mind.
>
> > > > <mailto:nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>
>
> > > > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>
> > > > <mailto:nhusers%252Buns...@googlegroups.com<nhusers%25252Bun...@googlegroups.com>>
>
> > > > > > .
> > > > > > For more options, visit this group at
> > > > > >http://groups.google.com/group/nhusers?hl=en.
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "nhusers" group.
> > > > To post to this group, send email to nhu...@googlegroups.com.
> > > > To unsubscribe from this group, send email to
> > > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > > > > > .
> > > > > > For more options, visit this group at
> > > > > >http://groups.google.com/group/nhusers?hl=en.
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "nhusers" group.
> > > > To post to this group, send email to nhu...@googlegroups.com.
> > > > To unsubscribe from this group, send email to
> > > > <mailto:nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>>
> > .
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > Groups
> > > > "nhusers" group.
> > > > To post to this group, send email to nhu...@googlegroups.com.
> > > > To unsubscribe from this group, send email to
> > > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > Groups
> > > > "nhusers" group.
> > > > To post to this group, send email to nhu...@googlegroups.com.
> > > > To unsubscribe from this group, send email to
> > .
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
Tuna Toksoz
Aug 25, 2010, 11:30:46 AM8/25/10
to nhu...@googlegroups.com
Ok forget my solution :D
Tuna Toksöz
Eternal sunshine of the open source mind.
http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike
Tuna Toksöz
Eternal sunshine of the open source mind.
http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
Fabio Maulo
Aug 25, 2010, 12:03:26 PM8/25/10
to nhu...@googlegroups.com
It is a pretty long task but completely possible using NH.
--
Fabio Maulo
You have to implements all IType needed (int, long, DateTime, string etc. etc.) calling it eint, elong, eDateTime, estring etc. etc.
Then you have to use your types in your mappings (pretty easy to do if you are using ConfORM or FNH).
Another pretty cool short-cut is using reflection+expression_based_delegates to hack TypeFactory cleaning the typeAliases field and then fill it using RegisterType with your implementations of IType.
That should be all.
--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
--
Fabio Maulo
Reply all
Reply to author
Forward
0 new messages