Click here to Skip to main content
13,554,862 members
Click here to Skip to main content
Add your own
alternative version


42 bookmarked
Posted 23 Aug 2006
Licenced CPOL

SQL Encryption and Decryption Library (Using Rijndael)

, 25 Sep 2009
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.


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


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.

You may also be interested in...


Comments and Discussions

QuestionUse CLR Sql Server Store Procedure ? Pin
kiquenet.com22-Jan-17 21:17
professionalkiquenet.com22-Jan-17 21:17 
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 
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 

first let me say yes you could encrypt a stored procedure, however it would not work. SQL 2000 and 2005 have a method to encrypt stored procedures and they remain usable you must us the Encryption key work when creating the proc


CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]



AS sql_statement [ ...n ]

You might also want to search the web on how easy it is to decrypt them back again

My program was mainly designed for encrypting and decrypting data only, thanks for the comment

Get access to awsome .Net Videos at

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

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

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