(SQL Server) Deriving a 256-bit AES Secret Key from a Password Using SHA256

This example demonstrates how to derive a 256-bit secret key for AES encryption from an password string. (A more secure way of deriving secret keys from passwords is via PBKDF2, and Chilkat has examples for this on example-code.com).

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @password nvarchar(4000)
    SELECT @password = 'mySecretPassword'

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'

    -- We're going to hash the utf-8 byte representation of the password.
    -- For this example, the utf-8 byte representation is identical to the us-ascii 
    -- representation because there are no 8bit chars (such as non-English or chars with accent marks.)
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'

    -- Compute the SHA-256 hash of the password, and return the hash
    -- in hexidecimal string form:
    DECLARE @strSecretKeyHex nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashStringENC', @strSecretKeyHex OUT, @password

    -- Set our encryption algorithm to 256-bit AES (CBC mode)
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'

    -- Now set the 256-bit binary secret key:
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @strSecretKeyHex, 'hex'

    -- That's all for this example.  We've setup the encryptor with our
    -- 256-bit key derived from a password string.  There are other examples
    -- to show the calls for encrpyting / decrypting..

    EXEC @hr = sp_OADestroy @crypt


END
GO