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
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
