(SQL Server) Azure Key Vault Find Certificate

See more Azure Key Vault Examples

Let's say you have the certificate locally, but not with the private key. You only have the certificate, such as in a .cer file, but not the .pfx. The purpose of this example is to show how to find the same certificate in Azure Key Vault, and return the Azure Key Vault's name for the certificate.

Note: This example requires Chilkat v9.5.0.96 or later.

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- We have a .cer file locally, and we want to find this same certificate in Azure Key Vault
    -- because we'll need Azure Key Vault's name (and version) for the certificate if we are going to ask
    -- Key Vault to sign using the cert's private key.

    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/myCert.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Let's the the SHA1 thumbprint for our cert in base64url format.  This is the "x5t" member that we'll 
    -- be seeking in the list of certificates returned from Azure Key Vault.
    DECLARE @bdThumbprint int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdThumbprint OUT

    EXEC sp_OAGetProperty @cert, 'Sha1Thumbprint', @sTmp0 OUT
    EXEC sp_OAMethod @bdThumbprint, 'AppendEncoded', @success OUT, @sTmp0, 'hex'
    DECLARE @seek_x5t nvarchar(4000)
    EXEC sp_OAMethod @bdThumbprint, 'GetEncoded', @seek_x5t OUT, 'base64url'

    PRINT 'Seeking the cert with x5t = ' + @seek_x5t

    -- Provide information needed for Chilkat to automatically get an OAuth2 access token as needed.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

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

    -- Download JSON containing information about the certs in the Azure Key Vault.
    -- Replace VAULT_NAME with the name of your Azure Key Vault.
    DECLARE @sbResponse int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponse OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://VAULT_NAME.vault.azure.net/certificates?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 @cert
        EXEC @hr = sp_OADestroy @bdThumbprint
        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_9_5_0.JsonObject', @jsonResp OUT

    EXEC sp_OAMethod @jsonResp, 'LoadSb', @success OUT, @sbResponse
    EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0

    -- The JSON will contain an array of certs like this:

    -- {
    --   "value": [
    --     {
    --       "id": "https://kvchilkat.vault.azure.net/certificates/BadSSL",
    --       "x5t": "U04xLnb8Ww7BKkW9dD7P1cCHNDY",
    --       "attributes": {
    --         "enabled": true,
    --         "nbf": 1674409014,
    --         "exp": 1737481014,
    --         "created": 1697294224,
    --         "updated": 1697294224
    --       },
    --       "subject": ""
    --     },
    -- ...
    -- ...

    -- Find the record having an "x5t" value equal to the one we're seeking.
    DECLARE @jsonRec int
    EXEC sp_OAMethod @jsonResp, 'FindRecord', @jsonRec OUT, 'value', 'x5t', @seek_x5t, 1
    EXEC sp_OAGetProperty @jsonResp, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'Did not find a matching certificate.'
      END
    ELSE
      BEGIN

        PRINT 'Found the matching certificate.'
        -- The id is a value such as https://kvchilkat.vault.azure.net/certificates/BadSSL

        EXEC sp_OAMethod @jsonRec, 'StringOf', @sTmp0 OUT, 'id'
        PRINT 'id: ' + @sTmp0

        -- The name of the certificate is the last word after the final "/", such as "BadSSL"
        DECLARE @sbId int
        EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbId OUT

        EXEC sp_OAMethod @jsonRec, 'StringOf', @sTmp0 OUT, 'id'
        EXEC sp_OAMethod @sbId, 'Append', @success OUT, @sTmp0
        DECLARE @certName nvarchar(4000)
        EXEC sp_OAMethod @sbId, 'GetAfterFinal', @certName OUT, '/', 0

        PRINT 'name: ' + @certName

        EXEC @hr = sp_OADestroy @jsonRec

      END

    EXEC @hr = sp_OADestroy @jsonResp


    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @bdThumbprint
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponse
    EXEC @hr = sp_OADestroy @jsonResp
    EXEC @hr = sp_OADestroy @sbId


END
GO