(SQL Server) Generate CSR from Pre-Existing Private Key

Demonstrates how to generate a Certificate Signing Request (CSR) from a pre-existing RSA private key.

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

    -- Load the private key from a file.
    -- (Chilkat provides the ability to load private keys from many different
    -- formats and sources..)
    DECLARE @success int
    EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, 'qa_data/rsa/privKey1.pem', 'password'
    IF Not @success
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Create the CSR object and set properties.
    DECLARE @csr int
    -- Use "Chilkat_9_5_0.Csr" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Csr', @csr OUT

    -- Specify the Common Name.  This is the only required property.
    -- For SSL/TLS certificates, this would be the domain name. 
    -- For email certificates this would be the email address. 
    EXEC sp_OASetProperty @csr, 'CommonName', 'mysubdomain.mydomain.com'

    -- Country Name (2 letter code)
    EXEC sp_OASetProperty @csr, 'Country', 'GB'

    -- State or Province Name (full name)
    EXEC sp_OASetProperty @csr, 'State', 'Yorks'

    -- Locality Name (eg, city)
    EXEC sp_OASetProperty @csr, 'Locality', 'York'

    -- Organization Name (eg, company)
    EXEC sp_OASetProperty @csr, 'Company', 'Internet Widgits Pty Ltd'

    -- Organizational Unit Name (eg, secion/division)
    EXEC sp_OASetProperty @csr, 'CompanyDivision', 'IT'

    -- Email address
    EXEC sp_OASetProperty @csr, 'EmailAddress', 'support@mydomain.com'

    -- Create the CSR using the private key.
    DECLARE @pemStr nvarchar(4000)
    EXEC sp_OAMethod @csr, 'GenCsrPem', @pemStr OUT, @privKey
    EXEC sp_OAGetProperty @csr, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @csr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @csr
        RETURN
      END

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

    EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'qa_output/csr1.pem', @pemStr, 'utf-8', 0

    -- Show the CSR.

    PRINT @pemStr

    -- Sample output:
    -- The CSR PEM can be checked here:
    -- https://www.networking4all.com/en/support/tools/csr+check/
    -- Copy-and-paste the PEM into the online CSR Decoding / CSR Verification form

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

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


END
GO