(SQL Server) Sign XML (XAdES) in the Cloud using Azure Key Vault

See more Signing in the Cloud Examples

Demonstrates how to sign XML using Azure Key Vault. The signing of the hash happens in the Cloud on Azure Key Vault. Everything else involving the updating the XML to add the signature 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
    -- 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.

    -- Create the XML to be signed.
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    DECLARE @success int
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '<?xml version="1.0" encoding="UTF-8" standalone="no" ?>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    <SOAP-ENV:Header>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '        <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" SOAP-ENV:mustUnderstand="1"></wsse:Security>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    </SOAP-ENV:Header>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    <SOAP-ENV:Body xmlns:SOAP-SEC="http://schemas.xmlsoap.org/soap/security/2000-12" SOAP-SEC:id="Body">', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '        <z:FooBar xmlns:z="http://example.com" />', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    </SOAP-ENV:Body>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '</SOAP-ENV:Envelope>', @bCrlf

    -- 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
    -- 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, '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 @sbXml
        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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.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

    -- Prepare for signing...
    DECLARE @gen int
    -- Use "Chilkat_9_5_0.XmlDSigGen" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT

    -- Indicate where the Signature will be inserted.
    EXEC sp_OASetProperty @gen, 'SigLocation', 'SOAP-ENV:Envelope|SOAP-ENV:Header|wsse:Security'

    -- Add a reference to the fragment of the XML to be signed.

    -- Note: "Body" refers to the XML element having an "id" equal to "Body", where "id" is case insensitive
    -- and where any namespace might qualify the attribute.  In this case, the SOAP-ENV:Body fragment is signed
    -- NOT because the tag = "Body", but because it has SOAP-SEC:id="Body"
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'Body', 'sha1', 'EXCL_C14N', '', ''

    -- (You can read about the SignedInfoPrefixList in the online reference documentation.  It's optional..)
    EXEC sp_OASetProperty @gen, 'SignedInfoPrefixList', 'wsse SOAP-ENV'

    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
    EXEC sp_OASetProperty @gen, 'X509Type', 'IssuerSerial'

    DECLARE @bUsePrivateKey int
    SELECT @bUsePrivateKey = 1
    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, @bUsePrivateKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonAzure
        EXEC @hr = sp_OADestroy @gen
        RETURN
      END
    -- Everything's specified.  Now create and insert the Signature
    -- (The actual signing of the hash will happen on the Aruba server.)
    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonAzure
        EXEC @hr = sp_OADestroy @gen
        RETURN
      END

    -- Examine the XML with the digital signature inserted
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- --------------------------------------------------------------------------------
    -- Also see Chilkat's Online WSDL Code Generator
    -- to generate code and SOAP Request and Response XML for each operation in a WSDL.
    -- --------------------------------------------------------------------------------

    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @jsonAzure
    EXEC @hr = sp_OADestroy @gen


END
GO