(SQL Server) Generate a CSR with SAN (Subject Alternative Name) Extension

Demonstrates how to generate a private key and a Certificate Signing Request (CSR) that includes the SAN extension.

Note: This example requires Chilkat v9.5.0.84 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.84 or greater.

    -- 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
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    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 @success int
    EXEC sp_OAMethod @rsa, 'GenerateKey', @success OUT, 2048
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Get the private key
    DECLARE @privKey int
    EXEC sp_OAMethod @rsa, 'ExportPrivateKeyObj', @privKey OUT

    -- 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. 
    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 @privKey

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

    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:

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


END
GO