(SQL Server) Get Certificate Distinguished Name in .NET Format

See more Certificates Examples

Certificate distinguished names (DN's), in the real world, can be constructed in various ways. Some systems expect certain ways of doing things. This example demonstrates how to get a DN (or SimpleName) exactly as .NET would return it.

This example requires Chilkat v9.5.0.96.

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)
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/testCert.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- In 99% of all cases, Chilkat already returns the Distinguished Name in the identical format as .NET.
    -- 
    -- In most cases, (internal to the certificate) the ASN.1 for the certificate's Subject 
    -- contains a sesquence of OIDs and values, where each OID is the single item in a SET.
    -- For example:

    -- SEQUENCE (3 elem)
    --       SET (1 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.6 countryName (X.520 DN component)
    --           PrintableString FR
    --       SET (1 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.12 title (X.520 DN component)
    --           UTF8String M�decin
    --       SET (3 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.42 givenName (X.520 DN component)
    --           UTF8String ALAIN
    --       SET (3 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.4 surname (X.520 DN component)
    --           UTF8String DUBOIS RPPS
    --       SET (3 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.3 commonName (X.520 DN component)
    --           UTF8String 389900098933

    -- If we look at the DN, we get this:
    DECLARE @subjectDN nvarchar(4000)
    EXEC sp_OAGetProperty @cert, 'SubjectDN', @subjectDN OUT

    PRINT @subjectDN

    -- Chilkat produces this:
    -- C=FR, Title=M�decin, G=ALAIN, SN=DUBOIS RPPS, CN=389900098933

    -- However, if the certificate's internal ASN.1 groups several items in a single SET,
    -- such as shown below, Microsoft .NET will produce a DN like this:
    -- G=ALAIN + SN=DUBOIS RPPS + CN=389900098933, T=M�decin, C=FR

    -- Notice how the items in the SET are grouped with " + ".
    -- Also notice the order is reverse, and "T" is used instead of using "Title.
    -- These are all variations of how a DN might be formatted.
    -- In practice, there is no correct way because what is needed depends on what is
    -- required as input to whatever is receiving the DN.

    -- SEQUENCE (3 elem)
    --       SET (1 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.6 countryName (X.520 DN component)
    --           PrintableString FR
    --       SET (1 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.12 title (X.520 DN component)
    --           UTF8String M�decin
    --       SET (3 elem)
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.42 givenName (X.520 DN component)
    --           UTF8String ALAIN
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.4 surname (X.520 DN component)
    --           UTF8String DUBOIS RPPS
    --         SEQUENCE (2 elem)
    --           OBJECT IDENTIFIER 2.5.4.3 commonName (X.520 DN component)
    --           UTF8String 389900098933

    -- To ensure Chilkat produces the same DN format as .NET, you can specify
    -- the keyword "DotNetSimpleName" in the UncommonOptions.
    -- For example:

    EXEC sp_OASetProperty @cert, 'UncommonOptions', 'DotNetSimpleName'

    -- If the above option is used (which was added in Chilkat v9.5.0.96), then Chilkat
    -- will produce the DN in the same format as .NET

    EXEC sp_OAGetProperty @cert, 'SubjectDN', @subjectDN OUT

    PRINT @subjectDN

    -- Now Chilkat produces this:
    -- G=ALAIN + SN=DUBOIS RPPS + CN=389900098933, T=M�decin, C=FR

    EXEC @hr = sp_OADestroy @cert


END
GO