Click here to Skip to main content
Click here to Skip to main content

SQL Encryption and Decryption Library (Using Rijndael)

By , 25 Sep 2009
Rate this:
Please Sign up or sign in to vote.

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)

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.

Comments and Discussions

 
QuestionNull PinmemberMember 1027615416-Sep-13 1:03 
QuestionCould you provide detail steps for step 6 Install the functions to SQL Server? PinmemberMember 960044310-Apr-13 7:47 
GeneralThank you very much! Pinmemberjbarrientos10-Feb-10 6:59 
GeneralWorks great! Pinmemberbjf321-Jan-09 3:01 
NewsTwo other related encryption articles in CodeProject ... PinmemberTony Selke27-Sep-07 7:02 
Questiontypes other then string? Pinmembernew bie21-Aug-07 22:03 
AnswerRe: types other then string? PinmemberGeorge Oakes22-Aug-07 2:24 
GeneralRe: types other then string? Pinmembernew bie30-Aug-07 0:40 
GeneralRe: types other then string? PinmemberGeorge Oakes30-Aug-07 3:51 
GeneralAlmost got it .... PinmemberSteveCliff14-Jun-07 5:34 
GeneralRe: Almost got it .... PinmemberSteveCliff14-Jun-07 5:51 
And I've now rebooted the server for the hell of it to make sure everything is tidy and the error is gone. But calling any of the routines simply returns a NULL value .... Frown | :(
GeneralRe: Almost got it .... PinmemberSteveCliff14-Jun-07 21:47 
QuestionWhat is the process to use this dll in asp ? [modified] PinmemberRaghu Bhandari13-May-07 21:28 
AnswerRe: What is the process to use this dll in asp ? PinmemberGeorge Oakes14-May-07 2:12 
GeneralGetting an error when tried in c# PinmemberSagar VM19-Sep-06 19:27 
GeneralRe: Getting an error when tried in c# PinmemberGeorge Oakes20-Sep-06 1:45 
GeneralGood Explanation ! PinmemberFarrukh_528-Aug-06 21:41 
GeneralRe: Good Explanation ! PinmemberGeorge 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 | Mobile
Web03 | 2.8.140415.2 | Last Updated 25 Sep 2009
Article Copyright 2006 by George Oakes
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid