title: Create a Stored Procedure for AES Encryption in MS SQL Server 2008
body: <p>I have an SQL Server 2008 table with a structure similar to the following:</p>
  
  <pre><code>ID int PRIMARY KEY IDENTITY(1,1)
  Name nvarchar(100)
  LongText ntext
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and for decryption, I have created the following SP:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <p><strong>For insertion:</strong></p>
  
  <pre><code>create proc sp_InsertData
  @Name nvarchar(100),
  @LongText ntext = NULL
  as
  INSERT INTO TABLE tbl VALUES (@Name, @LongText)
  </code></pre>
  
  <p><strong>For fetching</strong></p>
  
  <pre><code>create proc sp_FindDataById
  @Id int
  as
  SELECT ID, Name, LongText from tbl where ID=@Id
  </code></pre>
  
  <p>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 <code>ntext</code> datatype. Or maybe I might be going on the wrong path here. Any kind of help is appreciated.</p>
  
  <p>PS: Due to some reasons specified by the DBAs, I can't change the data type of <code>LongText</code> from <code>ntext</code> to <code>nvarchar</code> or <code>varchar</code>. Hence, all the casting is applied in the procedures.</p>