Cryptographics Stored Procedures for SQL Server 7.0, 2000, 2005 (Win32)
These procedures are implemented in xpFileUtils library.
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
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
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.