(SQL Server) Load PEM ASN.1 and Convert to XML

Loads a CSR (Certificate Signing Request) from PEM and converts the ASN.1 to XML format. The purpose of this example is to compare the ASN.1 format with the corresponding XML. One means for accessing a particular piece of information within any ASN.1 document is to convert to XML, and then use any XML API to extract the required data.

The PEM used in this example may be downloaded here: Sample CSR PEM

It contains ASN.1 with this format:

After converting, the XML looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<sequence>
    <sequence>
        <int>00</int>
        <sequence>
            <set>
                <sequence>
                    <oid>2.5.4.6</oid>
                    <printable>US</printable>
                </sequence>
            </set>
            <set>
                <sequence>
                    <oid>2.5.4.8</oid>
                    <utf8>Utah</utf8>
                </sequence>
            </set>
            <set>
                <sequence>
                    <oid>2.5.4.7</oid>
                    <utf8>Lindon</utf8>
                </sequence>
            </set>
            <set>
                <sequence>
                    <oid>2.5.4.10</oid>
                    <utf8>DigiCert Inc.</utf8>
                </sequence>
            </set>
            <set>
                <sequence>
                    <oid>2.5.4.11</oid>
                    <utf8>DigiCert</utf8>
                </sequence>
            </set>
            <set>
                <sequence>
                    <oid>2.5.4.3</oid>
                    <utf8>example.digicert.com</utf8>
                </sequence>
            </set>
        </sequence>
        <sequence>
            <sequence>
                <oid>1.2.840.113549.1.1.1</oid>
                <null />
            </sequence>
            <bits n="2160">3082010A0282010... </bits>
        </sequence>
        <contextSpecific tag="0" constructed="1" />
    </sequence>
    <sequence>
        <oid>1.2.840.113549.1.1.5</oid>
        <null />
    </sequence>
    <bits n="2048">1D2472B15C7129850E6...  </bits>
</sequence>

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Starting in v9.5.0.49, all Chilkat classes can be unlocked at once at the beginning of a program
    -- by calling UnlockBundle.  It requires a Bundle unlock code.
    DECLARE @chilkatGlob int
    -- Use "Chilkat_9_5_0.Global" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Global', @chilkatGlob OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @chilkatGlob, 'UnlockBundle', @success OUT, 'Anything for 30-day trial.'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @chilkatGlob, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        RETURN
      END

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

    -- Load the PEM file.  The password is ignored because it is not an encrypted PEM.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'notRequired'
    EXEC sp_OAMethod @pem, 'LoadPemFile', @success OUT, 'qa_data/asn/sampleCsr.pem', @password

    -- Get the CSR in base64 format:
    DECLARE @itemType nvarchar(4000)
    SELECT @itemType = 'csr'
    DECLARE @itemSubType nvarchar(4000)
    SELECT @itemSubType = ''
    DECLARE @encoding nvarchar(4000)
    SELECT @encoding = 'base64'
    DECLARE @index int
    SELECT @index = 0
    DECLARE @csrBase64 nvarchar(4000)
    EXEC sp_OAMethod @pem, 'GetEncodedItem', @csrBase64 OUT, @itemType, @itemSubType, @encoding, @index

    -- Load the CSR base64 string into the ASN object
    DECLARE @asnRoot int
    -- Use "Chilkat_9_5_0.Asn" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Asn', @asnRoot OUT

    EXEC sp_OAMethod @asnRoot, 'LoadEncoded', @success OUT, @csrBase64, 'base64'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @asnRoot, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @asnRoot
        RETURN
      END

    -- Get the ASN.1 in (Chilkat's) XML format:
    DECLARE @asnXml nvarchar(4000)
    EXEC sp_OAMethod @asnRoot, 'AsnToXml', @asnXml OUT

    -- The XML returned by AsnToXml is one long difficult-to-read string 
    -- with no linebreaks.  It is meant to be compact.
    -- To get the XML in pretty, human-readable, indented form,
    -- load into the Chilkat XML object an then retrieve:
    DECLARE @xmlObj int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlObj OUT

    EXEC sp_OAMethod @xmlObj, 'LoadXml', @success OUT, @asnXml
    EXEC sp_OAMethod @xmlObj, 'GetXml', @asnXml OUT


    PRINT @asnXml

    EXEC @hr = sp_OADestroy @chilkatGlob
    EXEC @hr = sp_OADestroy @pem
    EXEC @hr = sp_OADestroy @asnRoot
    EXEC @hr = sp_OADestroy @xmlObj


END
GO