Create a Stored Procedure for AES Encryption in MS SQL Server 2008

Question

I have an SQL Server 2008 table with a structure similar to the following:

ID int PRIMARY KEY IDENTITY(1,1)
Name nvarchar(100)
LongText ntext

What I am trying to achieve is simple. Before inserting data inside this table, I want to encrypt the LongText using AES_192 algorithm. I am using the following SP to encrypt data:

create proc sp_Encrypt_LongText
@rawText ntext = null,
@encryptedText nvarchar(max) output
as
begin
OPEN SYMMETRIC KEY Encryption_Symmetric_Key
DECRYPTION BY CERTIFICATE Encryption_Certificate WITH PASSWORD = 'mypassword'
set @encryptedText = ENCRYPTBYKEY(KEY_GUID(N'Encryption_Symmetric_Key'), cast(@rawText as nvarchar(max)))
CLOSE SYMMETRIC KEY Encryption_Symmetric_Key    
end

and for decryption, I have created the following SP:

alter proc sp_Decrypt_LongText
@encryptedText ntext = null,
@decryptedText varchar(max) output
as
begin
    OPEN SYMMETRIC KEY Encryption_Symmetric_Key
    DECRYPTION BY CERTIFICATE Encryption_Certificate WITH PASSWORD = 'mypassword'
    set @decryptedText = cast(DECRYPTBYKEY(cast(@encryptedText as nvarchar(max))) as varchar(max))
    CLOSE SYMMETRIC KEY Encryption_Symmetric_Key
end

The procedures seem to work fine when I use the exec command. So far, so good. The problem is that the data is inserted and fetched inside the table using stored procedures; one each for insert and select. What I have as of now is as follows:

For insertion:

create proc sp_InsertData
@Name nvarchar(100),
@LongText ntext = NULL
as
INSERT INTO TABLE tbl VALUES (@Name, @LongText)

For fetching

create proc sp_FindDataById
@Id int
as
SELECT ID, Name, LongText from tbl where ID=@Id

My question is, how do I plug the encryption/decryption procedures inside these SPs to make them work?. I have looked into several articles for achieving this, but I keep running into one issue or another; mostly because of the ntext datatype. Or maybe I might be going on the wrong path here. Any kind of help is appreciated.

PS: Due to some reasons specified by the DBAs, I can't change the data type of LongText from ntext to nvarchar or varchar. Hence, all the casting is applied in the procedures.

Answer 1

Okay, so I managed to convince the DBAs to have the data transferred to a new column with varbinary(max) data type. Then I transferred the values into this new column after encrypting them, and then dropped the older column and renamed the new one to the old one's name. Took some work, but everything is running smoothly now. I managed to create a stored procedure and two functions to further modularize the scripts.

For opening the symmetric key

CREATE PROCEDURE sp_OpenEncryptionKeys  
AS  
BEGIN  
    SET NOCOUNT ON;  
  
    BEGIN TRY  
        OPEN SYMMETRIC KEY Encryption_Symmetric_Key  
        DECRYPTION BY CERTIFICATE Encryption_Certificate 
    END TRY  
    BEGIN CATCH  
        --catch
    END CATCH  
END

For encrypting:

CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max);  
    SET @Result = EncryptByKey(Key_GUID('My_Encryption_Symmetric_Key'), @ValueToEncrypt);  
    -- Return the result of the function  
    RETURN @Result  
END

For decrypting:

CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max);  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max);  
    SET @Result = DecryptByKey(@ValueToDecrypt);  
    -- Return the result of the function  
    RETURN @Result  
END

For inserting

exec sp_OpenEncryptionKeys  
INSERT INTO tbl VALUES ('Name', Encrypt('some text here'));

For fetching

exec sp_OpenEncryptionKeys  
SELECT ID, Decrypt(LongText) from tbl;

Hope this helps someone.