SQL Server
SQL Server
Renew a DigiCert Certificate from an EST-enabled profile
See more Certificates Examples
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 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 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
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'
EXEC sp_OAMethod @fortuna, 'AddEntropy', @success OUT, @entropy, 'base64'
DECLARE @ec int
EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ec OUT
-- Generate a random EC private key on the prime256v1 curve.
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @ec, 'GenKey', @success OUT, 'prime256v1', @fortuna, @privKey
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ec, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fortuna
EXEC @hr = sp_OADestroy @ec
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
-- Specify your CN
EXEC sp_OASetProperty @csr, 'CommonName', 'mysubdomain.mydomain.com'
-- Create the CSR using the private key.
DECLARE @bdCsr int
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 @fortuna
EXEC @hr = sp_OADestroy @ec
EXEC @hr = sp_OADestroy @privKey
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 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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @tlsClientCert int
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 @privKey
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
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 @privKey
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
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 @privKey
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 @privKey
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @url nvarchar(4000)
SELECT @url = 'https://clientauth.demo.one.digicert.com/.well-known/est/IOT/simplereenroll'
EXEC sp_OAMethod @http, 'HttpBd', @success OUT, 'POST', @url, @bdCsr, 'application/pkcs10', @resp
IF @success = 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 @privKey
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 @resp
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 @fortuna
EXEC @hr = sp_OADestroy @ec
EXEC @hr = sp_OADestroy @privKey
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 @resp
RETURN
END
-- The response is the Base64 DER of the new certificate.
DECLARE @myNewCert int
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 @fortuna
EXEC @hr = sp_OADestroy @ec
EXEC @hr = sp_OADestroy @privKey
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 @resp
EXEC @hr = sp_OADestroy @myNewCert
RETURN
END
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 @privKey
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 @resp
EXEC @hr = sp_OADestroy @myNewCert
RETURN
END
PRINT 'Success.'
EXEC @hr = sp_OADestroy @fortuna
EXEC @hr = sp_OADestroy @ec
EXEC @hr = sp_OADestroy @privKey
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 @resp
EXEC @hr = sp_OADestroy @myNewCert
END
GO