We recently implemented some symmetric keys in MS SQL 2005 for encrypting and decrypting credit card, check routing/account numbers.
Ideally, we would like a user defined function to be able to perform the encryption and decryption, however, its not possible since the encryptbykey and decryptbykey functions cannot be used within user defined functions.
Initially I thought we could create a CLR Assembly (in C#) with functions to do this, however, as I learned the same rules apply.
I was wondering what others have been doing to easily perform this functionality. Creating many stored procedures such as:
OPEN SYMMETRIC KEY...
SELECT username, decryptByKey(...) AS password
CLOSE SYMMETRIC KEY...
... for the many areas in which we will encrypt/decrypt would be very tedious.
