SQL Server

Examine KeyInfo Certificate in XML Signature

See more XML Digital Signatures Examples

This example loads signed XML and gets the signing certificate, assuming the certificate is contained in X509Certificate within the KeyInfo. 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 @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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

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