SQL Server

PBKDF1 - Derive Key from Password

See more Encryption Examples

Demonstrates how to derive a symmetric encryption key from a password using PBKDF1. This example matches the results found at this URL: http://www.di-mgt.com.au/cryptoKDFs.html#examplespbkdf

It also matches the output produced by the .NET Framework using this C# code:

            byte[] salt = new byte[] { 0x78, 0x57, 0x8E, 0x5A, 0x5D, 0x63, 0xCB, 0x06 };
            PasswordDeriveBytes cdk = new PasswordDeriveBytes("password", salt);

            cdk.IterationCount = 1000;
            cdk.HashName = "SHA1";

            // generate a 16-byte key
            byte[] key = cdk.GetBytes(16);

SQL Server

-- 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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @hexKey nvarchar(4000)

    -- http://www.di-mgt.com.au/cryptoKDFs.html#examplespbkdf

    DECLARE @pw nvarchar(4000)
    SELECT @pw = 'password'
    DECLARE @pwCharset nvarchar(4000)
    SELECT @pwCharset = 'ansi'
    -- Hash algorithms may be: sha1, md2, md5, etc.
    DECLARE @hashAlg nvarchar(4000)
    SELECT @hashAlg = 'sha1'
    -- The salt should be 8 bytes:
    DECLARE @saltHex nvarchar(4000)
    SELECT @saltHex = '78578E5A5D63CB06'
    DECLARE @iterationCount int
    SELECT @iterationCount = 1000
    -- Derive a 128-bit key from the password.
    DECLARE @outputBitLen int
    SELECT @outputBitLen = 128

    -- The derived key is returned as a hex or base64 encoded string.
    -- (Note: The salt argument must be a string that also uses
    -- the same encoding.)
    DECLARE @enc nvarchar(4000)
    SELECT @enc = 'hex'

    EXEC sp_OAMethod @crypt, 'Pbkdf1', @hexKey OUT, @pw, @pwCharset, @hashAlg, @saltHex, @iterationCount, @outputBitLen, @enc


    PRINT @hexKey

    -- The output should have this value:
    -- DC19847E05C64D2FAF10EBFB4A3D2A2

    EXEC @hr = sp_OADestroy @crypt


END
GO