Using COM to Encrypt Passwords

This example will show you how easy it is to put the power of C++ or Visual Basic into your stored procedures.

Password Encryption using a Com Object

This example will show you how easy it is to put the power of C++ or Visual Basic into your stored procedures.

Before trying to use this example make sure that you have these stored procedures installed:

1. sp_displayoaerrorinfo
2. sp_hexadecimal

These are system stored procedures that are required for using com objects in a stored procedure. If you cannot find them in your master database find the article “OLE Automation Return Codes and Error Information” in your MSDN documentation.

This example uses a COM object to encrypt a password before inserting it into a table. After it has been inserted into a table another stored procedure can be used to check if passwords match. This checking will be used when a user of an application, such as an ASP intranet page, tries to access the resource. The users do not have logins from NT or SQL Server, so a separate table of logins and passwords has been created.

We create a COM object for encryption using Visual Basic. Then we instantiate and use the object in a stored procedure. This COM object does simple encryption of a string and returns it.

The class is called EncryptionClass in a project called Encrypt. Register the DLL on your SQL Server by using regsvr32. If you have an SQL Server machine with VB on it you can also register the dll by loading the class into a VB project and invoking the “Make DLL” command.

The main stored procedure is sp_encryptPassword.

1. The procedure uses sp_OACreate to instantiate the class.
2. The procedure calls sp_OAMethod to use the object’s method EncryptString.
3. The procedure destroys the object with sp_OADestroy

Now you can use the sp_encryptPassword procedure in other procedures

1. Inserting an encrypted string into a table.
2. Taking user input of a password, encrypting it and comparing it against the encrypted password in a table.

Here is the SQL your application would use to call the stored procedures

–insert a password

—sp_insertLogin will encrypt the password before saving it to the logins table

–sp_insertLogin calls the sp_encrypt procedure

sp_insertLogin ‘lsmith’, ‘les’, ‘smith’, ‘zzz’

–check for a password

–this will return 0 because it is incorrect

DECLARE @pwdCheck INT
EXEC @pwdCheck = sp_checkPassword 'lsmith', 'azz'
SELECT @pwdCheck

--check for a password
--this will return 1 because it is correct

DECLARE @pwdCheck INT
EXEC @pwdCheck = sp_checkPassword 'lsmith', 'zzz'
SELECT @pwdCheck