(SQL Server) Load a CSR and Examine the Contents

Demonstrates how to load a Certificate Signing Request (CSR) and examine the contents.

Note: This example requires Chilkat v9.5.0.65 or greater.

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)
    -- Note: Requires Chilkat v9.5.0.65 or greater.

    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    -- Load a CSR PEM file.  The file should contain content that looks like this:
    -- 
    -- 	-----BEGIN CERTIFICATE REQUEST-----
    -- 	MIIC6jCCAdICAQAwgaQxITAfBgNVBAMMGG15c3ViZG9tYWluLm15ZG9tYWluLmNv
    -- 	bTELMAkGA1UEBhMCR0IxDjAMBgNVBAgMBVlvcmtzMQ0wCwYDVQQHDARZb3JrMSEw
    -- 	HwYDVQQKDBhJbnRlcm5ldCBXaWRnaXRzIFB0eSBMdGQxCzAJBgNVBAsMAklUMSMw
    -- 	IQYJKoZIhvcNAQkBFhRzdXBwb3J0QG15ZG9tYWluLmNvbTCCASIwDQYJKoZIhvcN
    -- 	AQEBBQADggEPADCCAQoCggEBALnQ0un/wF8whk+gPuiAlf3qvx14jgAOV6Erm6EB
    -- 	H7WACPCpnKcm/8KP+7uoPiwRQaENhMeCgf45vcivl2p6aAn/spLXyEkXyw2d8wFb
    -- 	YYAGRkiz4Xf7ASJiKuwcOtORz+sSDzgtdfokHfXU1cYeFE2yQhSdLUY5fMn425+g
    -- 	KoEEsRSjSDe6AKru4+4iGNrLKd8pB9IA5/jOE139IkWlB9r5fEPD5bUTsgqXk9eb
    -- 	68O0gc712V2eZK07N24lDmFC4bIMTD4csDWocR5hFHXj7NX7c8sOBDcpEb9mPIk4
    -- 	elxubnhkfnjhOi4J3lDHcT/0ALnbLhf9LnaiKqs+5VcVZvECAwEAAaAAMA0GCSqG
    -- 	SIb3DQEBBQUAA4IBAQC0AETLIcP3foh5nbu2hVFS8uCUNZ5hEIR1eXmYZmZoBQq2
    -- 	26ZAoT4CZwixlggC+n7WvAXJ5Pzxpl4wLV4loTiQzaKPX1w0ERo5ZRwLy0n56oG2
    -- 	6QG+WTViT1C8rlgtVwkCFNOXr0kSSRs8FdaPllqKxK1hxYSL7zwNpumsk39F2cDt
    -- 	vhcekvH0V3BuGrQFm3dKN/0azW6GOod9+Vq4VzSyOe3kp15oxLBsZOFOu/REujcw
    -- 	Tzu2jt1asQKUm60CZ9wNHpYepR0Ww40uP1slbehEaFDa6V8b60/tlHHmBbJ4/fy5
    -- 	hJnYCvjzFz4O9VtT+JtP9ldRHWV3KpZ8ne3AjD+F
    -- 	-----END CERTIFICATE REQUEST-----

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

    DECLARE @csrPem nvarchar(4000)
    EXEC sp_OAMethod @fac, 'ReadEntireTextFile', @csrPem OUT, 'qa_data/csr/csr1.pem', 'utf-8'

    DECLARE @success int
    EXEC sp_OAMethod @csr, 'LoadCsrPem', @success OUT, @csrPem
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @csr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    -- Examine the contents.
    -- If the CSR does not contain a field, the property will
    -- contain an empty string.


    EXEC sp_OAGetProperty @csr, 'CommonName', @sTmp0 OUT
    PRINT 'Common Name: ' + @sTmp0

    -- Country Name (2 letter code)

    EXEC sp_OAGetProperty @csr, 'Country', @sTmp0 OUT
    PRINT 'Country: ' + @sTmp0

    -- State or Province Name (full name)

    EXEC sp_OAGetProperty @csr, 'State', @sTmp0 OUT
    PRINT 'State: ' + @sTmp0

    -- Locality Name (eg, city)

    EXEC sp_OAGetProperty @csr, 'Locality', @sTmp0 OUT
    PRINT 'Locality: ' + @sTmp0

    -- Organization Name (eg, company)

    EXEC sp_OAGetProperty @csr, 'Company', @sTmp0 OUT
    PRINT 'Company: ' + @sTmp0

    -- Organizational Unit Name (eg, secion/division)

    EXEC sp_OAGetProperty @csr, 'CompanyDivision', @sTmp0 OUT
    PRINT 'Company Division: ' + @sTmp0


    EXEC sp_OAGetProperty @csr, 'EmailAddress', @sTmp0 OUT
    PRINT 'Email Address: ' + @sTmp0

    EXEC @hr = sp_OADestroy @csr
    EXEC @hr = sp_OADestroy @fac


END
GO