Click here to Skip to main content
11,708,312 members (68,015 online)
Click here to Skip to main content

SQL Encryption and Decryption Library (Using Rijndael)

, 25 Sep 2009 CPOL 55K 716 41
Rate this:
Please Sign up or sign in to vote.
This library allows a user to use functions in SQL Server to encrypt and decrypt text.

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

  1. Build the library.
  2. 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.

  3. Create a strong named assembly.
  4. 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.

  5. Move the file so SQL can see the COM object.
  6. 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.

  7. Generate a type library.
  8. 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
  9. Register the assembly in the GAC.
  10. 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
  11. Install the functions to SQL Server.
  12. 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.

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 the encrypted text 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 goakes@tiresplus.com.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

George Oakes
Web Developer
United States United States
I have been developing software since 1995, and I enjoy it very much. My current position allows me to keep up with the latest technology, and it is refreshing.

I am married, and live with my wonderful wife, and our 2 lovely daughters.

I enjoy Woodworking, Fishing, Camping, and Auto racing. I live on the west coast of Florida and enjoy spending my free time outdoors, or building stuff in my workshop.

You may also be interested in...

Comments and Discussions

 
QuestionNull Pin
Member 1027615416-Sep-13 1:03
memberMember 1027615416-Sep-13 1:03 
QuestionCould you provide detail steps for step 6 Install the functions to SQL Server? Pin
Member 960044310-Apr-13 7:47
memberMember 960044310-Apr-13 7:47 
GeneralThank you very much! Pin
jbarrientos10-Feb-10 6:59
memberjbarrientos10-Feb-10 6:59 
GeneralWorks great! Pin
bjf321-Jan-09 3:01
memberbjf321-Jan-09 3:01 
NewsTwo other related encryption articles in CodeProject ... Pin
Tony Selke27-Sep-07 7:02
memberTony Selke27-Sep-07 7:02 
Questiontypes other then string? Pin
new bie21-Aug-07 22:03
membernew bie21-Aug-07 22:03 
hi

no doubt it is really a nice article. But i have certain queries.

1. If the column to be encrypted is other then string, like int, double etc. then this code will work or not? Please suggest

2. If we use this encryption algo., it will generate cipher text of length greater then the original length. So in case the column length is fixed and we will try to update the DB with encrypted text then SQL error might be generated or text can be trimmed. This is going to harm the decryption process. So what sort of encryption can be used in these cases?

new bie
AnswerRe: types other then string? Pin
George Oakes22-Aug-07 2:24
memberGeorge Oakes22-Aug-07 2:24 
GeneralRe: types other then string? Pin
new bie30-Aug-07 0:40
membernew bie30-Aug-07 0:40 
GeneralRe: types other then string? Pin
George Oakes30-Aug-07 3:51
memberGeorge Oakes30-Aug-07 3:51 
GeneralAlmost got it .... Pin
SteveCliff14-Jun-07 5:34
memberSteveCliff14-Jun-07 5:34 
GeneralRe: Almost got it .... Pin
SteveCliff14-Jun-07 5:51
memberSteveCliff14-Jun-07 5:51 
GeneralRe: Almost got it .... Pin
SteveCliff14-Jun-07 21:47
memberSteveCliff14-Jun-07 21:47 
QuestionWhat is the process to use this dll in asp ? [modified] Pin
Raghu Bhandari13-May-07 21:28
memberRaghu Bhandari13-May-07 21:28 
AnswerRe: What is the process to use this dll in asp ? Pin
George Oakes14-May-07 2:12
memberGeorge Oakes14-May-07 2:12 
GeneralGetting an error when tried in c# Pin
Sagar VM19-Sep-06 19:27
memberSagar VM19-Sep-06 19:27 
GeneralRe: Getting an error when tried in c# Pin
George Oakes20-Sep-06 1:45
memberGeorge Oakes20-Sep-06 1:45 
GeneralGood Explanation ! Pin
Farrukh_528-Aug-06 21:41
memberFarrukh_528-Aug-06 21:41 
GeneralRe: Good Explanation ! Pin
George Oakes29-Aug-06 1:24
memberGeorge Oakes29-Aug-06 1:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150819.1 | Last Updated 25 Sep 2009
Article Copyright 2006 by George Oakes
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid