(SQL Server) Renew a DigiCert Certificate from an EST-enabled profile

Demonstrates how to renew a certificate from an EST-enabled profile in DigiCert​​®​​ Trust Lifecycle Manager. (The certificate must be within the renewal window configured in the certificate profile. The CSR must have same Subject DN values as the original certificate.)

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- The example below duplicates the following OpenSSL commands:
    -- 
    -- # Name of certificate as argument 1
    -- 
    -- # Make new key
    -- openssl ecparam -name prime256v1 -genkey -noout -out ${1}.key.pem
    -- 
    -- # Make csr
    -- openssl req -new -sha256 -key ${1}.key.pem -out ${1}.p10.csr -subj "/CN=${1}"
    -- 
    -- # Request new cert
    -- curl -v --cacert data/ca.pem --cert data/${1}.pem --key data/${1}.key.pem 
    --     --data-binary @${1}.p10.csr -o ${1}.p7.b64 -H "Content-Type: application/pkcs10" https://clientauth.demo.one.digicert.com/.well-known/est/IOT/simplereenroll
    -- 
    -- # Convert to PEM
    -- openssl base64 -d -in ${1}.p7.b64 | openssl pkcs7 -inform DER -outform PEM -print_certs -out ${1}.pem

    -- ------------------------------------------------------------------------------------------------------------------

    -- Create a Fortuna PRNG and seed it with system entropy.
    -- This will be our source of random data for generating the ECC private key.
    DECLARE @fortuna int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @fortuna OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @entropy nvarchar(4000)
    EXEC sp_OAMethod @fortuna, 'GetEntropy', @entropy OUT, 32, 'base64'
    DECLARE @success int
    EXEC sp_OAMethod @fortuna, 'AddEntropy', @success OUT, @entropy, 'base64'

    DECLARE @ec int
    -- Use "Chilkat_9_5_0.Ecc" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ec OUT

    -- Generate a random EC private key on the prime256v1 curve.
    DECLARE @privKey int
    EXEC sp_OAMethod @ec, 'GenEccKey', @privKey OUT, 'prime256v1', @fortuna
    EXEC sp_OAGetProperty @ec, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ec, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        RETURN
      END

    -- Create the CSR object and set properties.
    DECLARE @csr int
    -- Use "Chilkat_9_5_0.Csr" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Csr', @csr OUT

    -- Specify your CN
    EXEC sp_OASetProperty @csr, 'CommonName', 'mysubdomain.mydomain.com'

    -- Create the CSR using the private key.
    DECLARE @bdCsr int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdCsr OUT

    EXEC sp_OAMethod @csr, 'GenCsrBd', @success OUT, @privKey, @bdCsr
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @csr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @privKey

        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        RETURN
      END

    -- Save the private key and CSR to files.
    EXEC sp_OAMethod @privKey, 'SavePkcs8EncryptedPemFile', @success OUT, 'password', 'c:/temp/qa_output/ec_privkey.pem'
    EXEC @hr = sp_OADestroy @privKey

    EXEC sp_OAMethod @bdCsr, 'WriteFile', @success OUT, 'c:/temp/qa_output/csr.pem'

    -- ----------------------------------------------------------------------
    -- Now do the CURL request to POST the CSR and get the new certificate.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    DECLARE @tlsClientCert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @tlsClientCert OUT

    EXEC sp_OAMethod @tlsClientCert, 'LoadFromFile', @success OUT, 'data/myTlsClientCert.pem'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tlsClientCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        RETURN
      END

    DECLARE @bdTlsClientCertPrivKey int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdTlsClientCertPrivKey OUT

    EXEC sp_OAMethod @bdTlsClientCertPrivKey, 'LoadFile', @success OUT, 'data/myTlsClientCert.key.pem'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load data/myTlsClientCert.key.pem'
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        RETURN
      END

    DECLARE @tlsClientCertPrivKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @tlsClientCertPrivKey OUT

    EXEC sp_OAMethod @tlsClientCertPrivKey, 'LoadAnyFormat', @success OUT, @bdTlsClientCertPrivKey, ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tlsClientCertPrivKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
        RETURN
      END

    EXEC sp_OAMethod @tlsClientCert, 'SetPrivateKey', @success OUT, @tlsClientCertPrivKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tlsClientCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
        RETURN
      END

    EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @tlsClientCert

    EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1

    -- The body of the HTTP request contains the binary CSR.
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PBinaryBd', @resp OUT, 'POST', 'https://clientauth.demo.one.digicert.com/.well-known/est/IOT/simplereenroll', @bdCsr, 'application/pkcs10', 0, 0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
        RETURN
      END

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed'
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
        RETURN
      END

    -- The response is the Base64 DER of the new certificate.
    DECLARE @myNewCert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @myNewCert OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @myNewCert, 'LoadFromBase64', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @myNewCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT 'Cert data = ' + @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @myNewCert
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    EXEC sp_OAMethod @myNewCert, 'SaveToFile', @success OUT, 'c:/temp/qa_output/myNewCert.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @myNewCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @fortuna
        EXEC @hr = sp_OADestroy @ec
        EXEC @hr = sp_OADestroy @csr
        EXEC @hr = sp_OADestroy @bdCsr
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @tlsClientCert
        EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
        EXEC @hr = sp_OADestroy @myNewCert
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @fortuna
    EXEC @hr = sp_OADestroy @ec
    EXEC @hr = sp_OADestroy @csr
    EXEC @hr = sp_OADestroy @bdCsr
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @tlsClientCert
    EXEC @hr = sp_OADestroy @bdTlsClientCertPrivKey
    EXEC @hr = sp_OADestroy @tlsClientCertPrivKey
    EXEC @hr = sp_OADestroy @myNewCert


END
GO