Authentication with a data source

  • Article
  • six minutes to read

When you attempt to connect to a data source using a new connector for the beginning time, yous might be asked to select the authentication method to utilize when accessing the data. After yous've selected the authentication method, you won't be asked to select an authentication method for the connector using the specified connexion parameters. However, if you demand to alter the authentication method later, y'all can do so.

Select an authentication method

Different connectors show different hallmark methods. For example, the OData Feed connector in Power BI Desktop and Excel displays the following authentication method dialog box.

OData Feed authentication method.

If you're using a connector from an online app, such as the Power BI service or Power Apps, you'll see an authentication method dialog box for the OData Feed connector that looks something like the following image.

OData Feed online authentication method.

As you can see, a different selection of authentication methods is presented from an online app. Likewise, some connectors might ask you to enter the name of an on-premises data gateway to be able to connect to your information.

Set the level of the hallmark method

In connectors that crave you to enter a URL, y'all'll be asked to select the level to which the authentication method will be practical. For example, if y'all select the Web connector with a URL of https://contoso.com/2020data/List_of_countries_and_capitals, the default level setting for your hallmark method will be https://contoso.com.

Default level setting.

The level you lot select for the authentication method you chose for this connector determines what part of a URL will have the hallmark method applied to it. If you select the height-level web address, the hallmark method you select for this connector will exist used for that URL address or any subaddress within that address.

However, yous might not desire to set the top-level address to a specific authentication method because unlike subaddresses can require dissimilar authentication methods. Ane example might exist if you were accessing two split folders of a unmarried SharePoint site and wanted to use different Microsoft accounts to access each i.

After you lot've ready the authentication method for a connector'southward specific address, you won't need to select the authentication method for that connector using that URL accost or any subaddress once more. For example, permit's say y'all select the https://contoso.com/ address as the level yous want the Web connector URL settings to apply to. Whenever you utilise a Web connector to access any webpage that begins with this accost, you won't be required to select the hallmark method again.

Change the hallmark method

In some cases, yous might demand to change the authentication method you use in a connector to access a specific information source.

To edit the authentication method in Power BI Desktop or Excel

  1. Practice ane of the post-obit:

    • In Power BI Desktop, on the File tab, select Options and settings > Data source settings.

      Power BI Desktop data source settings.

    • In Excel, on the Data tab, select Become Data > Information Source Settings.

      Excel data source settings.

  2. In the Data source settings dialog box, select Global permissions, choose the website where y'all desire to change the permission setting, and and then select Edit Permissions.

  3. In the Edit Permissions dialog box, under Credentials, select Edit.

    Edit permissions.

  4. Modify the credentials to the type required by the website, select Relieve, and then select OK.

Y'all tin can also delete the credentials for a particular website in step 3 by selecting Articulate Permissions for a selected website, or by selecting Clear All Permissions for all of the listed websites.

To edit the authentication method in online services, such every bit for dataflows in the Power BI service and Microsoft Ability Platform

  1. Select the connector, and and then select Edit connection.

    Edit the online authentication methods.

  2. Make the required changes, and then select Side by side.

Connecting with Azure Active Directory using the Web and OData connectors

When connecting to data sources and services that crave hallmark through OAuth or Azure Active Directory-based authentication, in certain cases where the service is configured correctly, you tin can employ the built-in Web or OData connectors to authenticate and connect to information without requiring a service-specific or custom connector.

This section outlines connection symptoms when the service isn't configured properly. It besides provides information on how Power Query interacts with the service when it's properly configured.

Symptoms when the service isn't configured properly

If y'all meet the error We were unable to connect considering this credential type isn't supported for this resources. Delight choose another credential type, this mistake means that your service doesn't back up the authentication type.

One example of this is the Northwind OData service.

  1. Enter the Northwind endpoint in the "Become Data" feel using the OData connector.

    Connect to Northwind.

  2. Select OK to enter the hallmark experience. Normally, because Northwind isn't an authenticated service, you would just use Anonymous. To demonstrate lack of support for Azure Active Directory, cull Organizational business relationship, and so select Sign in.

    Northwind authentication.

  3. You'll run across the error, indicating that OAuth or Azure Active Directory authentication isn't supported in the service.

    Northwind error.

Supported workflow

One example of a supported service working properly with OAuth is CRM, for example, https://*.crm.dynamics.com/api/data/v8.ii.

  1. Enter the URL in the "Get Data" experience using the OData connector.

    CRM authentication.

  2. Select Organizational Account, then select Sign-in to go along to connect using OAuth.

    CRM authentication dialog.

  3. The request succeeds and the OAuth flow continues to allow y'all to authenticate successfully.

    CRM authentication success.

When y'all select Sign-in in Step 2 above, Power Query sends a asking to the provided URL endpoint with an Say-so header with an empty bearer token.

              Go https://*.crm.dynamics.com/api/information/v8.2 HTTP/ane.1 Authorization: Bearer User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225) Host: pbi.crm.dynamics.com Connection: Proceed-Alive

The service is then expected to respond with a 401 response with a WWW_Authenticate header indicating the Azure AD dominance URI to utilise. This response should include the tenant to sign into, or /common/ if the resources isn't associated with a specific tenant.

              HTTP/i.1 401 Unauthorized Cache-Control: private Content-Type: text/html Server:  World wide web-Authenticate: Bearer authorization_uri=https://login.microsoftonline.com/3df2eaf6-33d0-4a10-8ce8-7e596000ebe7/oauth2/qualify  Date: Wednesday, 15 Aug 2022 15:02:04 GMT Content-Length: 49

Power Query can then initiate the OAuth catamenia against the authorization_uri. Power Query requests an Azure Advertizement Resources or Audience value equal to the domain of the URL being requested. This value would be the value you utilize for your Azure Application ID URL value in your API/service registration. For example, if accessing https://api.myservice.com/path/to/data/api, Power Query would expect your Awarding ID URL value to be equal to https://api.myservice.com.

The following Azure Active Directory customer IDs are used past Power Query. You might need to explicitly permit these customer IDs to access your service and API, depending on your overall Azure Active Directory settings.

Client ID Title Description
a672d62c-fc7b-4e81-a576-e60dc46e951d Ability Query for Excel Public customer, used in Power BI Desktop and Gateway.
b52893c8-bc2e-47fc-918b-77022b299bbc Power BI Data Refresh Confidential customer, used in Power BI service.
7ab7862c-4c57-491e-8a45-d52a7e023983 Power Apps and Ability Automate Confidential client, used in Ability Apps and Power Automate.

Y'all might need to explicitly allow these customer IDs to access your service and API, depending on your overall Azure Active Directory settings. Go to footstep 8 of Add a telescopic for more details.

If you demand more command over the OAuth flow (for example, if your service must respond with a 302 rather than a 401), or if your application's Application ID URL or Azure Advertizement Resource value don't match the URL of your service, so you'd need to utilize a custom connector. For more than information nearly using our built-in Azure Ad flow, get to Azure Agile Directory authentication.