(SQL Server) Create CAdES p7m using Azure Key Vault to Sign in the Cloud

See more Signing in the Cloud Examples

Demonstrates how to create a CAdES p7m, using Azure Key Vault. The signing of the hash happens in the Cloud on Azure Key Vault. Everything else regarding the creation of CAdES happens locally within Chilkat.

Note: This example requires Chilkat v9.5.0.96 or greater.

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.

    -- Load the certificate used for signing.  The certificate's private key is stored in 
    -- the Azure Key Vault.
    -- However, we still need the certificate locally (without private key).
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/myCert.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Here's a screenshot of our certificate with private key on Azure Key Vault:
    

    -- To sign using the Azure Key Vault, 
    -- add the following lines of code to specify your authentication credentials,
    -- and the name of the certificate w/ private key on the Azure server to be used.
    DECLARE @jsonAzure int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jsonAzure OUT

    -- Set the "service" equal to "azure_keyvault" to tell Chilkat to use Azure Key Vault for signing.
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'service', 'azure_keyvault'
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'client_id', 'APP_ID'
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'client_secret', 'APP_PASSWORD'
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'tenant_id', 'TENANT_ID'
    -- In the above screenshot, our vault name is "kvchilkat".  You will use your vault name.
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'vault_name', 'VAULT_NAME'
    -- In the above screenshot, our cert name is "ChilkatTest1".  You will use your cert name.
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'cert_name', 'CERT_NAME'
    -- In the above screenshot, our cert version is "63b94a23389546ecbc8eb6208a1bef37".  You will use your cert version.
    EXEC sp_OAMethod @jsonAzure, 'UpdateString', @success OUT, 'cert_version', 'CERT_VERSION'
    EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @jsonAzure

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT

    EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonAzure
        EXEC @hr = sp_OADestroy @crypt
        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

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    DECLARE @signedAttrs int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @signedAttrs OUT

    EXEC sp_OAMethod @signedAttrs, 'UpdateInt', @success OUT, 'contentType', 1
    EXEC sp_OAMethod @signedAttrs, 'UpdateInt', @success OUT, 'signingTime', 1
    EXEC sp_OAMethod @signedAttrs, 'UpdateInt', @success OUT, 'messageDigest', 1
    EXEC sp_OAMethod @signedAttrs, 'UpdateInt', @success OUT, 'signingCertificateV2', 1
    EXEC sp_OAMethod @signedAttrs, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @crypt, 'SigningAttributes', @sTmp0

    -- You can sign any type of file..
    DECLARE @inputXmlPath nvarchar(4000)
    SELECT @inputXmlPath = 'qa_data/e-Invoice.xml'
    DECLARE @outputP7mPath nvarchar(4000)
    SELECT @outputP7mPath = 'qa_output/signed.p7m'

    -- Create the CAdES-BES attached signature, which contains the original data.
    -- Chilkat will build the .p7m locally, but will (internally) use ARSS
    -- to do the RSA signing remotely.
    EXEC sp_OAMethod @crypt, 'CreateP7M', @success OUT, @inputXmlPath, @outputP7mPath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonAzure
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @signedAttrs
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @jsonAzure
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @signedAttrs


END
GO