ODBC Driver for Microsoft Excel

Build 24.0.8963

Creating a Custom OAuth App

There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.

When to Create a Custom Application

CData embeds OAuth Application Credentials with CData branding that can be used when connecting via either a Desktop Application or from a Headless Machine.

You may choose to use your own AzureAD Application Credentials when you want to

  • control branding of the Authentication Dialog
  • control the redirect URI that the application redirects the user to after the user authenticates
  • customize the permissions that you are requesting from the user

Custom AzureAD Applications

You can use a custom AzureAD application to authenticate a service account or a user account. You can always create a custom AzureAD application, but note that desktop and headless connections support embedded OAuth, which simplifies the process of authentication. See "Establishing a Connection" for information about using the embedded OAuth application.

Create a Custom AzureAD App

Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.

  1. Log in to https://portal.azure.com.
  2. In the left-hand navigation pane, select All services. Filter and select App registrations.
  3. Click New registrations.
  4. Enter an application name and select the desired tenant setup. When creating a custom AzureAD application in Azure Active Directory, you can define whether the application is single- or multi-tenant. If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the CData ODBC Driver for Microsoft Excel. Otherwise, the authentication attempt fails with an error. If your application is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your application, choose one of the multi-tenant options.
  5. Set the redirect url to http://localhost:33333, the driver's default. Or, specify a different port and set CallbackURL to the exact reply URL you defined.
  6. Click Register to register the new application. This opens an application management screen. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
  7. Navigate to the "Certificates & Secrets" and define the application authentication type. There are two types of authentication available: using a client secret or a certificate. The recommended authentication method is using a certificate.
    • Option 1: Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
    • Option 2: Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will need it as the OAuthClientSecret.
  8. Select API Permissions > Add a permission > Azure Storage > user_impersonation > Add permissions.
  9. Save your changes.
  10. If you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under "Admin Consent".

Custom AzureAD Service Principal Applications

When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.

Create a Custom AzureAD App with an Azure Service Principal

Follow the steps below to obtain the AzureAD values for your application.

  1. Log in to https://portal.azure.com.
  2. In the left-hand navigation pane, select All services. Filter and select App registrations.
  3. Click New registrations.
  4. Enter an app name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the driver's default.
  5. After creating the application, copy the Application (client) Id value displayed in the "Overview" section. This value is used as the OAuthClientId
  6. Define the app authentication type by going to the "Certificates & Secrets" section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate.
    • Option 1 - Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
    • Option 2 - Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will use it as the OAuthClientSecret.
  7. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

Add Service Principal to Workspace

Follow the steps below to add a service principal to a workspace.

  1. Log in to Microsoft Fabric.
  2. Click the gear icon (Settings) on the top right.
  3. Select Admin portal.
  4. In the left-hand navigation pane, select Tenant settings.
  5. Scroll until you find Developer settings.
  6. Expand Service principals can use Fabric APIs.
  7. Enable the option.
  8. Select Apply.
  9. Select the workspace where you want to add your service principal.
  10. Click Manage access.
  11. Click Add people or groups.
  12. Enter the name of your application (verify the ID if there are multiple applications with the same name).
  13. Set the level of access you would like to grant to your application. Contributor is the lowest security level necessary to access OneLake via the API.
  14. Select Add.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8963