SQL Server
SQL Server
Azure Key Vault Get the Latest Version of a Certificate
See more Azure Key Vault Examples
Demonstrates how to get the latest version of a certificate in Azure Key Vault.Note: This example requires Chilkat v9.5.0.96 or later.
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 @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.
-- See Azure Key Vault Get Certificates for a more detailed explanation
-- for how Chilkat is automatically getting the OAuth2 access token for your application.
-- Provide information needed for Chilkat to automatically get an OAuth2 access token as needed.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', 'APP_ID'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', 'APP_PASSWORD'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'resource', 'https://vault.azure.net'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/TENANT_ID/oauth2/token'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
-- Instead of providing an actual access token, we give Chilkat the information that allows it to
-- automatically fetch the access token using the OAuth2 client credentials flow.
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- Replace VAULT_NAME with the name of your Azure Key Vault.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'certName', 'importCert01'
DECLARE @sbResponse int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://VAULT_NAME.vault.azure.net/certificates/{$certName}?api-version=7.4', @sbResponse
IF @success = 0
BEGIN
DECLARE @statusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT
IF @statusCode = 0
BEGIN
-- We did not get a response from the server..
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
ELSE
BEGIN
-- We received a response, but it was an error.
PRINT 'Error response status code: ' + @statusCode
PRINT 'Error response:'
EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponse
RETURN
END
DECLARE @jsonResp int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT
EXEC sp_OAMethod @jsonResp, 'LoadSb', @success OUT, @sbResponse
EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- A sample JSON response is show at the bottom.
-- Let's do two things with the result.
-- 1) Load the DER of the cert into a Chilkat Cert object.
-- 2) Get the Key Vault version id of the certificate.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @jsonResp, 'StringOf', @sTmp0 OUT, 'cer'
EXEC sp_OAMethod @cert, 'LoadFromBase64', @success OUT, @sTmp0
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed to load certificate from Base64 DER.'
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @jsonResp
EXEC @hr = sp_OADestroy @cert
RETURN
END
-- The Azure Key Vault's "version" of the certificate is the hex string at the end of the "id", "kid", and "sid" JSON members.
-- For example: "7140c8755ed14839b5d86a9f7e7f0497"
DECLARE @sbId int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbId OUT
EXEC sp_OAMethod @jsonResp, 'StringOf', @sTmp0 OUT, 'id'
EXEC sp_OAMethod @sbId, 'Append', @success OUT, @sTmp0
DECLARE @certVersion nvarchar(4000)
EXEC sp_OAMethod @sbId, 'GetAfterFinal', @certVersion OUT, '/', 0
PRINT 'The key vault cert version is ' + @certVersion
-- {
-- "id": "https://kvchilkat.vault.azure.net/certificates/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
-- "kid": "https://kvchilkat.vault.azure.net/keys/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
-- "sid": "https://kvchilkat.vault.azure.net/secrets/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
-- "x5t": "I_e3776K5Q_6PN1HHvJoI2ZGQRQ",
-- "cer": "MIIGXjCCB .... cjTsi7yIY=",
-- "attributes": {
-- "enabled": true,
-- "nbf": 1633996800,
-- "exp": 1728691199,
-- "created": 1697411128,
-- "updated": 1697411128,
-- "recoveryLevel": "CustomizedRecoverable+Purgeable",
-- "recoverableDays": 7
-- },
-- "policy": {
-- "id": "https://kvchilkat.vault.azure.net/certificates/importCert01/policy",
-- "key_props": {
-- "exportable": true,
-- "kty": "RSA",
-- "key_size": 4096,
-- "reuse_key": false
-- },
-- "secret_props": {
-- "contentType": "application/x-pkcs12"
-- },
-- "x509_props": {
-- "subject": "CN=\"Chilkat Software, Inc.\", O=\"Chilkat Software, Inc.\", S=Illinois, C=US",
-- "ekus": [
-- "1.3.6.1.5.5.7.3.3"
-- ],
-- "key_usage": [
-- "digitalSignature"
-- ],
-- "validity_months": 37,
-- "basic_constraints": {
-- "ca": false
-- }
-- },
-- "lifetime_actions": [
-- {
-- "trigger": {
-- "lifetime_percentage": 80
-- },
-- "action": {
-- "action_type": "EmailContacts"
-- }
-- }
-- ],
-- "issuer": {
-- "name": "Unknown"
-- },
-- "attributes": {
-- "enabled": true,
-- "created": 1697411128,
-- "updated": 1697411128
-- }
-- }
-- }
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @jsonResp
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbId
END
GO