Daily use of encryption

In practice, I find encryption kind of messy to use -- opening, closing keys, use of certificates, all the while trying not to give away the password in cleartext. If our .NET programmers are to use it efficently in high-level code we need a function call, say "EncryptValue(<input value>)", that simply returns the encrypted value. Is it possible to write such a function? (And of course, we need the mate "DecryptValue(<encrypted value>)".

TIA,

Barkingdog

For decryption, you can use the DecryptByKeyAutoCert or the DecryptByKeyAutoAsymKey functions to automatically decrypt data. These auto functions will automatically open and close the key for you so you don't have to explicitly call open key.

Encrypt does not have a similar "auto" function, but only really have to call open symmetric key once. You don't have to close it after every encrypt call. Just open it at the beginning of the session and keep it open.

Sung

|||

BTW, you can also avoid the use of passwords by using the encryption hierarchy that SQL Server provides. If you create a database master key, you can use that to encrypt your certificate and then use that certificate to encrypt your symmetric key. If you do it this way you should not ever have to pass in a password. You must, of course, trust your dbo in this scenario :)

Alternatively, you can avoid using certificates by directly encrypting the symmetric key by password. In this case, it simplifies the code a little bit since you no longer have to deal with certificates, but unfortunately the autodecrypt functions described in my previous post will not work and you are still left with dealing with the open/close calls and having to make sure the password is protected.

Sung

|||

What is best practice in terms of encryption - for insert/update procedures, is it best to (A) open up the key at the beginning of a procedure, and then close it afterwards?

Or (B) is it okay to just create the key, open the key, and just leave it open for use?

Can I implement it using version (B) and have the database safeguarded by virtue of login security?

I am doing research on upgrading to SQL 2005 and getting rid of our old encryption.

Robert

|||

More information on how our application is setup - it's pretty basic - it's a classic ASP site that simply makes SQL Server 2000 procedure calls - so if we upgrade to SQL 2005, what would be the best way to implement the SQL Server encryption?

Our current SQL 2000 Server has a 3rd party encryption tool that we have implemented with a decrypt and encrypt function similar to what was described in the original post.

Rob

|||

Well, the key only remains open on a per session basis. If you log in, open the key, then log out, the key becomes "closed" at logout so next time you log in, the key is no longer open.

So if you only have one connection to the database, you can keep the key open, but you will have to rely on application level security because, obviously, the connection will have whatever privileges of the user you used to create the connection.

Please let me know if you need more info,

Sung

|||

That's what I just recently figured out - I will talk to my IT department to determine how the web application creates its database session - I also know that we have a webfarm - so each webserver that makes a connection to the database will need to open the key - so we need to figure out a way to make sure the key is open when the database makes the calls to the database

So basically, this solution can be solved in one of two ways - either the web app makes sure the key is open before the procedure is called, or we change all the stored procedures to open the key before encrypting -

Which one do you suppose is better practice? Do you have any other suggestions?

What I have determined is this - I could potentially do a small DB check to see if the key is open before calling a procedure (Small Webcode change, high DB overhead), or I can change the stored procedures to open the key before use and then close it after completion (large DB code change, less overhead)

What do you think?

Robert

|||

[edit] re-wrote the below :)

It's hard to say without knowing more details of your application but I would generally recommend you do this external to the procedure. It's better for performance, as you reduce the number of open key calls, and it's more secure as you leave key access outside of the procedure.

There are circumstances where you might want to put the open key call within the procedure itself, but again this varies from system to system.

Sung