Download source files - 26.8 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 algorithm is Rijndael. I used this because it is more commonly available on most platforms, as well as it is the new government standard AES encryption method.

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

Installation Instructions

To use this COM object with SQL Server 2000 or 2005, compile this project with Visual Studio 2005, and the DLL will be in the bin\release folder.

This project already has a strong named key, but you may swap it out with your own. By 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.

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

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

Regasm /tlb:EncryptDecrypt binn\EncryptDecrypt.dll

In order for any COM client 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

Open 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 four functions: EncryptTextNoPWD, EncryptTextWPWD, DecryptTextNoPwd, and DecryptTextWPWD.

Build the library.
Create a strong named assembly.
Move the file so SQL can see the COM object.
Generate a type library.
Register the assembly in the GAC.
Install the functions to SQL Server.

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

select dbo.EncryptStringnoPWD('test')

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

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

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

Encrypt text using the internal password:
Decrypt the encrypted text using the internal password:
Encrypt text using a user supplied password of ‘froggy’:
Decrypt the encrypted text using a user supplied password of ‘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 goakes27@gmail.com .

SQL Encryption and Decryption Library (Using Rijndael)