SQL Server
Decrypt a govtalk.gov.uk SOAP GovTalkMessage

Demonstrates how to decrypt the content contained in the XML of a GovTalkMessage SOAP response.
Chilkat SQL Server Downloads

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.

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