SQL Server
SQL Server
Belgium eHealth Platform - checkAccessControl
See more Belgian eHealth Platform Examples
Demonstrates the checkAccessControl operation of PlatformIntegrationConsumerTest, which requires an X.509 certificate and signature. This tests the validity of your certificate and signature.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
DECLARE @iTmp0 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.
-- Provide a certificate + private key.
-- Note: If your certificate + private key is located on a hardware token or smartcard, you can call a different function to load from smartcard..
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, 'LoadPfxFile', @success OUT, 'SSIN=12345678.acc.p12', 'p12_password'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
RETURN
END
-- Create the XML to be signed...
DECLARE @xmlToSign int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlToSign OUT
EXEC sp_OASetProperty @xmlToSign, 'Tag', 'soapenv:Envelope'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:urn', 'urn:be:fgov:ehealth:platformintegrationconsumertest:v1'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:urn1', 'urn:be:fgov:ehealth:platformintegrationconsumertest:types:v1'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsse', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'EncodingType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'ValueType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'wsu:Id', 'X509-FC77E2C72083DA8E0F16711753508182856'
-- ---------------------------------------------------------------------------------------------------------------
-- A note about the Id's, such as X509-FC77E2C72083DA8E0F16711753508182856, TS-FC77E2C72083DA8E0F16711753508042855, etc.
-- These Id's simply need to be unique within the XML document. You don't need to generate new Id's every time.
-- You can use the same Id's in each XML document that is submitted. The purpose of each Id is to
-- match the XMLDsig Reference to the element in XML being referenced.
-- In other words, you could use the Id's "mickey_mouse", "donald_duck", and "goofy", and it would work perfectly OK,
-- as long as no other XML elements also use the Id's "mickey_mouse", "donald_duck", or "goofy"
-- ---------------------------------------------------------------------------------------------------------------
DECLARE @bdCert int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdCert OUT
EXEC sp_OAMethod @cert, 'ExportCertDerBd', @success OUT, @bdCert
EXEC sp_OAMethod @bdCert, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', @sTmp0
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsu:Timestamp', 1, 'wsu:Id', 'TS-FC77E2C72083DA8E0F16711753508042855'
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsu:Timestamp|wsu:Created', @sTmp0
EXEC sp_OAMethod @dt, 'AddSeconds', @success OUT, 3600
EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsu:Timestamp|wsu:Expires', @sTmp0
EXEC sp_OAMethod @dt, 'AddSeconds', @success OUT, -3600
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body', 1, 'wsu:Id', 'id-FC77E2C72083DA8E0F16711753508182859'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|urn:CheckAccessControlRequest|urn1:Message', 'Hello World'
-- Create a timestamp with the current date/time in the following format: 2014-12-30T15:29:03.157+01:00
EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 1
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|urn:CheckAccessControlRequest|urn1:Timestamp', @sTmp0
DECLARE @gen int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT
EXEC sp_OASetProperty @gen, 'SigLocation', 'soapenv:Envelope|soapenv:Header|wsse:Security|wsse:BinarySecurityToken'
EXEC sp_OASetProperty @gen, 'SigLocationMod', 1
EXEC sp_OASetProperty @gen, 'SigId', 'SIG-FC77E2C72083DA8E0F16711753508252860'
EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
EXEC sp_OASetProperty @gen, 'SignedInfoPrefixList', 'soapenv urn urn1'
EXEC sp_OASetProperty @gen, 'IncNamespacePrefix', 'ec'
EXEC sp_OASetProperty @gen, 'IncNamespaceUri', 'http://www.w3.org/2001/10/xml-exc-c14n#'
EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'EXCL_C14N'
EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'
-- Set the KeyInfoId before adding references..
EXEC sp_OASetProperty @gen, 'KeyInfoId', 'KI-FC77E2C72083DA8E0F16711753508182857'
-- -------- Reference 1 --------
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'TS-FC77E2C72083DA8E0F16711753508042855', 'sha256', 'EXCL_C14N', 'wsse soapenv urn urn1', ''
-- -------- Reference 2 --------
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'id-FC77E2C72083DA8E0F16711753508182859', 'sha256', 'EXCL_C14N', 'urn urn1', ''
-- -------- Reference 3 --------
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'X509-FC77E2C72083DA8E0F16711753508182856', 'sha256', 'EXCL_C14N', '_EMPTY_', ''
EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1
EXEC sp_OASetProperty @gen, 'KeyInfoType', 'Custom'
-- Create the custom KeyInfo XML..
DECLARE @xmlCustomKeyInfo int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlCustomKeyInfo OUT
EXEC sp_OASetProperty @xmlCustomKeyInfo, 'Tag', 'wsse:SecurityTokenReference'
EXEC sp_OAMethod @xmlCustomKeyInfo, 'AddAttribute', @success OUT, 'wsu:Id', 'STR-FC77E2C72083DA8E0F16711753508182858'
EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'URI', '#X509-FC77E2C72083DA8E0F16711753508182856'
EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'ValueType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3'
EXEC sp_OASetProperty @xmlCustomKeyInfo, 'EmitXmlDecl', 0
EXEC sp_OAMethod @xmlCustomKeyInfo, 'GetXml', @sTmp0 OUT
EXEC sp_OASetProperty @gen, 'CustomKeyInfoXml', @sTmp0
-- Load XML to be signed...
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml
EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'
-- Sign the XML...
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 @cert
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @bdCert
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
EXEC @hr = sp_OADestroy @sbXml
RETURN
END
-- -----------------------------------------------
-- Send the signed XML...
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @bdCert
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
RETURN
END
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'text/xml'
-- Change to services.ehealth.fgov.be for the production environment.
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSb', @success OUT, 'POST', 'https://services-acpt.ehealth.fgov.be/PlatformIntegrationConsumerTest/v1', @sbXml, 'utf-8', 'application/xml', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @bdCert
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
-- A successful response is a 200 status code, with this sample response:
-- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
-- <soapenv:Header xmlns:v1="urn:be:fgov:ehealth:platformintegrationconsumertest:v1" xmlns:v11="urn:be:fgov:ehealth:platformintegrationconsumertest:types:v1"/>
-- <soapenv:Body xmlns:ic="urn:be:fgov:ehealth:platformintegrationconsumertest:v1" xmlns:type="urn:be:fgov:ehealth:platformintegrationconsumertest:types:v1">
-- <ic:CheckAccessControlResponse>
-- <type:Message>Hello World</type:Message>
-- <type:Timestamp>2023-09-28T22:17:26.643+02:00</type:Timestamp>
-- <type:AuthenticatedConsumer>CN="SSIN=aaaaaa", OU=eHealth-platform Belgium, OU=bbbb, OU="SSIN=aaaaaaa", O=Federal Government, C=BE</type:AuthenticatedConsumer>
-- </ic:CheckAccessControlResponse>
-- </soapenv:Body>
-- </soapenv:Envelope>
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @bdCert
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
END
GO