SQL Server

Generate CSR with Uncommon Fields

See more CSR Examples

Demonstrates how to generate a new RSA key and a Certificate Signing Request (CSR) for this:

csr.common.name=Admin forInformation-MainShop
csr.serial.number=1-XYZ|2-2.0|3-999695
csr.organization.identifier=990099994100099
csr.organization.unit.name=9999910000
csr.organization.name=9999910000
csr.country.name=SA
csr.invoice.type=1100
csr.location.address=King Fahed Road
csr.industry.business.category=MainOffice

SQL Server

-- 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- First generate an RSA private key.
    DECLARE @rsa int
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Generate a random 2048-bit RSA key.
    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @rsa, 'GenKey', @success OUT, 2048, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Create the CSR object and set properties.
    DECLARE @csr int
    EXEC @hr = sp_OACreate 'Chilkat.Csr', @csr OUT

    EXEC sp_OASetProperty @csr, 'CommonName', 'Admin forInformation-MainShop'

    -- Country Name (2 letter code)
    EXEC sp_OASetProperty @csr, 'Country', 'SA'

    -- Organization Name (eg, company)
    EXEC sp_OASetProperty @csr, 'Company', '9999910000'

    -- Organizational Unit Name (eg, secion/division)
    EXEC sp_OASetProperty @csr, 'CompanyDivision', '9999910000'

    -- See https://www.alvestrand.no/objectid/2.5.4.html for OIDs for the following fields:

    -- csr.serial.number=1-XYZ|2-2.0|3-999695
    -- 2.5.4.5 - id-at-serialNumber 
    EXEC sp_OAMethod @csr, 'SetSubjectField', @success OUT, '2.5.4.5', '1-XYZ|2-2.0|3-999695', 'UTF8String'

    -- csr.organization.identifier=990099994100099
    -- 2.5.4.45 - id-at-uniqueIdentifier   Don't know if this is correct.
    EXEC sp_OAMethod @csr, 'SetSubjectField', @success OUT, '2.5.4.45', '1100', 'UTF8String'

    -- csr.invoice.type=1100
    -- Don't know what this OID would be...
    EXEC sp_OAMethod @csr, 'SetSubjectField', @success OUT, '2.5.4.99', '1100', 'UTF8String'

    -- csr.location.address=King Fahed Road
    -- 2.5.4.9 - id-at-streetAddress 
    EXEC sp_OAMethod @csr, 'SetSubjectField', @success OUT, '2.5.4.9', 'King Fahed Road', 'UTF8String'

    -- csr.industry.business.category=MainOffice
    -- 2.5.4.15 - id-at-businessCategory 
    EXEC sp_OAMethod @csr, 'SetSubjectField', @success OUT, '2.5.4.15', 'MainOffice', 'UTF8String'

    -- Create the CSR using the private key.
    DECLARE @pemStr nvarchar(4000)
    EXEC sp_OAMethod @csr, 'GenCsrPem', @pemStr OUT, @privKey
    EXEC sp_OAGetProperty @csr, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @csr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @csr
        RETURN
      END

    -- Save the private key and CSR to a files.
    EXEC sp_OAMethod @privKey, 'SavePkcs8EncryptedPemFile', @success OUT, 'password', 'qa_output/privKey1.pem'

    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'qa_output/csr1.pem', @pemStr, 'utf-8', 0

    -- Show the CSR.

    PRINT @pemStr

    -- Sample output:
    -- The CSR PEM can be checked here:
    -- https://www.networking4all.com/en/support/tools/csr+check/
    -- Copy-and-paste the PEM into the online CSR Decoding / CSR Verification form

    -- 	-----BEGIN CERTIFICATE REQUEST-----
    -- 	MIIC6jCCAdICAQAwgaQxITAfBgNVBAMMGG15c3ViZG9tYWluLm15ZG9tYWluLmNv
    -- ...
    -- ...
    -- 	hJnYCvjzFz4O9VtT+JtP9ldRHWV3KpZ8ne3AjD+F
    -- 	-----END CERTIFICATE REQUEST-----

    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @csr
    EXEC @hr = sp_OADestroy @fac


END
GO