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.

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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  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
    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
    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'

    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
    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