(SQL Server) Generate a CSR containing an Extension Request

See more CSR Examples

Demonstrates how to generate a CSR containing a 1.2.840.113549.1.9.14 extensionRequest.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example will generate a secp256r1 ECDSA key for the CSR.
    DECLARE @ecc int
    -- Use "Chilkat_9_5_0.Ecc" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ecc OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    DECLARE @privKey int
    EXEC sp_OAMethod @ecc, 'GenEccKey', @privKey OUT, 'secp256r1', @prng
    EXEC sp_OAGetProperty @ecc, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'Failed to generate a new ECDSA private key.'
        EXEC @hr = sp_OADestroy @ecc
        EXEC @hr = sp_OADestroy @prng
        RETURN
      END

    DECLARE @csr int
    -- Use "Chilkat_9_5_0.Csr" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Csr', @csr OUT

    -- Add common CSR fields:
    EXEC sp_OASetProperty @csr, 'CommonName', 'mysubdomain.mydomain.com'
    EXEC sp_OASetProperty @csr, 'Country', 'GB'
    EXEC sp_OASetProperty @csr, 'State', 'Yorks'
    EXEC sp_OASetProperty @csr, 'Locality', 'York'
    EXEC sp_OASetProperty @csr, 'Company', 'Internet Widgits Pty Ltd'
    EXEC sp_OASetProperty @csr, 'EmailAddress', 'support@mydomain.com'

    -- Add the following 1.2.840.113549.1.9.14 extensionRequest
    -- Note: The easiest way to know the content and format of the XML to be added is to examine
    -- a pre-existing CSR with the same desired extensionRequest.  You can use Chilkat to
    -- get the extensionRequest from an existing CSR. 

    -- 
    -- Here is a sample extension request:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <set>
    --    <sequence>
    --        <sequence>
    --            <oid>1.3.6.1.4.1.311.20.2</oid>
    --            <asnOctets>
    --                <printable>ZATCA-Code-Signing</printable>
    --            </asnOctets>
    --        </sequence>
    --        <sequence>
    --            <oid>2.5.29.17</oid>
    --            <asnOctets>
    --                <sequence>
    --                    <contextSpecific tag="4" constructed="1">
    --                        <sequence>
    --                            <set>
    --                                <sequence>
    --                                    <oid>2.5.4.4</oid>
    --                                    <utf8>334623324234325</utf8>
    --                                </sequence>
    --                            </set>
    --                            <set>
    --                                <sequence>
    --                                    <oid>0.9.2342.19200300.100.1.1</oid>
    --                                    <utf8>310122393500003</utf8>
    --                                </sequence>
    --                            </set>
    --                            <set>
    --                                <sequence>
    --                                    <oid>2.5.4.12</oid>
    --                                    <utf8>0000</utf8>
    --                                </sequence>
    --                            </set>
    --                            <set>
    --                                <sequence>
    --                                    <oid>2.5.4.26</oid>
    --                                    <utf8>Sample E</utf8>
    --                                </sequence>
    --                            </set>
    --                            <set>
    --                                <sequence>
    --                                    <oid>2.5.4.15</oid>
    --                                    <utf8>Sample Business</utf8>
    --                                </sequence>
    --                            </set>
    --                        </sequence>
    --                    </contextSpecific>
    --                </sequence>
    --            </asnOctets>
    --        </sequence>
    --    </sequence>
    -- </set>

    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    -- Here's the code to generate the above extension request.

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'set'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence|oid', '1.3.6.1.4.1.311.20.2'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence|asnOctets|printable', 'ZATCA-Code-Signing'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|oid', '2.5.29.17'
    DECLARE @success int
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific', 1, 'tag', '4'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific', 1, 'constructed', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set|sequence|oid', '2.5.4.4'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set|sequence|utf8', '334623324234325'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[1]|sequence|oid', '0.9.2342.19200300.100.1.1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[1]|sequence|utf8', '310122393500003'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[2]|sequence|oid', '2.5.4.12'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[2]|sequence|utf8', '0000'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[3]|sequence|oid', '2.5.4.26'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[3]|sequence|utf8', 'Sample E'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[4]|sequence|oid', '2.5.4.15'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|sequence|contextSpecific|sequence|set[4]|sequence|utf8', 'Sample Business'

    -- Add the extension request to the CSR
    EXEC sp_OAMethod @csr, 'SetExtensionRequest', @success OUT, @xml

    -- Generate the CSR with the extension request
    DECLARE @csrPem nvarchar(4000)
    EXEC sp_OAMethod @csr, 'GenCsrPem', @csrPem OUT, @privKey
    EXEC sp_OAGetProperty @csr, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @csr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @privKey

        EXEC @hr = sp_OADestroy @ecc
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END


    PRINT @csrPem

    EXEC @hr = sp_OADestroy @privKey

    -- Sample PEM output:

    -- -----BEGIN CERTIFICATE REQUEST-----
    -- MIICEjCCAbkCAQAwgZcxITAfBgNVBAMMGG15c3ViZG9tYWluLm15ZG9tYWluLmNv
    -- bTELMAkGA1UEBhMCR0IxDjAMBgNVBAgMBVlvcmtzMQ0wCwYDVQQHDARZb3JrMSEw
    -- HwYDVQQKDBhJbnRlcm5ldCBXaWRnaXRzIFB0eSBMdGQxIzAhBgkqhkiG9w0BCQEW
    -- FHN1cHBvcnRAbXlkb21haW4uY29tMFkwEwYHKoZIzj0CAQYIKoZIzj0DAQcDQgAE
    -- g8EVNSV0ttlM9kG2E+J3ZB9WEDYVf2QA/8idrPRUafia1CHjd1kslwZA8eP2bAcf
    -- 2O493QAENqtW6DTHJbRz8KCBvjCBuwYJKoZIhvcNAQkOMYGtMIGqMCEGCSsGAQQB
    -- gjcUAgQUExJaQVRDQS1Db2RlLVNpZ25pbmcwgYQGA1UdEQR9MHukeTB3MRgwFgYD
    -- VQQEDA8zMzQ2MjMzMjQyMzQzMjUxHzAdBgoJkiaJk/IsZAEBDA8zMTAxMjIzOTM1
    -- MDAwMDMxDTALBgNVBAwMBDAwMDAxETAPBgNVBBoMCFNhbXBsZSBFMRgwFgYDVQQP
    -- DA9TYW1wbGUgQnVzaW5lc3MwCgYIKoZIzj0EAwIDRwAwRAIgF7D30eSBklfo+oel
    -- 1B0z64eJDB9MB3rCoiFZlj+mz0YCIHYI87eyqdtw2LOcAoBRhyxlBT6i28+Z/8t9
    -- bYsMIYvp
    -- -----END CERTIFICATE REQUEST-----

    EXEC @hr = sp_OADestroy @ecc
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @csr
    EXEC @hr = sp_OADestroy @xml


END
GO