(SQL Server) Examine KeyInfo Certificate in XML Signature

This example loads signed XML and gets the signing certificate, assuming the certificate is contained in X509Certificate within the KeyInfo.

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 example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @dsig int
    -- Use "Chilkat_9_5_0.XmlDSig" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @dsig OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @success int
    EXEC sp_OAMethod @sbXml, 'LoadFile', @success OUT, 'c:/aaworkarea/elias/3/face_f09006808443a699d1b.xml', 'utf-8'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load XML file.'
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    EXEC sp_OAMethod @dsig, 'LoadSignatureSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsig, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Get the KeyInfo XML.
    DECLARE @xmlKeyInfo int
    EXEC sp_OAMethod @dsig, 'GetKeyInfo', @xmlKeyInfo OUT
    EXEC sp_OAGetProperty @dsig, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsig, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    EXEC sp_OAMethod @xmlKeyInfo, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- Assuming the X509Certificate is in the KeyInfo, it will look like this:

    --   <ds:KeyInfo Id="...">
    --     <ds:KeyValue>
    --     ...  
    --     <ds:X509Data>
    --       <ds:X509Certificate>MIIHAz...</ds:X509Certificate>
    --     </ds:X509Data>
    --   </ds:KeyInfo>
    DECLARE @certBase64 nvarchar(4000)
    EXEC sp_OAMethod @xmlKeyInfo, 'GetChildContent', @certBase64 OUT, '*:X509Data|*:X509Certificate'
    EXEC sp_OAGetProperty @xmlKeyInfo, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'No X509Certificate found in the KeyInfo.'
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Load a certificate object w/ the base64.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromBase64', @success OUT, @certBase64
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Examine the cert..

    EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
    PRINT 'SubjectDN: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'IssuerDN', @sTmp0 OUT
    PRINT 'IssuerDN: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'SerialDecimal', @sTmp0 OUT
    PRINT 'SerialNumber as Decimal: ' + @sTmp0

    EXEC @hr = sp_OADestroy @xmlKeyInfo


    EXEC @hr = sp_OADestroy @dsig
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @cert


END
GO