Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 7, 2001

Using COM to Encrypt Passwords

By Les Smith

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

EXEC @pwdCheck = sp_checkPassword 'lsmith', 'azz'
SELECT @pwdCheck
--check for a password
--this will return 1 because it is correct

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM