SQL Server 2008 Hashing and Signing Data

Prior to SQL Server 2005, T-SQL included a couple of very simple, very basic hash functions: CHECKSUM and BINARY_CHECKSUM. Neither of these hash functions is collision-free, and both return a 32-bit hash, which is well below the minimum length recommended by cryptographic experts for secure applications.

Introduced in SQL Server 2005, the HashBytes function accepts the name of a hash algorithm and an input string, as follows:

SELECT HashBytes ('SHA1', 'Now is the time for all good men...');

The hash algorithm used in the example is SHA-1. You can use MD2, MD4, MD5, SHA, or SHA-1 for this parameter. The former three are the Message Digest algorithms, which generate 128-bit hashes of the input. The latter two are the Secure Hash Algorithm, which generates a 160-bit digest of the input. The input to the HashBytes function is a varchar, an nvarchar, or a varbinary value. The result of HashBytes is always a varbinary value with amaximum length of 8,000 bytes.

SQL Server also provides functions to sign data with certificates and asymmetric keys, and to verify those signatures. This is useful for protecting the integrity of sensitive data, since any small change in the data will affect the signature. The SignByCert and SignByAsymKey functions sign your data with a certificate or an asymmetric key and return the signature as a varbinary. The length of the signature depends on the length of the certificate or asymmetric key’s private key. A 2,048-bit private key generates a 256-byte signature; a 1,024-bit private key generates a 128-byte signature; and so on. The formats for SignByCert and SignByAsymKey are as follows:

SignByCert ( certificate_ID, plaintext, password )
SignByAsymKey ( asym_key_ID, plaintext, password )

The SignByCert function accepts a certificate ID, which can be retrieved with the Cert_ID function. The SignByAsymKey function accepts the asymmetric key ID, which is retrieved with the AsymKey_ID function. The plaintext parameter in both functions is the plain text to be signed—a char, a varchar, an nchar, or an nvarchar value. The password is the password required to decrypt the certificate or asymmetric key, if it is protected by password.

You can verify previously signed data with the VerifySignedByCert and VerifySignedByAsymKey functions, which have the following format:

VerifySignedByCert ( certificate_ID, plaintext, signature )
VerifySignedByAsymKey ( asym_key_ID, plaintext, signature )

The VerifySignedByCert and VerifySignedByAsymKey functions accept a certificate ID and an asymmetric key ID, respectively. The plaintext parameter of both functions is the plain text that was previously signed, and the signature parameter is the varbinary signature that was generated. These two functions generate the signature for the plaintext value and compare the newly generated signature to the signature you pass in to the function. Both functions return a 1 if the data matches the signature, or a 0 if the data and signature do not match.

Source of Information : Apress Accelerated SQL Server 2008


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner