SQL Server

Load PEM ASN.1 and Convert to XML

See more ASN.1 Examples

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>

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.

    SELECT @success = 0

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

    -- 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
    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 @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
    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 @pem
    EXEC @hr = sp_OADestroy @asnRoot
    EXEC @hr = sp_OADestroy @xmlObj


END
GO