SQL Server

Create CAdES (p7m) Signature using ARSS (Aruba Remote Signing Service)

See more Signing in the Cloud Examples

Demonstrates creating a CAdES (p7m) signature using the Aruba Remote Signing Service. SQL Server

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

    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

    -- 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.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'

    -- 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'

    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'user', 'YOUR_ARSS_USERNAME'
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'userPWD', 'YOUR_ARSS_PASSWORD'

    -- The "endpoint" can be specified starting in Chilkat v11.5.0
    EXEC sp_OAMethod @jsonArss, 'UpdateString', @success OUT, 'endpoint', 'https://app1.firma-remota.it/ArubaSignerService/webresources/signerservice'

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

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.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 @jsonArss
        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.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 @jsonArss
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @signedAttrs
        RETURN
      END


    PRINT 'Success.'

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


END
GO