SQL Encryption and Decryption Library (Using Rijndael)

/**********************************************************************************************
					SQL Encryption and Decryption COM compatable object
					
	Author:		George L. Oakes
	Date:		8/23/2006
	Email:		OneSpecialdj@yahoo.com
	Copyright:	This source code is provided under the OPEN SOURCE License included in the file 
				OpenSource.eula. You may use copy and re-distribute the software providing the Author
				, and the OpenSource.eula remain intact and are not removed or altered in anyway.

	Description:	This project creates a COM callable object that can be used by SQL 2000 or newer 
				to encrypt text in the database. The encryption uses the new Federaly accepted standard ,
				Rijndael encryption. The project contains all the source code to build the COM object and the 
				SQL Scripts to create the functions needed to encrypt text and unencrypt text in a
				SQL database. This library could also be used in other projects, but was originaly designed so 
				Text* could be encrypted and decrypted in a SQL
	database
				
	Public Object Methods:
	
		EncryptTextNoPWD:	This method takes plain text and encrypts the text using the internal** password
			Inputs:			Text to be encrypted (Note this text can only be unencrypted using the DecryptNoPwd method.
			Outputs:		The Rijndael encrypted text

		EncryptTextWPWD:	This method takes plain text and a password the user must supply, and encrypts the text
			Inputs:			Text to be Encrypted, password supplied by the user
			Outputs:		The Rijndael encrypted text with the users paassword
			
		DecryptTextNoPWD:	This method takes an encrypted string and decrypts the text using the internal** password
			Inputs:			Text to be unencrypted (NOTE this text must have been encrypted using the EncryptTextNoPWD method.
			Outputs:		The Plain (original) text encrypted by the EncryptTextNoPwd method
			
		DecryptTextWPWD:	This method takes an encrypted string and decrypts the text using the user supplied text, this must be
							the same password the user used to encrypt the text with.
			Inputs:			Encrypted text to be unencrypted (NOTE the same password must be supplied that was used to encrypt the text)
			Outputs:		The Plain (original) text encrypted by the EncryptTextWPWD
			
			
	* = Text no other datatypes were addressed in the project, and assumes the user is using Varchar/nVarchar data types in SQL. 
		It would be up to the user to modify this program to accept other datatypes.
		
	**= internat password; This project contains a "HARD-CODED" Password and it is located in the crypto.vb class, you may change
		the password to anything you like but it must remain identical in the Encrypt and decrpyt functions so the data can be 
		successfuly encrypted and decrypted.
	

***********************************************************************************************/

	Installation Instructions:

1) build the library
To use this COM Object with your SQL server 2000 or 2005 compile this project, and look 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