SQL Server Requires Chilkat v11.0.0+
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.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
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.
-- This example will generate a secp256r1 ECDSA key for the CSR.
DECLARE @ecc int
EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ecc OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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 @ecc, 'GenKey', @success OUT, 'secp256r1', @prng, @privKey
IF @success = 0
BEGIN
PRINT 'Failed to generate a new ECDSA private key.'
EXEC @hr = sp_OADestroy @ecc
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @privKey
RETURN
END
DECLARE @csr int
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
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', '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 @ecc
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @csr
EXEC @hr = sp_OADestroy @xml
RETURN
END
PRINT @csrPem
-- 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 @privKey
EXEC @hr = sp_OADestroy @csr
EXEC @hr = sp_OADestroy @xml
END
GO