SQL Server CLR Integration to acheive Encryption/Decryption
- by Aakash
I have a requirement to store the data in encrypted form in database tables. I want to do it at database level but the problems I am facing:
( a) Data Type of the field should be Varbinary.
( b) Encryption is not supported by Workgroup edition
( c) Is it possible to encrypt Numeric Fields?
I want to access the encrypted data in tables to fetch in views and stored procedure for some processing but due to above problems I am not able to.
Here is my Environment:
Development Platform - ASP.Net,.Net Framework 3.5,Visual studio 2008
Server Operating System - Windows Server 2008
Database - SQL Server 2008 Work group edition
I was also thinking to adopt a different approach to resolve this issue (yet to test it's feasibility). I was just wondering if I could create a CLR function (which could take parameters to encrypt and decrypt data using Cryptography types provided in .Net framework) and use the CLR integration feature of SQL Server and call that function from stored procedure and views. I am not sure if I am thinking in right direction? Any advice on this as well please.
