(SQL Server) ECDSA Sign Data and Verify Signature

Demonstrates using the Elliptic Curve Digital Signature Algorithm to hash data and sign it. Also demonstrates how to verify the ECDSA signature.

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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- To create an ECDSA signature, the data first needs to be hashed.  Then the hash
    -- is signed.

    -- Use Chilkat Crypt2 to generate a hash for any of the following
    -- hash algorithms: SHA256, SHA384, SHA512, SHA1, MD5, MD2, HAVAL, RIPEMD128/160/256/320

    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

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'SHA256'
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    -- Hash a string.
    DECLARE @hash1 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashStringENC', @hash1 OUT, 'The quick brown fox jumps over the lazy dog'

    PRINT 'hash1 = ' + @hash1

    -- Or hash a file..
    DECLARE @hash2 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashFileENC', @hash2 OUT, 'qa_data/hamlet.xml'

    PRINT 'hash2 = ' + @hash2

    -- (The Crypt2 API provides many other ways to hash data..)

    -- -----------------------------------------------------------
    -- An ECDSA private key is used for signing.  The public key is for signature verification.
    -- Load our ECC private key.
    -- Our private key file contains this:

    -- 	// -----BEGIN PRIVATE KEY-----
    -- 	MIGHAgEAMBMGByqGSM49AgEGCCqGSM49AwEHBG0wawIBAQQg3J8q/24D1sEKGdP9
    -- 	72MGYElLGpw/a56Y3t6pfON3uhShRANCAATlSmoizyhAwoYZAOuFBATl07/1RR54
    -- 	a1Dzfm16grxJe666AGKR+bSs24hk7TEpaeCTvT8YOOM3l+xKFg7zq6Q9
    -- 	-----END PRIVATE KEY-----

    DECLARE @privKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    DECLARE @success int
    EXEC sp_OAMethod @privKey, 'LoadPemFile', @success OUT, 'qa_data/ecc/secp256r1-key-pkcs8.pem'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- We'll need a PRNG source for random number generation.
    -- Use Chilkat's PRNG (for the Fortuna PRNG algorithm).
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    -- Sign the hash..
    DECLARE @ecdsa int
    -- Use "Chilkat_9_5_0.Ecc" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ecdsa OUT

    DECLARE @ecdsaSigBase64 nvarchar(4000)
    EXEC sp_OAMethod @ecdsa, 'SignHashENC', @ecdsaSigBase64 OUT, @hash1, 'base64', @privKey, @prng
    EXEC sp_OAGetProperty @ecdsa, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ecdsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @ecdsa
        RETURN
      END


    PRINT 'ECDSA signature = ' + @ecdsaSigBase64

    -- -----------------------------------------------------------
    -- Now let's verify the signature using the public key.

    DECLARE @pubKey int
    -- Use "Chilkat_9_5_0.PublicKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT

    EXEC sp_OAMethod @pubKey, 'LoadFromFile', @success OUT, 'qa_data/ecc/secp256r1-pubkey.pem'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pubKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @ecdsa
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END

    DECLARE @result int
    EXEC sp_OAMethod @ecdsa, 'VerifyHashENC', @result OUT, @hash1, @ecdsaSigBase64, 'base64', @pubKey
    IF @result = 1
      BEGIN

        PRINT 'Signature is valid.'
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @ecdsa
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END
    IF @result = 0
      BEGIN

        PRINT 'Signature is invalid.'
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @ecdsa
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END
    IF @result < 0
      BEGIN
        EXEC sp_OAGetProperty @ecdsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'The VerifyHashENC method call failed.'
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @ecdsa
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @ecdsa
    EXEC @hr = sp_OADestroy @pubKey


END
GO