(SQL Server) DSA Signature Create and Verify

Shows how to create a DSA (DSS) signature for the contents of a file. The first step is to create an SHA-1 hash of the file contents. The hash is signed using the Digital Signature Algorithm and the signature bytes are retrieved as a hex-encoded string.

The 2nd part of the example loads the signature and verifies it against the hash.

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
    -- 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.

    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, 'EncodingMode', 'hex'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha-1'

    -- Return the SHA-1 hash of a file.  The file may be any size.
    -- The Chilkat Crypt component will stream the file when 
    -- computing the hash, keeping the memory usage constant
    -- and reasonable.
    -- The 20-byte SHA-1 hash is returned as a hex-encoded string.
    DECLARE @hashStr nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashFileENC', @hashStr OUT, 'hamlet.xml'

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

    -- Load a DSA private key from a PEM file.  Chilkat DSA
    -- provides the ability to load and save DSA public and private
    -- keys from encrypted or non-encrypted PEM or DER.
    -- The LoadText method is for convenience only.  You may
    -- use any means to load the contents of a PEM file into
    -- a string.
    DECLARE @pemPrivateKey nvarchar(4000)

    EXEC sp_OAMethod @dsa, 'LoadText', @pemPrivateKey OUT, 'dsa_priv.pem'
    DECLARE @success int
    EXEC sp_OAMethod @dsa, 'FromPem', @success OUT, @pemPrivateKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        RETURN
      END

    -- You may optionally verify the key to ensure that it is a valid
    -- DSA key.
    EXEC sp_OAMethod @dsa, 'VerifyKey', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        RETURN
      END

    -- Load the hash to be signed into the DSA object:
    EXEC sp_OAMethod @dsa, 'SetEncodedHash', @success OUT, 'hex', @hashStr
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        RETURN
      END

    -- Now that the DSA object contains both the private key and hash,
    -- it is ready to create the signature:
    EXEC sp_OAMethod @dsa, 'SignHash', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        RETURN
      END

    -- If SignHash is successful, the DSA object contains the
    -- signature.  It may be accessed as a hex or base64 encoded
    -- string.  (It is also possible to access directly in byte array form via
    -- the "Signature" property.)
    DECLARE @hexSig nvarchar(4000)
    EXEC sp_OAMethod @dsa, 'GetEncodedSignature', @hexSig OUT, 'hex'

    PRINT 'Signature:'

    PRINT @hexSig

    -- -----------------------------------------------------------
    -- Step 2: Verify the DSA Signature
    -- -----------------------------------------------------------

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

    -- Load the DSA public key to be used for verification:
    DECLARE @pemPublicKey nvarchar(4000)

    EXEC sp_OAMethod @dsa2, 'LoadText', @pemPublicKey OUT, 'dsa_pub.pem'
    EXEC sp_OAMethod @dsa2, 'FromPublicPem', @success OUT, @pemPublicKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        EXEC @hr = sp_OADestroy @dsa2
        RETURN
      END

    -- Load the hash to be verified against the signature.
    EXEC sp_OAMethod @dsa2, 'SetEncodedHash', @success OUT, 'hex', @hashStr
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        EXEC @hr = sp_OADestroy @dsa2
        RETURN
      END

    -- Load the signature:
    EXEC sp_OAMethod @dsa2, 'SetEncodedSignature', @success OUT, 'hex', @hexSig
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dsa
        EXEC @hr = sp_OADestroy @dsa2
        RETURN
      END

    -- Verify:
    EXEC sp_OAMethod @dsa2, 'Verify', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsa2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END
    ELSE
      BEGIN

        PRINT 'DSA Signature Verified!'
      END

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @dsa
    EXEC @hr = sp_OADestroy @dsa2


END
GO