(SQL Server) Sign PDF using Cloud Signature Consortium API

See more Signing in the Cloud Examples

Demonstrates how to sign a PDF using a CSC provider. (CSC is the acronym for Cloud Signature Consortium.) This example shows the how to sign a PDF using a particular instance of a CSC service providee JCC Payment Systems Ltd, a company based in Cyprus that provides payment processing services. To use a different CSC provider would only require the values of the JSON parameters, such as base URL, client ID, client secret, etc. to be modified.

The Cloud Signature Consortium (CSC) is an international group of organizations and technology providers dedicated to creating and promoting a standardized open API for cloud-based digital signatures. The goal of the consortium is to make remote, legally-binding digital signatures accessible through cloud-based services, ensuring compatibility and interoperability across different platforms, devices, and services.

The CSC develops and maintains an open standard for APIs that allow cloud-based digital signature solutions to be easily integrated into applications and platforms.

These APIs enable users to sign documents using cloud-based certificate authorities (CAs) and trust service providers (TSPs), ensuring legal compliance with digital signature regulations (such as eIDAS in the EU).

Note: This example requires Chilkat v10.0.0 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.

    DECLARE @pdf int
    -- Use "Chilkat_9_5_0.Pdf" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load a PDF to be signed.
    DECLARE @success int
    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/hello.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    -- Options for signing are specified in JSON.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    -- Indicate that we want an LTV-enabled signature
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'ltvOcsp', 1

    -- Define the appearance of the signature.
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'page', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.y', 'top'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.x', 'left'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.fontScale', '10.0'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[0]', 'Digitally signed by: cert_cn'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[1]', 'current_dt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[2]', 'This is an LTV-enabled signature.'

    -- --------------------------------------------------------------------
    -- Provide the information about the CSC service to be used for signing.
    DECLARE @jsonCsc int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonCsc OUT

    -- Set the "service" equal to "CSC" to tell Chilkat to use the Cloud Signature Consortium API standard for signing.
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'service', 'CSC'

    -- Provide the service's base URL, the user ID, client ID, and client secret.
    -- Note: This example requires the CSC service to support the 
    -- OAuth2 client credentials (oauth2code authType) method of authentication.

    -- This is the base URL for the JCC CSC service.  It should be changed to your CSC provider's base URL.
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'baseUrl', 'https://ras-test.jcc.com.cy/adss/service/ras/csc/v1/'

    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'userId', 'YOUR_USER_ID'
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'clientId', 'YOUR_CLIENT_ID'
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'clientSecret', 'YOUR_CLIENT_SECRET'

    -- If your user ID has multiple possible credential IDs, you can select one by 
    -- specifying the ID or a substring that must be contained within the ID.
    -- Otherwise, Chilkat will use the 1st credential ID returned via the "credentials/list" endpoint.
    -- ------------------------------------------------------------------------------------------------------------
    -- IMPORTANT: Remove this line if you don't have multiple credential IDs, or if you wish to simply use the 1st.
    -- ------------------------------------------------------------------------------------------------------------
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'useCredential', 'MY_CREDENTIAL_ID'

    -- The SetCloudSigner method will send HTTPS REST requests to the base URL
    -- to get information about the CSC service.
    -- Specifically, it will do the following internally:
    -- 1) Calls the "info" endpoint to get information about the remote service and the list of the API methods it supports.
    -- 2) Calls the "oauth2/token" endpoint to get the OAuth2 authorization token via client credentials (using the clientId and clientSecret).
    -- 3) Calls the "credentials/list" endpoint to get the list of credentials associated with the userId.
    -- 4) Calls the "credentials/info" endpoint to retrieve the credential and return the main identity information 
    --    and the public key certificate or the certificate chain associated to it.
    --    The Chilkat certificate object is loaded with the retrieved certificate.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @jsonCsc

    -- Tell the pdf object to use the certificate for signing.
    EXEC sp_OAMethod @pdf, 'SetSigningCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonCsc
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- This is where a 2FA means of authentication might occur, depending on the CSC provider.
    -- For example, in the case of JCC Payment Systems, an authorization request is sent to 
    -- the Ascertia GoSign app on your mobile device where you must authorize the request to sign.
    EXEC sp_OAMethod @pdf, 'SignPdf', @success OUT, @json, 'qa_output/hello_signed.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonCsc
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    PRINT 'The PDF has been successfully signed using a CSC cloud signing service.'

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonCsc
    EXEC @hr = sp_OADestroy @cert


END
GO