SQL Server

Decrypt a govtalk.gov.uk SOAP GovTalkMessage

See more Encryption Examples

Demonstrates how to decrypt the content contained in the XML of a GovTalkMessage SOAP response. 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
    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.

    --  The GovTalkMessage response looks something like this:

    --  <?xml version="1.0" encoding="utf-8"?>
    --  <GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
    --    <EnvelopeVersion>3.1</EnvelopeVersion>
    --    <Header>
    --      <MessageDetails>
    --        <Class>CSSZ_DZDPN</Class>
    --        <Qualifier>request</Qualifier>
    --        <Function>submit</Function>
    --        <TransactionID />
    --        <AuditID />
    --        <CorrelationID>aaaaa</CorrelationID>
    --        <ResponseEndPoint PollInterval="0" />
    --        <Transformation>XML</Transformation>
    --        <GatewayTest />
    --        <GatewayTimestamp />
    --      </MessageDetails>
    --      <SenderDetails>
    --        <IDAuthentication>
    --          <SenderID />
    --          <Authentication>
    --            <Method>clear</Method>
    --            <Role />
    --            <Value />
    --          </Authentication>
    --        </IDAuthentication>
    --        <X509Certificate />
    --        <EmailAddress>somebody@example.com</EmailAddress>
    --      </SenderDetails>
    --    </Header>
    --    <GovTalkDetails>
    --      <Keys>
    --        <Key Type="vars">9999999999</Key>
    --      </Keys>
    --      <GatewayAdditions>
    --        <Source>VREP</Source>
    --      </GatewayAdditions>
    --    </GovTalkDetails>
    --    <Body>
    --      <Message xmlns="http://www.cssz.cz/XMLSchema/envelope" version="1.2" eType="DZDPN20">
    --        <Header>
    --          <Signature xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">MIIJ0A ... UMw=
    --  </Signature>
    --          <Vendor productName="some product name" version="2019" />
    --        </Header>
    --        <Body xmlns:dt="urn:schemas-microsoft-com:datatypes" encrypted="yes" contentEncoding="gzip" dt:dt="bin.base64">MIIF2w ... N2vW</Body>
    --      </Message>
    --    </Body>
    --  </GovTalkMessage>

    --  We want to get the content of the Body and decrypt it.

    --  First, let's get the content of the Body XML element, which is a base64 string starting with MIIF2w...

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_data/xml/govTalkMessageResponse.xml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    DECLARE @Body nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @Body OUT, 'Body|Message|Body'

    PRINT @Body

    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/govTalkMessage_aaa.pfx', 'aaa'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'pki'
    EXEC sp_OAMethod @crypt, 'SetDecryptCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    --  Append the bytes to bd.
    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, @Body, 'base64'

    --  Decrypt in-place.
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @bd
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    --  Save the decrypted data to a file.
    EXEC sp_OAMethod @bd, 'WriteFile', @success OUT, 'qa_output/out.dat'

    --  If the decrypted data is non-text (binary) then we can examine it in an encoding, such as hex:

    EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'hex'
    PRINT 'Decrypted bytes as hex: ' + @sTmp0

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bd


END
GO