(SQL Server) eHealth.gov.be RequestSecurityToken

Request a security token for use with the eHealth.gov.be SOAP web services.

For more information, see https://www.ehealth.fgov.be/ehealthplatform/fr/data/file/view/bf25682e28c6ccd0d06768c73a467176f63791dd?name=STS%20-%20WS%20Trust%20-%20Cookbook%20v1.0%20dd%2020012023.pdf

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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int
    SELECT @success = 1

    -- 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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    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 <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Create the following XML to be signed..

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <soapenv:Envelope xmlns:ns="http://docs.oasis-open.org/ws-sx/ws-trust/200512" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    -- 	<soapenv:Header>
    -- 		<wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" 
    -- 		xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    -- 			<wsse:BinarySecurityToken EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary" 
    -- 			    ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3" 
    -- 			    wsu:Id="X509-4A13D668E59AAC4F3816750824965588">{organization certificate}</wsse:BinarySecurityToken>			
    -- 			<wsu:Timestamp wsu:Id="TS-4A13D668E59AAC4F3816750824965567">
    -- 				<wsu:Created>2023-02-01T12:42:11.156Z</wsu:Created>
    -- 				<wsu:Expires>2023-02-01T12:58:51.156Z</wsu:Expires>
    -- 			</wsu:Timestamp>
    -- 		</wsse:Security>
    -- 	</soapenv:Header>
    -- 	<soapenv:Body wsu:Id="id-4A13D668E59AAC4F38167508249655911" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    -- 		<wst:RequestSecurityToken Context="RC-302613de-a809-46b5-931a-0a55bfca5937" 
    -- 		    xmlns:auth="http://docs.oasis-open.org/wsfed/authorization/200706"
    -- 		    xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
    -- 		    xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing"
    -- 		    xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy"
    -- 		    xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
    -- 		    xmlns:wst="http://docs.oasis-open.org/ws-sx/ws-trust/200512">
    -- 			<wst:TokenType>http://docs.oasis-open.org/wss/oasis-wss-saml-token-profile-1.1#SAMLV1.1</wst:TokenType>
    -- 			<wst:RequestType>http://docs.oasis-open.org/ws-sx/ws-trust/200512/Issue</wst:RequestType>
    -- 			<wst:Claims Dialect="http://docs.oasis-open.org/wsfed/authorization/200706/authclaims">
    -- 				<auth:ClaimType Uri="urn:be:fgov:kbo-bce:organization:cbe-number">
    -- 					<auth:Value>{cbenumber}</auth:Value>
    -- 				</auth:ClaimType>
    -- 				<auth:ClaimType Uri="urn:be:fgov:ehealth:1.0:certificateholder:enterprise:cbe-number">
    -- 					<auth:Value>{cbenumber}</auth:Value>
    -- 				</auth:ClaimType>
    -- 			</wst:Claims>
    -- 			<wst:Lifetime>
    -- 				<wsu:Created>2023-02-01T08:30:10+02:00</wsu:Created>
    -- 				<wsu:Expires>2023-02-01T09:30:10+02:00</wsu:Expires>
    -- 			</wst:Lifetime>
    -- 			<wst:KeyType>http://docs.oasis-open.org/ws-sx/wstrust/200512/PublicKey</wst:KeyType>
    -- 		</wst:RequestSecurityToken>
    -- 	</soapenv:Body>
    -- </soapenv:Envelope>

    DECLARE @xmlToSign int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlToSign OUT

    EXEC sp_OASetProperty @xmlToSign, 'Tag', 'soapenv:Envelope'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:ns', 'http://docs.oasis-open.org/ws-sx/ws-trust/200512'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
    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-4A13D668E59AAC4F3816750824965588'

    DECLARE @bdCert int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    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-4A13D668E59AAC4F3816750824965567'

    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    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, 300
    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, -300

    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body', 1, 'wsu:Id', 'id-4A13D668E59AAC4F38167508249655911'
    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, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken', 1, 'Context', 'RC-302613de-a809-46b5-931a-0a55bfca5937'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken', 1, 'xmlns:auth', 'http://docs.oasis-open.org/wsfed/authorization/200706'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken', 1, 'xmlns:ds', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken', 1, 'xmlns:wsa', 'http://schemas.xmlsoap.org/ws/2004/08/addressing'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken', 1, 'xmlns:wsp', 'http://schemas.xmlsoap.org/ws/2004/09/policy'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken', 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:Body|wst:RequestSecurityToken', 1, 'xmlns:wst', 'http://docs.oasis-open.org/ws-sx/ws-trust/200512'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|wst:RequestSecurityToken|wst:TokenType', 'http://docs.oasis-open.org/wss/oasis-wss-saml-token-profile-1.1#SAMLV1.1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|wst:RequestSecurityToken|wst:RequestType', 'http://docs.oasis-open.org/ws-sx/ws-trust/200512/Issue'

    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken|wst:Claims', 1, 'Dialect', 'http://docs.oasis-open.org/wsfed/authorization/200706/authclaims'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|wst:RequestSecurityToken|wst:Claims|auth:ClaimType[1]', 1, 'Uri', 'urn:be:fgov:ehealth:1.0:certificateholder:person:ssin'

    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|wst:RequestSecurityToken|wst:KeyType', 'http://docs.oasis-open.org/ws-sx/wstrust/200512/PublicKey'

    DECLARE @gen int
    -- Use "Chilkat_9_5_0.XmlDSigGen" for versions of Chilkat < 10.0.0
    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-4A13D668E59AAC4F38167508249656212'
    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-4A13D668E59AAC4F3816750824965589'

    -- -------- Reference 1 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'TS-4A13D668E59AAC4F3816750824965567', 'sha256', 'EXCL_C14N', 'wsse soapenv urn urn1', ''

    -- -------- Reference 2 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'id-4A13D668E59AAC4F38167508249655911', 'sha256', 'EXCL_C14N', 'urn urn1', ''

    -- -------- Reference 3 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'X509-4A13D668E59AAC4F3816750824965588', '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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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-4A13D668E59AAC4F38167508249655810'
    EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'URI', '#X509-4A13D668E59AAC4F3816750824965588'
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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 <> 1
      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

    -- The sbXml is sent as the HTTP request body below..
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- -----------------------------------------------------------------------------------------------------------
    -- Send the SOAP requet to ask the server to issue a security token, which can then be used to access other SOAP services..
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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'

    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://services-acpt.ehealth.fgov.be/IAM/SecurityTokenService/v1/RequestSecurityToken'
    DECLARE @resp int
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, @url, @sTmp0, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 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

    DECLARE @responseStatus int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @responseStatus OUT

    PRINT 'Response Status Code = ' + @responseStatus

    -- You'll want to check to see if the response status code = 200.
    -- If not, then the response body contains error information instead of a security token.
    -- This example will assume we received 200 status code.

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    -- The response body contains XML like this:

    -- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    --     <SOAP-ENV:Header/>
    --     <SOAP-ENV:Body>
    --         <wst:RequestSecurityTokenResponse xmlns:wst="http://docs.oasis-open.org/ws-sx/ws-trust/200512" Context="RC-302613de-a809-46b5-931a-0a55bfca5937">
    --             <wst:RequestedSecurityToken>
    --                 <Assertion xmlns="urn:oasis:names:tc:SAML:1.0:assertion" AssertionID="_3e8ea5c951b2167c274974750ace9b5d" 
    -- ...
    --                 </Assertion>
    --             </wst:RequestedSecurityToken>
    --         </wst:RequestSecurityTokenResponse>
    --     </SOAP-ENV:Body>
    -- </SOAP-ENV:Envelope>

    -- The portion of the response from  <Assertion ..> ... </Assertion>  is the SAML security token to be included
    -- in a subsesquent SOAP request.  It is extremely important to not modify the contents of the security token in any way, including not changing
    -- whitespace or any formatting.  Therefore, we get the response body exactly as-is, to be used in a SOAP request.

    -- Copy the response body to a Chilkat BinData object.
    DECLARE @bdSecToken int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdSecToken OUT

    EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @bdSecToken

    EXEC @hr = sp_OADestroy @resp

    -- Let's save the bdSecToken to a file, and pick it up in the next example where it is used
    -- in a SOAP request, such as in this example:  AddressBook Search for Professionals

    EXEC sp_OAMethod @bdSecToken, 'WriteFile', @success OUT, 'qa_data/tokens/ehealth-fgov-be-sectoken.xml'


    PRINT 'OK'

    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 @bdSecToken


END
GO