Enter client credentials in Power BI or Excel

Azure DevOps Services | Azure DevOps Server 2019

When connecting from Power BI or Excel to the Analytics Service using the OData feed, choose Azure Active Directory (Azure AD) for the most secure option. The next secure option is the use of Personal Access Tokens (PATs).

When connecting from Power BI or Excel to the Analytics Service using the OData feed, choose Windows credentials as the most secure option. The next secure option is the use of Personal Access Tokens (PATs).

Note

The Analytics Service is generally available for all organizations using Azure DevOps Services. It provides several advanced widgets. Power BI integration and access to the OData feed of the Analytics Service is in Preview. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Note

The Analytics Service is in preview for Azure DevOps Server 2019. While in preview, it is available to everyone free of charge. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

You access the Analytics Service by installing the Analytics Marketplace extension. Installing it provides several advanced widgets, Power BI integration, and access to the OData feed.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Why use a Personal Access Token over an Alternate Access Credential?

For these three reasons:

  • As part of good password management, these tokens automatically expire so you don't have to remember to change your password
  • Since PATs are a generated password, you won't use a common password as part of your AAC, which means a more limited surface area for attack
  • If you need to provide a diagnostic trace and forget to replace the value, you can simply revoke the credential thereby maintaining secure access to Azure DevOps.

Azure Active Directory (Azure AD)

Azure Active Directory is also known as "Organizational Credential" in Power BI Desktop. You can use it in PowerBI.com through OAuth selection when configuring data refresh.

  1. When connecting with Power BI Desktop you will be prompted to enter your credentials for the data source. Click Organizational account as shown here:

    Organizational account prompt

  2. Choose Sign in and enter your Azure AD credentials. After that, you're done.

To change an existing URL to use Azure AD instead of a PAT or AAC, see Change authentication credentials later in this article.

Windows

You can use Windows authentication in PowerBI.com through OAuth selection when configuring data refresh.

  1. When connecting with Power BI Desktop you will be prompted to enter your credentials for the data source. Click Windows as shown here:

    Windows authentication prompt

  2. Enter your Windows credentials and choose Connect.

To change an existing URL to use Windows instead of a PAT, see Change authentication credentials later in this article.

Create a Personal Access Token

  1. Sign in to Azure DevOps Services, open your user profile menu, and select Security.

    User Profile, My Security menu option

  2. Enter the required information:

    • Add a description
    • Set the expiration period
    • Select the organization
    • Choose Selected scopes
    • Check the Analytics (read)

      And, then choose Create Token.

      Create personal access token

  3. Copy the token that displays. If you navigate away from the page, you won't be able to retrieve it later.

    Copy Personal Access Token dialog

  4. If you need to revoke a token, simply navigate to this page and choose the Revoke link. That token will immediately be denied access to Azure DevOps Services.

    These tokens are your identity. When used, the token is acting as you. Keep your tokens secret and treat them like your password. To help keep your token more secure, consider using credential managers so that you don't have to enter your credentials every time you push.

Enter credentials within a client

Both Power BI and Excel work using Power Query when accessing OData, therefore the authentication mechanism, including the various authentication screens are identical. This walkthrough is done using Power BI Desktop, however it also applies to Excel.

  1. Open Power BI Desktop.

  2. Choose Get Data.

  3. Click OData Feed (or More>Other>OData Feed).

  4. Enter the URL for the OData endpoint and choose OK.

    OData Feed URL

    OData Feed URL

  5. In the authentication dialog, choose Basic, enter your credentials, and then choose Connect:

    • If you are using an AAC, enter your username and password
    • If you are using a PAT, enter a text string, such as "test" or "user" for the username and enter the token in the password field.

      Authentication information

      Authentication information

Change authentication credentials

If you receive an access denied message, you may need to change your authentication credentials. Power Query caches your credential information so you only have to enter it once. However, Personal Access Tokens expire and you may need to update or change your authentication information.

Update credentials in Power BI Desktop

  1. Open Power BI Desktop.

  2. Click File > Options and Settings > Data Source Settings. Select the correct URL to the OData Feed and click Edit.

    Data source settings

    Data source settings

  3. In the next Data Source Settings dialog, click the Edit button under the Credentials setting.

    Data source settings, 2nd dialog

    Data source settings, 2nd dialog

  4. Edit the credentials as noted above, click Save, then Done, then Close.

Update credentials in Excel

  1. Open Excel and click the Data tab.

  2. Click New Query > Data Source Settings...

    Excel, Open Data source settings

  3. Select the correct URL to the OData Feed and click Edit.

    Data source settings

    Data source settings

  4. In the next Data Source Settings dialog, click the Edit button under the Credentials setting.

    Data source settings, 2nd dialog

    Data source settings, 2nd dialog

  5. Edit the credentials as noted above, click Save, then Done, then Close.

Publish to PowerBI.com

After you have created a model and loaded it with data you can publish it to Power BI. When using Azure AD or Windows, make sure to select the oAuth2 option.