(SQL Server) Sign PDF using ARSS (Aruba Remote Signing Service)

See more Signing in the Cloud Examples

Demonstrates how to sign a PDF using the Aruba Remote Signing Service (ARSS).

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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @pdf int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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.'

    -- Load the certificate used for signing.  The certificate's private key is stored on 
    -- the Aruba.it server and the signing will happen remotely using the
    -- ARSS (Aruba Remote Signing Service).
    -- However, we still need the certificate locally (without private key).
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT

    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 @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- To sign using the Aruba Remote Signing Service, 
    -- add the following lines of code to specify your authentication credentials,
    -- and the ID of the certificate w/ private key on the server to be used.
    DECLARE @jsonArss int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jsonArss OUT

    -- Set the "service" equal to "ARSS" to tell Chilkat to use ARSS for signing.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'service', 'ARSS'
    -- Specify the certificate ID, such as "AS0"
    -- This certificate should match the certificate loaded in the above code.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'certID', 'YOUR_ARSS_CERT_ID'
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'otpPwd', 'YOUR_OTP_PWD'
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'typeOtpAuth', 'demoprod'
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'user', 'YOUR_ARSS_USERNAME'
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'userPWD', 'YOUR_ARSS_PASSWORD'
    EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @jsonArss

    -- 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 @cert
        EXEC @hr = sp_OADestroy @jsonArss
        RETURN
      END

    -- Chilkat will build the signed PDF locally, but will (internally) use ARSS
    -- to do the RSA sign hash operation.
    EXEC sp_OAMethod @pdf, 'SignPdf', @success OUT, @json, 'qa_output/hello_ltv_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 @cert
        EXEC @hr = sp_OADestroy @jsonArss
        RETURN
      END


    PRINT 'The PDF has been successfully cryptographically signed with long-term validation.'

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


END
GO