SQL Server
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 SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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.StringBuilder', @sbCsr OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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.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.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.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.Ecc', @ecdsa OUT
DECLARE @prng int
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @ecdsa, 'GenKey', @success OUT, 'secp256k1', @prng, @privKey
IF @success = 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
EXEC @hr = sp_OADestroy @privKey
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.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 @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 @privKey
EXEC @hr = sp_OADestroy @csr
RETURN
END
PRINT @csrPem
-- 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 @privKey
EXEC @hr = sp_OADestroy @csr
END
GO