(SQL Server) Get Certificate As XML (and gets Signature Algorithm Identifier)

Demonstrates how to convert a certificate to XML such that the contents can easily be identified and accessed. Typically, the Chilkat Cert API provides properties and methods to access the most commonly needed pieces of information from a certificate. If a bit of information is not readily available then it can likely be retrieved from the XML representation. One such case is the certificate's signature algorithm identifier.

The basic internal format of a certificate is as follows (from section 4.1 of RFC 5280).

   Certificate  ::=  SEQUENCE  {
        tbsCertificate       TBSCertificate,
        signatureAlgorithm   AlgorithmIdentifier,
        signatureValue       BIT STRING  }

   TBSCertificate  ::=  SEQUENCE  {
        version         [0]  EXPLICIT Version DEFAULT v1,
        serialNumber         CertificateSerialNumber,
        signature            AlgorithmIdentifier,
        issuer               Name,
        validity             Validity,
        subject              Name,
        subjectPublicKeyInfo SubjectPublicKeyInfo,
        issuerUniqueID  [1]  IMPLICIT UniqueIdentifier OPTIONAL,
        subjectUniqueID [2]  IMPLICIT UniqueIdentifier OPTIONAL,
                             -- If present, version MUST be v2 or v3
        extensions      [3]  EXPLICIT Extensions OPTIONAL
                             -- If present, version MUST be v3
        }

Chilkat does not expect you to fully understand this notation and structure, but you can see that at a high level, the certificate is a sequence of three things, one after the other (a TBSCertifciate, an AlgorithmIdentifier, and a BIT STRING that is the signature). In XML format, it will look like this:

<sequence>
    <sequence>
        TBSCertificate goes here...
    </sequence>
    <sequence>
        <oid>1.2.840.113549.1.1.11</oid>
        <null />
    </sequence>
    <bits n="2048">3D20E895... 8DA307EC</bits>
</sequence>

The "oid" specifies the signature algorithm (such as sha256RSA), but it is an OID. In this case, the OID "1.2.840.113549.1.1.11" indicates "sha256WithRSAEncryption". (Google "1.2.840.113549.1.1.11" and you will find the 1st search result is http://www.alvestrand.no/objectid/1.2.840.113549.1.1.11.html. You can lookup any OID this way, to see what it means.)

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

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

    DECLARE @useTls int
    SELECT @useTls = 1
    DECLARE @maxWaitMillisec int
    SELECT @maxWaitMillisec = 20000
    DECLARE @tlsServerHost nvarchar(4000)
    SELECT @tlsServerHost = 'www.google.com'

    DECLARE @success int
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, @tlsServerHost, 443, @useTls, @maxWaitMillisec
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    DECLARE @cert int
    EXEC sp_OAMethod @socket, 'GetSslServerCert', @cert OUT
    EXEC sp_OAGetProperty @socket, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Close the connection with the server
    -- Wait a max of 20 millsec for a clean TLS shutdown.  
    EXEC sp_OAMethod @socket, 'Close', @success OUT, 20

    -- Examine the server cert in XML format
    DECLARE @certXml nvarchar(4000)
    EXEC sp_OAMethod @cert, 'ExportCertXml', @certXml OUT

    PRINT @certXml

    -- Let's parse the XML to get the signature algorithm identifier.
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @certXml

    -- The 2nd child is at index 1.
    DECLARE @algId int
    EXEC sp_OAMethod @xml, 'GetChild', @algId OUT, 1
    DECLARE @oid nvarchar(4000)
    EXEC sp_OAMethod @algId, 'GetChildContent', @oid OUT, 'oid'

    PRINT 'Signature Algorithm OID = ' + @oid

    EXEC @hr = sp_OADestroy @algId

    EXEC @hr = sp_OADestroy @cert


    EXEC @hr = sp_OADestroy @socket
    EXEC @hr = sp_OADestroy @xml


END
GO