SQL Server Requires Chilkat v11.0.0+

Generate a CSR with SAN (Subject Alternative Name) Extension

See more CSR Examples

Demonstrates how to generate a private key and a Certificate Signing Request (CSR) that includes the SAN extension. 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 @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

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

    --  First generate an RSA private key.
    --  (It is also possible to create CSRs based on ECDSA private keys..)
    DECLARE @rsa int
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Generate a random 2048-bit RSA key.
    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @rsa, 'GenKey', @success OUT, 2048, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    --  Create the CSR object and set properties.
    DECLARE @csr int
    EXEC @hr = sp_OACreate 'Chilkat.Csr', @csr OUT

    --  Specify the Common Name. 
    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'

    --  Add Subject Alternative Names
    --  (The AddSan method is added in Chilkat v9.5.0.84)
    --  Call AddSan for each alternative name.
    EXEC sp_OAMethod @csr, 'AddSan', @success OUT, 'dnsName', 'mydomain.com'
    EXEC sp_OAMethod @csr, 'AddSan', @success OUT, 'dnsName', 'mysubdomain.mydomain.com'
    EXEC sp_OAMethod @csr, 'AddSan', @success OUT, 'ipAddress', '192.168.0.123'

    --  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 @rsa
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @csr
        RETURN
      END

    --  Save the private key and CSR to a files.
    EXEC sp_OAMethod @privKey, 'SavePkcs8EncryptedPemFile', @success OUT, 'password', 'qa_output/privKey1.pem'

    DECLARE @fac int
    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:

    --  	-----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 @rsa
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @csr
    EXEC @hr = sp_OADestroy @fac


END
GO