(SQL Server) Load a CSR and Get the SANs (Subject Alternative Names)

See more CSR Examples

Demonstrates how to load a Certificate Signing Request (CSR) and get the Subject Alternative Names (if any).

Note: This example requires Chilkat v9.5.0.91 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.91 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-----
    -- MIIDnzCCAocCAQAwgZ4xFDASBgNVBAMMC2V4YW1wbGUuY29tMRQwEgYDVQQHDAtM
    -- b3MgQW5nZWxlczETMBEGA1UECAwKQ2FsaWZvcm5pYTEUMBIGA1UECgwLRXhhbXBs
    -- ZSBJbmMxFDASBgNVBAsMC1NTTCBTdXBwb3J0MSIwIAYJKoZIhvcNAQkBFhNleGFt
    -- cGxlQGV4YW1wbGUuY29tMQswCQYDVQQGEwJVUzCCASIwDQYJKoZIhvcNAQEBBQAD
    -- ggEPADCCAQoCggEBALhIT/q7y1xwpYaR0Coplktwn7RGaJpEBCgbJWxOVPTGL540
    -- erlhc6MrSEr6fo/HNtAbn6MNWm5F0MwjEU7H1Jxc7DYav+ps3hyhGaK9jalE99Sm
    -- yFOzKMLDPyWTPpxfT4QBO4rpWxMT2lgixDkZlaNqmC6pUGXArfH+H1F3C0gtH4ZU
    -- XN/Ipyk6r6ozWnJvIefdMe1JmeMD3RSoVcXd9bNWSpiD4uJnV+2pewkkNzhcGzBH
    -- MGi4ZjIbpPiYLlri/79fPb6u7wYkYLzCWRKzq6EaJkqY6OQgE33SC1GQKjC/fFRx
    -- wogub1s9cRjfL6vdqWbTJ5xhOkPeBO3UzHBHIksCAwEAAaCBujCBtwYJKoZIhvcN
    -- AQkOMYGpMIGmMAkGA1UdEwQCMAAwCwYDVR0PBAQDAgWgMB0GA1UdJQQWMBQGCCsG
    -- AQUFBwMBBggrBgEFBQcDAjBtBgNVHREEZjBkggtleGFtcGxlLmNvbYIPd3d3LmV4
    -- YW1wbGUuY29tgg9mdHAuZXhhbXBsZS5jb22CD3N2bi5leGFtcGxlLmNvbYIQc210
    -- cC5leGFtcGxlLmNvbYIQaW1hcC5leGFtcGxlLmNvbTANBgkqhkiG9w0BAQsFAAOC
    -- AQEAsIEDMlZViEVtuQWpfa6vUl5RfqQrop+hh9l4UmEOnERLEzyVO+9Kn7jNxyOR
    -- Gg7O9H4BYTfet6YwfpawbnQ1HBexduRZaUnET8RiYgKch0vWr/HbP7jvc23XUz2P
    -- NOAiet/tyTU6U5xJn0H4q1VFIbHjntpBSL+n+aiv8glIUepG8BwTu+vBe2/kVzKG
    -- ro2tudhbpEJYOwurlHCoubhCah9ryIkDeYEnycsrB6VnaxeGK0/5S2JBQo22/F9s
    -- T+q0Gee936ISvypLwNoIX+YFAjw2KnqucUM3RKHAAheJin5WNIQXdHHKGsImapun
    -- VqmPwLN6Kgm+kFVS0H5eyULSnA==
    -- -----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/myCsr.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

    -- Get the SANs
    DECLARE @st int
    -- Use "Chilkat_9_5_0.StringTable" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @st OUT

    EXEC sp_OAMethod @csr, 'GetSans', @success OUT, @st
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @csr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @st
        RETURN
      END

    DECLARE @i int
    SELECT @i = 0
    DECLARE @numSans int
    EXEC sp_OAGetProperty @st, 'Count', @numSans OUT
    WHILE @i < @numSans
      BEGIN
        EXEC sp_OAMethod @st, 'StringAt', @sTmp0 OUT, @i
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

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


END
GO