Dec 22, 2008

CLR Stored Procedure to decrypt the encrypted value

Here is a CLR Stored Proc to decrypt the encrypted value and return it through output variable.

After launching Visual Studio 2005 choose File -> New Project.
In the dialog box under Project Type choose Visual C# -> Database and then choose SQL Server Project on the right side.
I named my project CLR_Decrypt . This creates a solution and a project both named CLR_Decrypt . Visual Studio will also ask you to create a database reference or use an existing one.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Security.Cryptography;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
public static void CLR_Decrypt(SqlString employeeID, out SqlBinary decryptedSignatureBytes)
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
string signatureString = string.Empty;
string signatureString1 = string.Empty;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"SELECT EncryptedSignatureImage
EncryptedEmployeeID = '" + employeeID.ToString() + "'";

SqlDataReader rdr = cmd.ExecuteReader();
// This to call the Decrypt method to Decrypt the already encrypted text.
using (rdr)
while( rdr.Read() )
signatureString = rdr.GetString(0).ToString();
signatureString1 = DecryptText(signatureString);

// To convert string to SQL binary
decryptedSignatureBytes = Convert.FromBase64String(signatureString1);

No comments: