How to Add Data Source Credentials in Power BI

Connecting data to Power BI is your first step toward building great reports, but if you can't manage your credentials correctly, your reports will fail to refresh, and your data will quickly become stale. Setting up your data source credentials is a critical, one-time task that ensures Power BI has secure and ongoing permission to access your data, whether it’s in the cloud or on a local server. This guide will walk you through exactly how to add, edit, and manage credentials in both Power BI Desktop and the Power BI Service.

What Are Data Source Credentials and Why Do They Matter?

Simply put, data source credentials are the usernames, passwords, access tokens, or keys that Power BI uses to authenticate itself with a data source. Think of it as a key to a locked room, without the right key, Power BI can't get in to retrieve your data.

The process is slightly different depending on where you're working:

• In Power BI Desktop: When you connect to a data source, you provide a set of credentials. These are typically your own personal credentials and are stored securely on your local machine. This allows you to build and modify reports.
• In the Power BI Service: When you publish a report and set up a scheduled refresh, the Power BI cloud service needs its own set of credentials to access the data automatically. Without an automated way to log in, your scheduled refreshes will fail.

Managing these credentials correctly is fundamental for three reasons:

1. Security: It ensures that only authorized services (Power BI) and users can access sensitive company data.
2. Automation: It allows the Power BI Service to perform scheduled data refreshes, keeping your dashboards and reports up-to-date without any manual intervention.
3. Reliability: It prevents reports from failing due to authentication errors, making your data visualizations trustworthy and consistently available.

How to Manage Credentials in Power BI Desktop

In Power BI Desktop, you'll provide credentials the first time you connect to a new data source. However, you often need to update a password or change the account you're using. This is all done through the "Data source settings" dialog.

Finding the Data Source Settings

To access the controls for your project’s credentials, follow these steps:

1. Click on File in the top-left corner.
2. Go to Options and settings.
3. Select Data source settings.

Here, you'll see two key tabs:

• Data sources in current file: Lists only the data sources being used in the Power BI file you currently have open.
• Global permissions: Lists every data source you've ever connected to in Power BI Desktop on your machine, across all your projects.

Editing or Clearing Permissions

Imagine you connected to a company SQL server using your personal Windows credentials, but now your organization wants you to use a dedicated service account for reporting. Or perhaps you just changed your password. Here’s how you update it.

In the "Data source settings" window, find the data source you need to update and select it. You will see several options at the bottom:

Edit Permissions

This is what you'll use most often. Clicking this opens a new window where you can change the authentication method and update the credentials.

1. Select the data source (e.g., your SQL Server).
2. Click Edit Permissions....
3. A dialog box will appear. On the left, click Credentials and then click the Edit button.
4. You can now choose a different authentication method (like "Database" instead of "Windows") and enter a new username and password.
5. Click OK and then OK again to save the changes.

Clear Permissions

This option completely deletes the stored credentials for that data source. The next time you try to refresh your data from that source, Power BI will prompt you to enter credentials from scratch. This is useful if you want a clean slate or if you're experiencing connection issues and want to start over.

Setting up Credentials in the Power BI Service

Once you publish your report to the Power BI Service (app.powerbi.com), keeping the data fresh requires configuring scheduled refreshes. This is where you tell the cloud service how to securely log into your data sources on its own.

For Cloud-Based Data Sources (SharePoint, Azure SQL, etc.)

Connecting to cloud sources is often the easiest, as it typically uses a modern authentication method called OAuth2. In practice, this means you just log in with your Microsoft 365 or Azure account, and Power BI handles the rest securely.

Here’s how to set it up:

1. After publishing your PBIX file, navigate to the workspace where you published it.
2. Find your dataset in the list (it will have the same name as your file and an orange icon). Click the ellipsis (...) and select Settings.
3. Expand the Data source credentials section.
4. You'll likely see a warning like, "The credentials for this data source are invalid."
5. Click the Edit credentials link.
6. A pop-up window will appear. Choose an Authentication method like OAuth2 and a Privacy level (usually "Organizational" is a solid choice).
7. Click Sign in. This will redirect you to a standard Microsoft login page.
8. Enter your credentials and approve the permissions request.

Once you've signed in, the warning will disappear. You can now go to the "Scheduled refresh" section on the same page, enable it, and choose a frequency. Power BI Service now has what it needs to refresh the data automatically.

For On-Premises Data Sources (Local SQL Servers, Network Files)

What if your data isn't in the cloud? What if it's on a SQL server in your office or even just an Excel file on a shared network drive? For these situations, the Power BI Service cannot directly access your local network. You need a bridge, and that bridge is the On-Premises Data Gateway.

How the Gateway Handles Credentials

The gateway is a small piece of software you install on a server or computer that is always on and connected to your local network. It acts as a secure messenger:

• You enter your local data source credentials (like a Windows username and password) into the Power BI Service.
• Power BI encrypts these credentials and stores them securely in the cloud.
• When a refresh is scheduled, Power BI sends an encrypted request to the gateway.
• The gateway on your local network decrypts the credentials, uses them to log into your on-premises data source, retrieves the new data, and sends it back to Power BI Service in an encrypted fashion.

Your local password is never exposed to the public internet.

Setting Up Credentials for a Gateway Source

Assuming your IT team has already installed and configured a gateway, here's how you connect your dataset to it:

1. First, make sure the data source is added to the gateway. Go to the Settings icon ⚙️ in the top right of the Power BI Service and click Manage connections and gateways.
2. You will manage the connection to the source data on the gateway's data page. Then you map your report’s dataset to this connection.
3. On the dataset, an expandable panel for "gateway and cloud connections" should display data sources which need defining a gateway for.
4. If not already mapped, expand the data source row, and select to add to a gateway. Following that, select your configured gateway from the dropdown and Apply.

Once you've mapped your dataset to use the gateway connection, the data source credentials section will show as valid, and you can enable scheduled refreshes just like you would for a cloud source.

Best Practices for Credential Management

To avoid common issues and keep your data secure, follow these simple guidelines:

• Use Service Accounts: For gateways, avoid using your personal user account. If you change your password or leave the company, all your reports will fail. Instead, use a dedicated "service account" with a strong, non-expiring password.
• Apply the Principle of Least Privilege: The account used for refreshing data should only have the minimum permissions necessary. If the report is read-only, give the service account only read-only permissions on the database.
• Clean Up Global Permissions: Periodically review the "Global permissions" list in Power BI Desktop and remove entries for old servers or sources you no longer use.
• Prefer OAuth2 for Cloud Sources: Whenever possible, use organizational accounts (OAuth2) for cloud-to-cloud connections. It's more secure and easier to manage than managing individual API keys or passwords.

Final Thoughts

Properly managing your data source credentials is the foundation of reliable and secure Power BI reporting. By understanding the difference between Desktop and Service workflows and knowing how to use the On-Premises Data Gateway for local data, you can ensure your reports always have access to the fresh, accurate data stakeholders depend on.

With a dozen different marketing and sales platforms, just managing passwords can feel like a job in itself. Onboarding a new tool often means hunting down API keys and dealing with complex authentication setups before you even get to the data. We simplify this entirely. With Graphed you use one-click integrations to connect all your data sources like Google Analytics, HubSpot, and Shopify. There are no API keys to track down or credentials to manage for each refresh - just a secure, streamlined connection so you can get answers from your dashboards and reports in seconds.