MasterCluster.software Contact   About
Apps for Windows Source Code SQL Server Extensions  

Cryptographics Stored Procedures for SQL Server 7.0, 2000, 2005 (Win32)

Important note

These procedures are implemented in xpFileUtils library.

SQL Cryptographic Utilities Overview

This library uses Microsoft Cryptographic API (CryptoAPI) to operate.

There are the following extended stored procedures:

  • xp_HashCreate creates a new hash object (MD5 or SHA) and returns a handle on it

  • xp_HashAppend appends data to hash

  • xp_HashClose returns hash value and then closes the hash handle

  • xp_md5_quick returns MD5-hash on a given value

  • xp_md5_quick_compare checks if a given value is OK to a given MD5-hash

  • xp_sha_quick returns SHA-hash on a given value

  • xp_sha_quick_compare checks if a given value is OK to a given SHA-hash

  • xp_symmetric_quick_encode encodes data with password using either RC2, RC4 or DES algorithm

  • xp_symmetric_quick_decode decodes data protected with password using either RC2, RC4 or DES algorithm

  • xp_GetRandomData returns a random bytes value of the specified length

Example Example

Data hashes can be used to store users' passwords securely, not in a form how users type them.

As user types his password and passes it to SQL Server, then SQL Server calls one of xp_xxx_quick to hash the password's data:

declare @sOriginalPassword varchar(15), @sHashedPassword varchar(50), @iResult int

set @sOriginalPassword = 'A VeRy SeCrEt PaSsWoRd!'
exec master..xp_md5_quick @sOriginalPassword, @sHashedPassword OUTPUT

After that, the hashed password data can be stored in a table record associated to the user's account.

Then, as user need to be authorized next time, he enters his password again and SQL Server calls one of xp_xxx_quick_compare to check if a stored hash value is identical to the incoming password's hash:

-- Assume @sOriginalPassword is the incoming user's password to be verified,
-- and @sHashedPassword is a hash of the original user's password.
exec @iResult = master..xp_md5_quick_compare @sOriginalPassword, @sHashedPassword
if @iResult = 1
  print 'Compare: The source data is «OK» for the given hash'
else if @iResult = 0
  print 'Compare: The source data is «BAD» for the given hash'
else if @iResult = -1
  print 'Compare: Fatal error'

This technique can be also used to store and verify the other types of private data, such as credit card numbers.

Copyright © 2003-2017 MasterCluster.com
Share |