Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

RC4 Encryption in a Stored Procedure

By Joseph Gama,

RC4 Encryption SP's/UDF's with TSQL calling activeX

These are stored procedures and functions to encrypt/decrypt data using the RC4 or Rijndael encryption algorithm. This is an interesting and useful example of calling COM objects with TSQL.

For learning purposes the examples are very simple and based on a few lines of code, something similar to:

declare @hr int
exec @hr =sp_oacreate 'OLEComponent.Object', @object out
exec @hr =sp_oamethod @object, 'RC4ED', ReturnValue out,@parameter,@parameter
EXEC @hr = sp_OADestroy @object

The variable @hr will store the return value from the system stored procedure call to ensure that the call was successful. If the value is other than zero it means that an error occurred. sp_oacreate will create an instance of the object previously created with VB6 and properly registered as an ActiveX control. VB6 will do that automatically when compiling the code. The next line of code uses sp_oamethod which will call a method of the ActiveX control. In this example, there are only two methods, encrypt and decrypt but there could be properties too. Finally, sp_OADestroy will destroy the instance we used before.

The system stored procedures used are the following:

sp_oacreate-Creates an instance of the OLE object.
sp_OAMethod-Calls a method of an OLE object.
sp_OADestroy-Destroys an instance of an OLE object.

Other useful ones:

sp_OAGetProperty-Gets a property value of an OLE object.
sp_OASetProperty-Sets a property of an OLE object.
sp_OAGetErrorInfo-Returns an error code from the latest OLE Automation operation.

 

RC4 Encryption

Stored Procedures

Sp_RC4- Encrypts any data type input to a varchar data type. Used to encrypt character data, such as text.

Sp_RC4Bin- Encrypts any data type input to a varbinary data type. Used to encrypt binary data, such as numbers or pictures.

User Defined Functions

XRC4- Encrypts any data type input to a varchar data type. Used to encrypt character data, such as text.

XRC4Bin- Encrypts any data type input to a varbinary data type. Used to encrypt binary data, such as numbers or pictures.

Rijndael Encryption

Stored Procedures

Sp_EncRijndael- Encrypts any data type input to a varchar data type. Used to encrypt character data, such as text.

Sp_DecRijndael- Decrypts any data type input to a varchar data type.

Sp_EncRijndaelBin- Encrypts any data type input to a varbinary data type. Used to encrypt binary data, such as pictures.

Sp_DecRijndaelBin - Decrypts any data type input to a varbinary data type.

User Defined Functions

EncRijndael- Encrypts any data type input to a varchar data type. Used to encrypt character data, such as text.

DecRijndael- Decrypts any data type input to a varchar data type.

EncRijndaelBin- Encrypts any data type input to a varbinary data type. Used to encrypt binary data, such as pictures.

DecRijndaelBin - Decrypts any data type input to a varbinary data type.

It works by calling an activeX DLL which has to be created first.

Files

RC4 files
Rijndael files

To create and use the activeX DLL:

1-Open the project in VB6.
2-Compile the DLL.
3-Copy the DLL to c:\winnt\system32
4-Register the DLL using the START MENU: START/RUN regsvr32 c:\winnt\system32\crypt.Dll

The stored procedures will work fine now.

RC4 code based on Eric Hodges' RC4 Implementation
http://www.planet-source-code.com/vb/scripts/showcode.asp?lngWId=1&txtCodeId=29691

He deserves the credit for it.

I used the code from Queen City Software - Rijndael Encryption - to create the activeX dll

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=24820&lngWId=1

They deserve the credit for it.

Joseph Gama

Total article views: 8645 | Views in the last 30 days: 0
 
Related Articles
FORUM

Parameterised Input stored procedure

Parameterised Input stored procedure

FORUM

SQLS2K: How to encrypt and decrypt objects

SQLS2K: How to encrypt and decrypt objects

FORUM

Encrypted Stored Procedures

Encrypted Stored Procedures

SCRIPT

Unused Input Parameters

Identify all objects containing unused input parameters.

FORUM

xml input for stored procedure

xml input for stored procedure

Tags
miscellaneous    
programming    
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones