SQL Server
SQL Server
XAdES XML Signature using ARSS (Aruba Remote Signing Service)
See more Signing in the Cloud Examples
Demonstrates how to sign XML using ARSS (Aruba Remote Signing Service).Chilkat SQL Server Downloads
-- 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.
-- --------------------------------------------------------------------------------
-- Also see Chilkat's Online WSDL Code Generator
-- to generate code and SOAP Request and Response XML for each operation in a WSDL.
-- --------------------------------------------------------------------------------
-- Create the XML to be signed.
DECLARE @sbXml int
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
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
-- Specify the signing certificate and the ARSS credentials
-- 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
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
-- 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
-- Prepare for signing...
DECLARE @gen int
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 = 0
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @jsonArss
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 = 0
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @jsonArss
EXEC @hr = sp_OADestroy @gen
RETURN
END
-- Examine the XML with the digital signature inserted
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @jsonArss
EXEC @hr = sp_OADestroy @gen
END
GO