Handling encryption at the application level

Developing an application, which is handling encrypting for a SQL server 2005 DB at the application level, using symmetric AES encryption. After being encrypted the data is being sent to the SQL server 2005 DB to be inserted. This is what I want to accomplish. But I have one or two questions as I am going through the research for my project.

1. Encrypting the data shouldn’t be that big a deal, but can SQL server 2005 handle to insert these data? If yes how? I am thinking just simple SQL INSERT statements?

2. When the data are inserted can SQL server, as it supports AES encryption, through the DBMS decrypt the data as they have been encrypted else where and then perform specific functions? (by specific function I just mean any function that may have to run, it could be any function one could think of)

3. How difficult would it be to take the encrypted data from SQL server to the application and then at the application level decrypt these?

Any help on the above questions is highly appreciated or if people have any comment on the subject I would be more than happy to receive these.

Thank you

Kenn Kikkenborg

1. Yes this is supported. Encryption is currently done manually. For more info, please check Laurentiu's blog:

http://blogs.msdn.com/lcris/archive/2005/06/09/427523.aspx

2. I'm not sure I understand the question. This seems like you are encrypting data externally to SQL Server? If this is the case then the two systems are not exactly compatible. It is possible to make it work, but this is somewhat tricky and involved, especially because symmetric keys cannot currently be imported or exported. If you are primarily concerned about transport level security (i.e. sending information in the clear over a connection) I suggest you explore using SSL instead:

http://msdn2.microsoft.com/en-us/library/ms189067.aspx

3. Please see #2

Please let me know if you have any further questions or would like clarification on the above.

Sung

|||

Thanks for the follow-up to my question

As to point 2 in my question. As SQL server 2005 should do its internal encryption by calling the Windows CryptoAPI. I was wondering if SQL server was configured to handle AES encryption on specific columns, and I would find a way to use the CryptoAPI, that I then could decrypt the data from SQL server AND my own application. The possibility to run batch-scripts on the DB would then be available, as the BD would be able to decrypt the data. But I haven’t found a way to do this and I REALLY need to find out if this is possible as it would be an enormous gain for both the DB and application.

If you have any other ideas or comment then I would highly appreciate these…!!!

Thank you

Kenn Kikkenborg

|||

I'm still not sure why you need to call Windows CAPI directly against the database. Why do you want your application to handle decryption?

If this is because you want to run batch operations, you should still be able to do so by using views and triggers. For example, if you wanted to automatically encrypt data going into a table, you can simply create an insert trigger. If you want to automatically decrypt data, you can create a view that decrypts the columns and select from the view instead of from the table.

If instead you are concered about sensitive information being passed over your connecion in plaintext, the best solution would be to enable SSL.

Can you perhaps provide some use cases for your scenario?

Thanks,

Sung