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

See more Signing in the Cloud Examples

Demonstrates how to digitally sign a PDF using the Aruba Remote Signing Service (ARSS). The example loads a local PDF and certificate, configures the ARSS cloud signer credentials, specifies the OTP authentication type with typeOtpAuth, and creates an LTV-enabled signed PDF where the private key remains protected on the Aruba signing server.

Note: This example requires Chilkat v11.5.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)
    DECLARE @success int
    SELECT @success = 0

    -- 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.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load the PDF that will be digitally signed.
    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

    -- Signing options are specified in a JSON object.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    -- Enable LTV (Long-Term Validation).
    -- When ltvOcsp is true, OCSP validation information is embedded in the PDF
    -- so that signature validation can continue to succeed in the future,
    -- even if the original OCSP responder is no longer available.
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'ltvOcsp', 1

    -- Specify the visual appearance of the signature on the PDF page.
    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'

    -- Text lines displayed in the visible signature appearance.
    -- Special values such as "cert_cn" and "current_dt" are replaced
    -- with the certificate common name and current date/time.
    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 signing certificate.
    -- 
    -- The private key is NOT stored locally.  Instead, the private key is
    -- stored and protected on the Aruba Remote Signing Service (ARSS).
    -- 
    -- Even though the signing operation will occur remotely, Chilkat still
    -- needs the corresponding public certificate locally so that it can
    -- construct the CMS/PAdES signature and embed the certificate chain
    -- in the signed PDF.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.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

    -- Configure Aruba Remote Signing Service (ARSS) credentials.
    -- 
    -- When SetCloudSigner is called, Chilkat is instructed to perform
    -- cryptographic signing operations through the ARSS web service.
    -- The PDF is assembled locally, but the actual RSA signature operation
    -- is performed remotely using the private key held by Aruba.
    DECLARE @jsonArss int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonArss OUT

    -- Required.  Indicates that the cloud signing provider is ARSS.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'service', 'ARSS'

    -- The ARSS certificate identifier (for example, "AS0").
    -- This identifies which remote certificate/private key pair should be used.
    -- The remote certificate should correspond to the certificate loaded above.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'certID', 'YOUR_ARSS_CERT_ID'

    -- OTP password associated with the Aruba remote-signing account.
    -- Depending on the ARSS configuration, an OTP may be required to
    -- authorize each signing operation.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'otpPwd', 'YOUR_OTP_PWD'

    -- Specifies the OTP authentication environment.
    -- 
    -- Common values are:
    --   "demoprod" - Demo/Test environment
    --   "prod"     - Production environment
    -- 
    -- This value is sent to the ARSS service and determines how the OTP
    -- authentication is validated.  The correct value depends on the type
    -- of Aruba account and environment that has been provisioned.
    -- 
    -- If signing fails with an authentication-related error, verify that
    -- the typeOtpAuth value matches the environment associated with the
    -- ARSS account credentials being used.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'typeOtpAuth', 'demoprod'

    -- ARSS account username.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'user', 'YOUR_ARSS_USERNAME'

    -- ARSS account password.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'userPWD', 'YOUR_ARSS_PASSWORD'

    -- Beginning with Chilkat v11.5.0, the ARSS endpoint can be explicitly
    -- specified.  This allows the application to target a particular
    -- Aruba signing service endpoint when required.
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'endpoint', 'https://app1.firma-remota.it/ArubaSignerService/webresources/signerservice'

    EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @jsonArss
    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
        EXEC @hr = sp_OADestroy @jsonArss
        RETURN
      END

    -- Associate the certificate with the PDF object.
    -- All subsequent signing operations will use this certificate.
    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

    -- Create the signed PDF.
    -- 
    -- Chilkat performs all PDF processing locally.  When the time comes
    -- to generate the cryptographic signature value, Chilkat sends the
    -- hash to ARSS, which signs it using the remote private key and returns
    -- the signature.  The private key never leaves the Aruba service.
    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