SQL Server Cell Level Encryption

views:

answers:

+2 Q:

SQL Server Cell Level Encryption

I want to implement cell level encryption and only users with my ODBC will be able to connect to database and use it.

What do you suggest about it?

Is it possible? If yes, any resource, source code, samples, documentations on it?

If it's not possible, please tell me why.

Thank you! Kind Regards

+1 A:

SQL Server can only control encrypting a column - you can read about how to do it here.

A "cell" is Excel/spreadsheet terminology; encrypting a column value for a specific row would have to be encrypted prior to being INSERTed or UPDATEd. If this is what you are after, you need to look at using these functions when adding/updating the data:

OMG Ponies 2010-09-25 19:30:09

That's exactly what I want! But I'll use my own method to encrypt data, after encryption, how users will be able to receive decrypted data for SELECT * from test table? or even update?

Martin Craig 2010-09-25 19:36:24

I want to let users to run their own queries (without modification on their code), just they should change connection string (ODBC) and pass another extra encryption key, so ODBC could encrypt/decrypt data. But how I can parse and decrypt/encrypt all things in query and in resultset?

Martin Craig 2010-09-25 19:37:28

Or even searching and indexing? Encrypted data will be placed in DB, when user runs select * from test where testcol = 'testvalue'; testvalue in DB has been encrypted, so how DB will do a search query?

Martin Craig 2010-09-25 19:38:27

@Martin Craig: I updated the answer; I believe the functions listed are the functionality you need. IMO, it'll be a pain for your users to have to remember to use the functions, indexes will be useless, and you'll need to decrypt the data to search it.

OMG Ponies 2010-09-25 19:44:29

Any better way? I want a method which will not change code of current application my users have

Martin Craig 2010-09-25 20:10:51

OMG Ponies 2010-09-25 20:13:15

@Martin Craig: I'd recommend the white paper "Implementing Row- and Cell-Level Security In Classified Databases Using SQL Server 2005". It uses the EncryptByKey and DecryptByKey functions mentioned in this answer: http://technet.microsoft.com/en-us/library/cc966395.aspx#XSLTsection132121120120

8kb 2010-09-25 22:46:36

ansaurus

tags:

views:

answers:

SQL Server Cell Level Encryption

related questions