(SQL Server) CAdES BES Attached (Opaque) Signature

Demonstrates how to create a CAdES BES attached signature file (.p7m). This is a PKCS7 signature format (known as an opaque, or "attached" signature) where the file data is embedded within the signature file. The signature verification both verifies the signature and extracts the original data.

(A "detached" signature is where the original data is NOT included within the PKCS7 signature format.)

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

    -- Use a digital certificate and private key from a PFX file (.pfx or .p12).
    DECLARE @pfxPath nvarchar(4000)
    SELECT @pfxPath = 'qa_data/pfx/myCertAndPrivateKey.p12'
    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'password'

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

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, @pfxPath, @pfxPassword
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Tell the crypt component to use this cert.
    EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- The CadesEnabled property applies to all methods that create PKCS7 signatures. 
    -- To create a CAdES-BES signature, set this property equal to true. 
    EXEC sp_OASetProperty @crypt, 'CadesEnabled', 1

    -- To sign with SHA1, set the HashAlgorithm property to "sha1"
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha1'

    -- To sign with SHA256, set the HashAlgorithm property to "SHA256".
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    -- Other hash algorithm choices are "md5", "md2", "sha384", and "sha512"

    -- We can sign any type of file, creating a .p7m as output.
    -- The .p7m contains the signature and also embeds the data of the file that is signed.
    DECLARE @inFile nvarchar(4000)
    SELECT @inFile = 'qa_data/json/sample.json'
    DECLARE @sigFile nvarchar(4000)
    SELECT @sigFile = 'qa_output/signature.p7m'

    -- -----------------------------------------------------------------------------------------
    -- Also see Chilkat's online tool to examine a .p7m and generate code to duplicate the .p7m
    -- -----------------------------------------------------------------------------------------

    -- Create the CAdES-BES attached signature, which contains the original data.
    EXEC sp_OAMethod @crypt, 'CreateP7M', @success OUT, @inFile, @sigFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Verify the .p7m file and extract the original file from the .p7m. 
    DECLARE @extractedToFilePath nvarchar(4000)
    SELECT @extractedToFilePath = 'qa_output/sample.json'
    EXEC sp_OAMethod @crypt, 'VerifyP7M', @success OUT, @sigFile, @extractedToFilePath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    PRINT 'Success!'

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @cert


END
GO