Hardware-assisted crypto?

I've been experimenting with the new column-level encryption features in SQL2005. While it's been very handy, I'm somewhat concerned about its performance impact -- particularly when there are many columns involved. Is there any known method for SQL2005 to use a hardware crypto accelerator (i.e. IBM 4758 or equivalent)?You can probably write an application that uses a hardware crypto card with SQL Server 2005, but there is no builtin support for any such card.

Thanks
Laurentiu

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

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 support 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

Hi,

1. AFter encryption the values you will pass to SQL Server will just be strings. SQL Server will not care about the content of the strings. As you are using client side encryption in your solution, the encrypted value will be handled by SQL Server as you would have entered a "real" value.

2.I don′t know if you can use the server side introduced decryption methods to decrypt the client side encrypted data, that should be worth a try. But if you expose your decryption functionality in an assembly, you can create a CLR function using this logic to decrypt the data.

3.If the data was not encrypted by SQL Server and only your client application, the same rules as above in 1. would apply. If you encrypted the data in SQL Server and want to decrypt it in your client application you would have to check the thoughts of 2.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de