(SQL Server) Generate a CSR with keyUsage, extKeyUsage, and other Extensions

See more CSR Examples

Demonstrates how to generate a CSR containing a 1.2.840.113549.1.9.14 extensionRequest with the following extensions:

• 1.3.6.1.4.1.311.20.2 enrollCerttypeExtension
• 2.5.29.15 keyUsage
• 2.5.29.37 extKeyUsage
• 2.5.29.14 subjectKeyIdentifier

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 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_9_5_0.Ecc', @ecc OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @prng int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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>
    --                 <universal tag="30" constructed="0">AEUAbgBkAEUAbgB0AGkAdAB5AEMAbABpAGUAbgB0AEEAdQB0AGgAQwBlAHIAdABpAGYAaQBjAGEAdABl
    -- AF8AQwBTAFIAUABhAHMAcwB0AGgAcgBvAHUAZwBoAC8AVgAx</universal>
    --             </asnOctets>
    --         </sequence>
    --         <sequence>
    --             <oid>2.5.29.15</oid>
    --             <bool>1</bool>
    --             <asnOctets>
    --                 <bits n="3">A0</bits>
    --             </asnOctets>
    --         </sequence>
    --         <sequence>
    --             <oid>2.5.29.37</oid>
    --             <asnOctets>
    --                 <sequence>
    --                     <oid>1.3.6.1.5.5.7.3.3</oid>
    --                 </sequence>
    --             </asnOctets>
    --         </sequence>
    --         <sequence>
    --             <oid>2.5.29.14</oid>
    --             <asnOctets>
    --                 <octets>MCzBMQAViXBz8IDt8LsgmJxJ4Xg=</octets>
    --             </asnOctets>
    --         </sequence>
    --     </sequence>
    -- </set>

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

    -- A few notes:
    -- The string "AEUAbgBkAEUAbgB0AGkAdAB5AEMAbABpAGUAbgB0AEEAdQB0AGgAQwBlAHIAdABpAGYAaQBjAGEAdABlAF8AQwBTAFIAUABhAHMAcwB0AGgAcgBvAHUAZwBoAC8AVgAx"
    -- is the base64 encoding of the utf-16be byte representation of the string "EndEntityClientAuthCertificate_CSRPassthrough/V1"

    DECLARE @s nvarchar(4000)
    SELECT @s = 'EndEntityClientAuthCertificate_CSRPassthrough/V1'
    DECLARE @bdTemp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdTemp OUT

    DECLARE @success int
    EXEC sp_OAMethod @bdTemp, 'AppendString', @success OUT, @s, 'utf-16be'
    DECLARE @s_base64_utf16be nvarchar(4000)
    EXEC sp_OAMethod @bdTemp, 'GetEncoded', @s_base64_utf16be OUT, 'base64'
    -- The string should be "AEUA....."

    PRINT @s_base64_utf16be

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

    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, 'UpdateAttrAt', @success OUT, 'sequence|sequence|asnOctets|universal', 1, 'tag', '30'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'sequence|sequence|asnOctets|universal', 1, 'constructed', '0'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence|asnOctets|universal', @s_base64_utf16be
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|oid', '2.5.29.15'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|bool', '1'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'sequence|sequence[1]|asnOctets|bits', 1, 'n', '3'
    -- A0 is hex for decimal 160.
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[1]|asnOctets|bits', 'A0'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[2]|oid', '2.5.29.37'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[2]|asnOctets|sequence|oid', '1.3.6.1.5.5.7.3.3'

    -- This is the subjectKeyIdentifier extension.
    -- The string "MCzBMQAViXBz8IDt8LsgmJxJ4Xg=" is base64 that decodes to 20 bytes, which is a SHA1 hash.
    -- This is simply a hash of the DER of the public key.

    DECLARE @pubKey int
    EXEC sp_OAMethod @privKey, 'GetPublicKey', @pubKey OUT
    DECLARE @bdPubKeyDer int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdPubKeyDer OUT

    EXEC sp_OAMethod @pubKey, 'GetEncoded', @sTmp0 OUT, 1, 'base64'
    EXEC sp_OAMethod @bdPubKeyDer, 'AppendEncoded', @success OUT, @sTmp0, 'base64'
    DECLARE @ski nvarchar(4000)
    EXEC sp_OAMethod @bdPubKeyDer, 'GetHash', @ski OUT, 'sha1', 'base64'
    EXEC @hr = sp_OADestroy @pubKey

    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[3]|oid', '2.5.29.14'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|sequence[3]|asnOctets|octets', @ski

    -- 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 @bdTemp
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @bdPubKeyDer
        RETURN
      END


    PRINT @csrPem

    EXEC @hr = sp_OADestroy @privKey


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


END
GO