SQL Encrypt and decryption library (using rijndael)

• Download source files - 55 Kb

Introduction

I needed a way to be able to encrypt and decrypt text in SQL server and wanted an easy way to do this. I did not know C++ to build an extended procedure so using some code I was already using in other projects, I found a way to use a .NET library as a COM object that SQL could call into and it would handle the encryption and decryption. The Encryption algorythm is Rijndael, I used this because it is most commonly available on most platforms, as well as it is the new goverenment standard AES encryption method.

The following are the instuctions on how to build, install and run the sql scripts which are included in the ZIP code.

Installation Instructions:

1) build the library

To use this COM Object with your SQL server 2000 or 2005 compile this project with Visual Studio 2005, and the dll will be in the bin\release folder.

2) Create a strong named assembly

This project already has a strong named key but you may swap it out with your own. Buy building the project it is already strong named. You may remove this but you will be responsible for putting it where it belongs so the COM client can find it.

3) move the file so SQL can see the COM Object

Take the EncryptDecrypt.dll and copy that to your SQL servers binn folder. This is usually in the C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder.

4) Generate a type library

Once you have copied the file you will need to register the component to make it visable to a COM client. You do this using the Regasm utility

Regasm /tlb:EncryptDecrypt binn\EncryptDecrypt.dll

5) Register the assembly in the GAC

In order for any COM clinet to see the .NET assembly, we need to register the assembly in the Global Assembly Cache, use the GACUTIl utility to register the assembly with the GAC

GACUTIL /i binn\EncryptDecrypt.dll

6) Install the functions to SQL

Open the EncryptDecryptFunctions.sql in your Query Analyzer select the database you wish to install the functions into, and execute the SQL script. This script will create 4 functions EncryptTextNoPWD, EncryptTextWPWD, DecryptTextNoPwd, and DecryptTextWPWD

The following are examples on how to use the functions in your SQL

--Encrypt text using the internal password

select dbo.EncryptStringnoPWD('test')

--Decrypt the encrypted text using the internal password

Select dbo.DecryptStringNoPWD('NzevW30d2I9egnLSz+PDvw==')

--Encrypt text using a user supplied password of ‘froggy’

select dbo.EncryptStringwPWD('test','froggy')

--Decrypt encrypted using a user supplied password of ‘froggy’

Select dbo.DecryptStringWPWD('eKO76BsvLSJMWK7kF6Mfpw==','froggy')

Good Luck and I hope this helps someone. If you find this software useful drop me an email and tell me about your experiences at OneSpecialDJ@yahoo.com

Peace

George