(SQL Server) Duplicate CSR Created by OpenSSL with Config.cnf

See more CSR Examples

Demonstrates how to duplicate a CSR created by the following commands:

# Generate Private Key
openssl ecparam -name secp256k1 -genkey -noout -out PrivateKey.pem

#Generate CSR
openssl req -new -sha256 -key PrivateKey.pem -extensions v3_req -config Config.cnf -out CSR.csr

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example duplicates the CSR created by OpenSSL with the following config file:

    -- oid_section = OIDs
    -- [ OIDs ]
    -- certificateTemplateName= 1.3.6.1.4.1.311.20.2
    -- 
    -- [ req ]
    -- default_bits 	= 2048
    -- emailAddress 	= it@example.sa
    -- req_extensions	= v3_req
    -- x509_extensions 	= v3_ca
    -- prompt = no
    -- default_md = sha256
    -- req_extensions = req_ext
    -- distinguished_name = dn
    -- 
    -- [ v3_req ]
    -- basicConstraints = CA:FALSE
    -- keyUsage = digitalSignature, nonRepudiation, keyEncipherment
    -- 
    -- [req_ext]
    -- certificateTemplateName = ASN1:PRINTABLESTRING:ZATCA-Code-Signing
    -- subjectAltName = dirName:alt_names
    -- 
    -- [ dn ]
    -- CN =EXAMPLE-CORP  				# Common Name
    -- C=SA									# Country Code e.g SA
    -- OU=HEAD-OFFICE							# Organization Unit Name
    -- O=ASC									# Organization Name
    -- 
    -- [alt_names]
    -- SN=1-ASC|2-V01|3-1234567890				# EGS Serial Number 1-ABC|2-PQR|3-XYZ
    -- UID=312345678900003						# Organization Identifier (VAT Number)
    -- title=1100								# Invoice Type
    -- registeredAddress=Dammam  	 			# Address
    -- businessCategory=IT						# Business Category

    -- The OpenSSL commands we are duplicating:

    -- openssl ecparam -name secp256k1 -genkey -noout -out PrivateKey.pem
    -- openssl req -new -sha256 -key PrivateKey.pem -extensions v3_req -config Config.cnf -out CSR.csr

    -- The 1st step is to actually use OpenSSL to generate a sample CSR.csr that we wish to duplicate.
    -- With the sample CSR.csr, we get the ExtensionRequest as XML.  
    -- For example:

    DECLARE @sbCsr int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbCsr OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @sbCsr, 'LoadFile', @success OUT, 'qa_data/csr/openssl_cnf/CSR.csr', 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load CSR.csr'
        EXEC @hr = sp_OADestroy @sbCsr
        RETURN
      END

    DECLARE @csr0 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Csr', @csr0 OUT

    EXEC sp_OAMethod @sbCsr, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @csr0, 'LoadCsrPem', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @csr0, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbCsr
        EXEC @hr = sp_OADestroy @csr0
        RETURN
      END

    DECLARE @xml0 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml0 OUT

    EXEC sp_OAMethod @csr0, 'GetExtensionRequest', @success OUT, @xml0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @csr0, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbCsr
        EXEC @hr = sp_OADestroy @csr0
        EXEC @hr = sp_OADestroy @xml0
        RETURN
      END

    -- Let's examine the extension request..
    EXEC sp_OAMethod @xml0, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- <?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>1-ASC|2-V01|3-1234567890</utf8>
    --                                 </sequence>
    --                             </set>
    --                             <set>
    --                                 <sequence>
    --                                     <oid>0.9.2342.19200300.100.1.1</oid>
    --                                     <utf8>312345678900003</utf8>
    --                                 </sequence>
    --                             </set>
    --                             <set>
    --                                 <sequence>
    --                                     <oid>2.5.4.12</oid>
    --                                     <utf8>1100</utf8>
    --                                 </sequence>
    --                             </set>
    --                             <set>
    --                                 <sequence>
    --                                     <oid>2.5.4.26</oid>
    --                                     <utf8>Dammam</utf8>
    --                                 </sequence>
    --                             </set>
    --                             <set>
    --                                 <sequence>
    --                                     <oid>2.5.4.15</oid>
    --                                     <utf8>IT</utf8>
    --                                 </sequence>
    --                             </set>
    --                         </sequence>
    --                     </contextSpecific>
    --                 </sequence>
    --             </asnOctets>
    --         </sequence>
    --     </sequence>
    -- </set>

    -- If you wish to generate the above XML without going through the above steps, copy the XML into
    -- the online tool at https://tools.chilkat.io/xmlCreate

    -- Here is the generated code for the above XML:

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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'
    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', '1-ASC|2-V01|3-1234567890'
    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', '312345678900003'
    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', '1100'
    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', 'Dammam'
    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', 'IT'

    -- We'll need a new secp256k1 private key, so let's generate it.
    DECLARE @ecdsa int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Ecc', @ecdsa OUT

    DECLARE @prng int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Prng', @prng OUT

    DECLARE @privKey int
    EXEC sp_OAMethod @ecdsa, 'GenEccKey', @privKey OUT, 'secp256k1', @prng
    EXEC sp_OAGetProperty @ecdsa, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ecdsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbCsr
        EXEC @hr = sp_OADestroy @csr0
        EXEC @hr = sp_OADestroy @xml0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @ecdsa
        EXEC @hr = sp_OADestroy @prng
        RETURN
      END

    PRINT 'Generated secp256k1 private key.'

    -- Use a new CSR object to generate a CSR with the private key and extension request.
    DECLARE @csr int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Csr', @csr OUT

    -- Add the [dn] fields
    --  [ dn ]
    --  CN =EXAMPLE-CORP  				# Common Name
    --  C=SA									# Country Code e.g SA
    --  OU=HEAD-OFFICE							# Organization Unit Name
    --  O=ASC									# Organization Name
    EXEC sp_OASetProperty @csr, 'CommonName', 'EXAMPLE-CORP'
    EXEC sp_OASetProperty @csr, 'Country', 'SA'
    EXEC sp_OASetProperty @csr, 'CompanyDivision', 'HEAD-OFFICE'
    EXEC sp_OASetProperty @csr, 'Company', 'ASC'

    -- 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 @sbCsr
        EXEC @hr = sp_OADestroy @csr0
        EXEC @hr = sp_OADestroy @xml0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @ecdsa
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @csr
        RETURN
      END


    PRINT @csrPem

    EXEC @hr = sp_OADestroy @privKey

    -- Sample output:

    -- -----BEGIN CERTIFICATE REQUEST-----
    -- MIIBuDCCAV8CAQAwSDEVMBMGA1UEAwwMRVhBTVBMRS1DT1JQMQswCQYDVQQGEwJT
    -- QTEUMBIGA1UECwwLSEVBRC1PRkZJQ0UxDDAKBgNVBAoMA0FTQzBWMBAGByqGSM49
    -- AgEGBSuBBAAKA0IABFI5rusr76HiJcMMr1r4L0B0BOAs6azLkt/RwHoT6A0xFRRt
    -- tulWT40tNhx3qJ4I5ePNgMceOEtuK1kMGVTovI6ggbcwgbQGCSqGSIb3DQEJDjGB
    -- pjCBozAhBgkrBgEEAYI3FAIEFBMSWkFUQ0EtQ29kZS1TaWduaW5nMH4GA1UdEQR3
    -- MHWkczBxMSEwHwYDVQQEDBgxLUFTQ3wyLVYwMXwzLTEyMzQ1Njc4OTAxHzAdBgoJ
    -- kiaJk/IsZAEBDA8zMTIzNDU2Nzg5MDAwMDMxDTALBgNVBAwMBDExMDAxDzANBgNV
    -- BBoMBkRhbW1hbTELMAkGA1UEDwwCSVQwCgYIKoZIzj0EAwIDRwAwRAIgJnbgpSGb
    -- diB+0M1VTqc1GU9sFsfnOvVN/8WhWRRxQIwCIF5eH9vgMgXyoU284X8Bx3dqOJ4q
    -- xashGWci87POxSvT
    -- -----END CERTIFICATE REQUEST-----

    EXEC @hr = sp_OADestroy @sbCsr
    EXEC @hr = sp_OADestroy @csr0
    EXEC @hr = sp_OADestroy @xml0
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @ecdsa
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @csr


END
GO