(SQL Server) BIP39 Compute Binary Seed from Mnemonic

See more Encryption Examples

Creates a binary seed from a mnemonic. Uses the PBKDF2 function with a mnemonic sentence (in UTF-8 NFKD) used as the password and the string "mnemonic" + passphrase (again in UTF-8 NFKD) used as the salt. The iteration count is set to 2048 and HMAC-SHA512 is used as the pseudo-random function. The length of the derived key is 512 bits (= 64 bytes).

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
    DECLARE @sTmp0 nvarchar(4000)
    -- 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_9_5_0.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Test with the test vectors at https://github.com/trezor/python-mnemonic/blob/master/vectors.json

    -- This is the 2nd test vector..
    DECLARE @mnemonic nvarchar(4000)
    SELECT @mnemonic = 'legal winner thank year wave sausage worth useful legal winner thank yellow'
    DECLARE @passphrase nvarchar(4000)
    SELECT @passphrase = 'TREZOR'
    DECLARE @expectedSeed nvarchar(4000)
    SELECT @expectedSeed = '2e8905819b8723fe2c1d161860e5ee1830318dbf49a83bd451cfb8440c28bd6fa457fe1296106559a3c80937a1c1069be3a3a5bd381ee6260e8d9739fce1f607'
    DECLARE @expectedMasterKey nvarchar(4000)
    SELECT @expectedMasterKey = 'xprv9s21ZrQH143K2gA81bYFHqU68xz1cX2APaSq5tt6MFSLeXnCKV1RVUJt9FWNTbrrryem4ZckN8k4Ls1H6nwdvDTvnV7zEXs2HgPezuVccsq'

    -- The mnemonic sentence (in UTF-8 NFKD) used as the password.
    -- The string "mnemonic" + passphrase (again in UTF-8 NFKD) used as the salt.
    -- The iteration count is set to 2048 and HMAC-SHA512 is used as the pseudo-random function.
    -- The length of the derived key is 512 bits (= 64 bytes). 

    -- We want the computed seed to be lowercase hex, therefore our salt must also be hex.
    -- The seed is the keyword "mnemonic" + passphrase (in this case is "TREZOR") converted to hex.
    DECLARE @bdSalt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdSalt OUT

    DECLARE @success int
    EXEC sp_OAMethod @bdSalt, 'AppendString', @success OUT, 'mnemonic', 'utf-8'
    EXEC sp_OAMethod @bdSalt, 'AppendString', @success OUT, @passphrase, 'utf-8'

    DECLARE @computedSeed nvarchar(4000)
    EXEC sp_OAMethod @bdSalt, 'GetEncoded', @sTmp0 OUT, 'hex_lower'
    EXEC sp_OAMethod @crypt, 'Pbkdf2', @computedSeed OUT, @mnemonic, 'utf-8', 'sha512', @sTmp0, 2048, 512, 'hex_lower'


    PRINT 'Expected: ' + @expectedSeed

    PRINT 'Computed: ' + @computedSeed

    -- To compute the hd_master_key, duplicate this Python code:

    --     def to_hd_master_key(seed: bytes, testnet: bool = False) -> str:
    --         if len(seed) != 64:
    --             raise ValueError("Provided seed should have length of 64")
    -- 
    --         # Compute HMAC-SHA512 of seed
    --         seed = hmac.new(b"Bitcoin seed", seed, digestmod=hashlib.sha512).digest()
    -- 
    --         # Serialization format can be found at: https://github.com/bitcoin/bips/blob/master/bip-0032.mediawiki#Serialization_format
    --         xprv = b"\x04\x88\xad\xe4"  # Version for private mainnet
    --         if testnet:
    --             xprv = b"\x04\x35\x83\x94"  # Version for private testnet
    --         xprv += b"\x00" * 9  # Depth, parent fingerprint, and child number
    --         xprv += seed[32:]  # Chain code
    --         xprv += b"\x00" + seed[:32]  # Master key
    -- 
    --         # Double hash using SHA256
    --         hashed_xprv = hashlib.sha256(xprv).digest()
    --         hashed_xprv = hashlib.sha256(hashed_xprv).digest()
    -- 
    --         # Append 4 bytes of checksum
    --         xprv += hashed_xprv[:4]
    -- 
    --         # Return base58
    --         return b58encode(xprv)

    -- First compute the HMAC-SHA512 of the computedSeed
    DECLARE @bdSeed int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdSeed OUT

    EXEC sp_OAMethod @bdSeed, 'AppendEncoded', @success OUT, @computedSeed, 'hex_lower'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex_lower'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha512'
    EXEC sp_OAMethod @crypt, 'SetMacKeyString', @success OUT, 'Bitcoin seed'
    DECLARE @hmacSha512_hex nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'MacBdENC', @hmacSha512_hex OUT, @bdSeed

    DECLARE @bdHmac int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdHmac OUT

    EXEC sp_OAMethod @bdHmac, 'AppendEncoded', @success OUT, @hmacSha512_hex, 'hex_lower'

    DECLARE @bdXprv int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdXprv OUT

    EXEC sp_OAMethod @bdXprv, 'AppendEncoded', @success OUT, '0488ade4', 'hex_lower'
    EXEC sp_OAMethod @bdXprv, 'AppendEncoded', @success OUT, '000000000000000000', 'hex_lower'
    EXEC sp_OAMethod @bdHmac, 'GetEncodedChunk', @sTmp0 OUT, 32, 32, 'hex_lower'
    EXEC sp_OAMethod @bdXprv, 'AppendEncoded', @success OUT, @sTmp0, 'hex_lower'
    EXEC sp_OAMethod @bdXprv, 'AppendByte', @success OUT, 0
    EXEC sp_OAMethod @bdHmac, 'GetEncodedChunk', @sTmp0 OUT, 0, 32, 'hex_lower'
    EXEC sp_OAMethod @bdXprv, 'AppendEncoded', @success OUT, @sTmp0, 'hex_lower'

    -- Double hash using SHA256
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex_lower'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    DECLARE @bdHash int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdHash OUT

    EXEC sp_OAMethod @crypt, 'HashBdENC', @sTmp0 OUT, @bdXprv
    EXEC sp_OAMethod @bdHash, 'AppendEncoded', @success OUT, @sTmp0, 'hex_lower'
    DECLARE @secondHash nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashBdENC', @secondHash OUT, @bdHash
    EXEC sp_OAMethod @bdHash, 'Clear', @success OUT
    EXEC sp_OAMethod @bdHash, 'AppendEncoded', @success OUT, @secondHash, 'hex_lower'

    -- Append the 1st 4 bytes of the bdHash to bdXprv.
    EXEC sp_OAMethod @bdHash, 'GetEncodedChunk', @sTmp0 OUT, 0, 4, 'hex_lower'
    EXEC sp_OAMethod @bdXprv, 'AppendEncoded', @success OUT, @sTmp0, 'hex_lower'

    -- Base58 encode bdXprv
    DECLARE @computedMasterKey nvarchar(4000)
    EXEC sp_OAMethod @bdXprv, 'GetEncoded', @computedMasterKey OUT, 'base58'


    PRINT 'Expected Master Key: ' + @expectedMasterKey

    PRINT 'Computed Master Key: ' + @computedMasterKey

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bdSalt
    EXEC @hr = sp_OADestroy @bdSeed
    EXEC @hr = sp_OADestroy @bdHmac
    EXEC @hr = sp_OADestroy @bdXprv
    EXEC @hr = sp_OADestroy @bdHash


END
GO