Creating the Data Source Name

This section describes how to edit the DSN configuration and then authenticate and connect to Microsoft Excel Online APIs.

DSN Configuration

You can use the Microsoft ODBC Data Source Administrator to edit the DSN configuration. Note that the installation process creates a both a user DSN and a system DSN, as described in Installing the Connector.

Note: The connector stores connection information in the Windows registry. To ensure that the connector can write to the registry, either run Power BI as an administrator or use a User DSN for your connection instead of a System DSN.

User DSN

Complete the following steps to edit the DSN configuration:

Select Start > Search, and enter ODBC Data Sources in the Search box.
Choose the version of the ODBC Administrator that corresponds to the bitness of your Power BI Desktop installation (32-bit or 64-bit).
Select the system data source and click Configure.
Edit the information on the Connection tab and click OK.

System DSN

Configure the system DSN the same way as the user DSN, except you will need to switch to the System DSN tab before performing Step 3.

If you're using Power BI's On-Premises Data Gateway with Standard mode, you must use the system DSN.

You must also specify a valid location for OAuthSettingsLocation (a path where OAuth credentials are locally stored to avoid repeated OAuth prompts).

This is because the Standard mode runs on service mode and can only access permitted locations, such as C:\Windows\ServiceProfiles\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway.

Connecting to Microsoft Excel Online

There are two authentication methods available for connecting to Microsoft Excel Online data source:

OAuth 2.0-based methods: Microsoft Excel Online provides OAuth 2.0-based authentication via both Azure AD and Azure Service Principal.
- For Azure AD authentication, set AuthScheme to AzureAD.
- For Azure Service Principal authentication, set AuthScheme to AzureServicePrincipal.
Managed Service Identity (MSI) authentication. To use this method, set AuthScheme to AzureMSI.

Azure AD

Azure AD is user-based authentication. Credentials are supplied to connect AzureAD to Microsoft Excel Online via a Desktop application. To use the embedded credentials for authentication, leave the OAuth credentials (OAuthClientId and OAuthClientSecret) blank.

For connecting via a Web application, you must create a custom OAuth Azure AD application. The creation of a Custom Azure AD application establishes the OAuth credentials OAuthClientId and OAuthClientSecret. For a discussion of how to create a custom Azure AD application, and reasons why that might be advantageous even if you are not connecting via a Web application, see Creating an Azure AD Application.

Azure Service Principal

The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an AzureAD App and an Azure Service Principal

When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating an Azure AD Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
Select the subscription to assign the application to.
Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
Select Owner as the role to assign to your created Azure AD app.

Complete the Authentication Choose whether to use a client secret or a certificate and follow the relevant steps below.

Client Secret

Set these connection properties:

AuthScheme: AzureServicePrincipal to use a client secret.
InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: The tenant you want to connect to.
OAuthClientId: The client Id in your application settings.
OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

AuthScheme: AzureServicePrincipalCert to use a certificate.
InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: The tenant you want to connect to.
OAuthJWTCert: The JWT Certificate store.
OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

Managed Service Identity (MSI)

If you are running Microsoft Excel Online on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's "client_id".

When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Connecting to a Workbook

The connector exposes workbooks and worksheets from drives you specify in your Microsoft account. You can connect to a workbook by providing authentication to Excel Online and setting any of the following properties.

To control which drives are discovered:

Drive: The ID of a specific drive. You can use the Drives and SharePointSites views to view all the sites and drives you have access to.
SharepointURL: The browser URL of a SharePoint site. The driver will expose all drives under the site.
OAuthClientId: If AuthScheme is set to AzureServicePrincipal or if OAuthGrantType is set to CLIENT, the drive associated with your OAuth app will be exposed.

If none of the above are specified, access is restricted to the authenticated user's personal drive.

To control which workbooks and worksheets are exposed OR which drives are exposed:

Workbook: The name or Id of the workbook. An authenticate user can view a list of information about the available workbooks by executing a query to the Workbooks view.
UseSandbox: True to connect to a workbook in a sandbox account; otherwise, leave blank.
BrowsableSchemas: A list of drive names to expose.
Tables: A list of table names to expose.

Executing SQL Against Worksheet Data

For information on how to execute data manipulation SQL against worksheets and ranges, see:

For details on how the connector models worksheets and cells as tables and columns, See Data Model.

Retrieving Data from SharePoint Excel Files

To retrieve data from Sharepoint Excel files, set the SharepointURL connection property to the URL of your Sharepoint site. For example,

 SharepointURL=https://mysite.sharepoint.com/

The driver automatically looks up each document library you have in SharePoint and lists it as a schema. Individual Excel workbooks and worksheets are listed as tables in the format Workbook_Worksheet under their corresponding document library. This works in the same manner as listing your own personal Excel documents when SharepointURL is not set.

Power BI Connector for Microsoft Excel Online