(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.

For more information, see https://learn.microsoft.com/en-us/rest/api/keyvault/certificates/get-certificate/get-certificate?tabs=HTTP

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- 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.

    DECLARE @success int

    -- Provide information needed for Chilkat to automatically get an OAuth2 access token as needed.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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