title: CLR Assembly for Encryption/Decryption
body: <p>We recently implemented some symmetric keys in MS SQL 2005 for encrypting and decrypting credit card, check routing/account numbers.</p>
  
  <p>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.</p>
  
  <p>Initially I thought we could create a CLR Assembly (in C#) with functions to do this, however, as I learned the same rules apply.</p>
  
  <p>I was wondering what others have been doing to easily perform this functionality. Creating many stored procedures such as:</p>
  
  <p>OPEN SYMMETRIC KEY...<br>
  SELECT username, decryptByKey(...) AS password
  CLOSE SYMMETRIC KEY...</p>
  
  <p>... for the many areas in which we will encrypt/decrypt would be very tedious.</p>