SQL Server

Azure Key Vault Get OAuth2 Access Token using Client Credentials

See more Azure Key Vault Examples

Demonstrates how to get an OAuth2 access token using client credentials for an Azure Key Vault resource. SQL Server

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- ---
    -- See RBAC Permissions Required for Azure Key Vault API Using OAuth2 Client Credentials Flow
    -- ---

    -- You can use OAuth2 client credentials with an Azure App (service principal) that has
    -- the required Role-Based Access Control (RBAC) permissions.
    -- In this case, it would be service principal with RBAC permissions to administer and manage
    -- the key vault.

    -- You can create the Azure App (also known as the Service Principal)
    -- in the Azure CLI (command line interface) as follows:

    -- ----------------------------------------------------------------------
    -- az ad sp create-for-rbac --name http://example.com --role Contributor
    -- ----------------------------------------------------------------------

    -- The argument to --name must be a valid URI that is a verified domain of your 
    -- organization or its subdomain.

    -- The output of the above "az ad sp create-for-rbac ..." command is JSON such as this:

    -- {
    --   "appId": "25ac6e3a-9ac7-42b9-b13e-18644c1de959",
    --   "displayName": "azure-cli-2023-10-14-22-38-15",
    --   "name": "http://example.com",
    --   "password": "f1f2f3f0-52dc-4236-8295-c8a1d6aa393c",
    --   "tenant": "4d8dfd66-68d1-13b0-af5c-b31b4b3d53d"
    -- }

    -- Save the values in the above JSON.  You'll need it below..

    -- You'll also want to add the role of "Key Vault Administrator" to the Service Principal
    -- for the particular key vault.

    -- ----------------------------------------------------------------------
    -- az role assignment create --assignee <Application-ID> --role "Key Vault Administrator" 
    --       --scope /subscriptions/<Subscription-ID>/resourceGroups/<Resource-Group-Name>/providers/Microsoft.KeyVault/vaults/<KeyVault-Name>
    -- ----------------------------------------------------------------------

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

    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    -- Add query params to the request.
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'

    -- Use the service principal's appId
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', '25ac6e3a-9ac7-42b9-b13e-18644c1de959'

    -- Use the service principal's password.
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_secret', 'f1f2f3f0-52dc-4236-8295-c8a1d6aa393c'

    -- Note: The resource must match the API for which you're using the access token..
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'resource', 'https://vault.azure.net'

    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'tenant', '4d8dfd66-68d1-13b0-af5c-b31b4b3d53d'
    EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
    EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'

    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://login.microsoftonline.com/{$tenant}/oauth2/token', @req, @resp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    DECLARE @strRespBody nvarchar(4000)
    EXEC sp_OAGetProperty @resp, 'BodyStr', @strRespBody OUT
    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Status Code = ' + @respStatusCode

        PRINT 'Response Body:'

        PRINT @strRespBody
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    DECLARE @jsonResp int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT

    EXEC sp_OAMethod @jsonResp, 'Load', @success OUT, @strRespBody
    EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The result is an access token such as the following:

    -- {
    --   "token_type": "Bearer",
    --   "expires_in": "3600",
    --   "ext_expires_in": "3600",
    --   "expires_on": "1557864616",
    --   "not_before": "1557860716",
    --   "resource": "https://vault.azure.net",
    --   "access_token": "eyJ0eXAiOiJKV1QiL ... 20UFDDOHEyUg"
    -- }

    -- If you wish, you can save the token to a file.
    -- The access token is generally valid for 1 hour.
    -- After 1 hour, you would need to get a new access token in the same way.
    EXEC sp_OAMethod @jsonResp, 'WriteFile', @success OUT, 'qa_data/tokens/azureKeyVaultToken.json'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @jsonResp


END
GO