Managing Data Source Credentials and Connection Settings in Microsoft Power BI

Course: Microsoft Power BI Training

Topic: Managing Data Source Credentials and Connection Settings

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Data Source Credentials and Connection Settings

Microsoft Power BI connects to different data sources, including Excel workbooks, SQL databases, cloud applications, websites, APIs, and organizational data platforms. To retrieve information successfully, Power BI must know how to connect to each source and, when required, which credentials to use.

Managing data source credentials and connection settings is an important part of building secure, reliable, and maintainable Power BI reports. Incorrect credentials, outdated connection details, or unsupported authentication methods can prevent data from loading or refreshing.

Data source management includes configuring server names, database names, file paths, authentication methods, privacy levels, gateway connections, and refresh-related settings.

1.1 What Are Data Source Credentials?

Data source credentials are authentication details or identity information used to establish access to a data source. They help the source determine whether the user or application has permission to retrieve the requested data.

Examples include:

• Username and password for a supported database connection.
• Organizational account credentials for Microsoft services.
• OAuth access tokens obtained through an authorization process.
• API keys for services that support API key authentication.
• Windows credentials for supported on-premises connections.
• Anonymous access for resources that do not require authentication.

1.2 What Are Connection Settings?

Connection settings define how Power BI identifies and accesses a data source. They can include the server address, database name, file path, URL, authentication method, connection type, privacy level, and gateway mapping.

For example, a SQL Server connection may require a server name, database name, authentication method, and account with permission to access the database.

1.3 Why Is Credential Management Important?

• Security: Helps protect confidential business information.
• Reliable refresh: Reduces failures caused by expired or invalid credentials.
• Access control: Ensures that connections use authorized identities.
• Automation: Supports scheduled data refresh when the source and authentication method permit it.
• Maintenance: Makes it easier to update passwords, URLs, and connection details.
• Governance: Helps organizations control access to sensitive datasets.
• Collaboration: Allows authorized team members to work with appropriately configured shared connections.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand credentials, permissions, and connection settings.
• Identify common Power BI authentication methods.
• Manage credentials in Power BI Desktop.
• Understand global permissions and current-file data source settings.
• Configure credentials in Power BI Service.
• Understand cloud connections and on-premises data gateways.
• Configure data source privacy levels.
• Recognize the difference between Import and DirectQuery connectivity.
• Troubleshoot credential and connection errors.
• Apply security and data governance best practices.

3. Understanding Data Source Connections in Power BI

A Power BI report can use one or more data sources. Each source may have its own connection details, authentication requirements, and refresh behavior.

3.1 Common Data Sources

3.2 Components of a Connection

• Source identifier: Identifies the source, such as a URL, server, database, or file path.
• Authentication method: Specifies how access is established.
• Credentials: Supplies the required authentication information.
• Permissions: Determines what the authenticated identity can access.
• Privacy level: Controls how Power Query treats data when combining sources.
• Gateway or cloud connection: Provides the supported route to the data source.
• Refresh configuration: Determines when and how updated data is retrieved.

4. Understanding Authentication Methods

Power BI supports different authentication methods depending on the connector and data source. The correct method must match the security requirements of the source.

4.1 Anonymous Authentication

Anonymous access is used when a data source permits requests without credentials.

Example: A publicly accessible CSV file that does not require sign-in.

Anonymous access should only be used when the resource explicitly permits it. It should not be selected merely to bypass an authentication error.

4.2 Username and Password Authentication

Some data sources support a username and password or database-specific credentials. Power BI uses these details to authenticate with the source.

Example: A database connection that supports SQL authentication.

Use strong passwords, apply least-privilege access, and update stored credentials when the password changes.

4.3 Windows Authentication

Windows authentication uses a Windows identity to access supported sources, commonly within an organizational network.

Example: A SQL Server database configured to use Windows authentication.

When using an on-premises gateway, ensure that the configured identity has the necessary permissions on the gateway machine and the target data source.

4.4 Organizational Account Authentication

Organizational account authentication allows users to sign in with a supported work or school identity. It is commonly used with Microsoft cloud services and organizational data sources.

The exact options available depend on the connector, tenant configuration, and source service.

4.5 OAuth Authentication

OAuth is an authorization framework that allows an application to access resources using access tokens. Power BI connectors may support OAuth-based authentication for suitable services.

Tokens can expire or require renewal. A report that works initially may fail to refresh if the authentication flow or token renewal is not supported in the target environment.

4.6 API Key Authentication

An API key is a credential issued by a service provider to identify or authorize an application. The key may be supplied through a supported credential field, request header, or query parameter, depending on the API.

API keys must be protected. Avoid hardcoding sensitive keys into shared query text, publishing them in public repositories, or exposing them in reports.

4.7 Authentication Comparison

5. Managing Data Source Settings in Power BI Desktop

Power BI Desktop provides a Data source settings window for reviewing connection information and managing credentials and permissions.

5.1 Opening Data Source Settings

1. Open Power BI Desktop.
2. Open the report that contains the relevant data source.
3. Select the File menu.
4. Choose Options and settings.
5. Select Data source settings.
6. Review the listed data sources.
7. Select the source you want to manage.

Depending on the Power BI Desktop version, related data source settings may also be available through Power Query Editor.

5.2 Current File Data Sources

The current-file option displays data sources used by the report or file. It is useful when a developer needs to review or update the permissions and connection details associated with a particular report.

Common tasks include:

• Reviewing the source used by a query.
• Editing supported source connection details.
• Updating authentication credentials.
• Changing privacy settings.
• Clearing permissions for a selected source when necessary.

5.3 Global Permissions

Global permissions apply to stored data source permissions beyond a single current report, as supported by Power BI Desktop and its data connectors.

They are useful when the same source is accessed from multiple reports and the developer wants to manage the stored permissions centrally on that computer.

Important: Global permissions in Power BI Desktop are not the same as permissions in Power BI Service. Changing credentials locally does not automatically update every published semantic model or cloud connection.

6. Editing Data Source Credentials

When a password changes, a token expires, or the wrong authentication method is selected, the stored credentials may need to be updated.

6.1 Step-by-Step Process

1. Open Power BI Desktop.
2. Navigate to File > Options and settings > Data source settings.
3. Select the relevant data source.
4. Choose the option to edit permissions or credentials, as available for that source.
5. Select the correct authentication method.
6. Enter the required credentials or sign in again.
7. Review the privacy level if applicable.
8. Save the changes.
9. Refresh the report to test the connection.
10. Verify that the query completes without authentication errors.

6.2 Example: Updating SQL Server Credentials

Suppose a database password has changed and a Power BI report can no longer retrieve data.

1. Open Data source settings.
2. Identify the SQL Server source.
3. Open the permissions or credential settings.
4. Choose the authentication method required by the database.
5. Enter the updated credentials.
6. Confirm that the account has access to the required database and tables.
7. Save the settings.
8. Refresh the report and check the result.

6.3 When Should Credentials Be Cleared?

Clearing stored permissions can be useful when Power BI repeatedly uses an incorrect identity or when a developer needs to establish the connection again.

Before clearing permissions, confirm which reports or queries depend on the source. Clearing stored permissions removes the saved authorization information, so you may need to authenticate again.

7. Changing Connection Settings

Connection settings identify the resource that Power BI accesses. They may need to be updated when a database server changes, a file is moved, or a web endpoint is replaced.

7.1 Common Connection Details

• Server name or IP address.
• Database name.
• File path or folder location.
• Website URL or API endpoint.
• Cloud storage location.
• Authentication method.
• Connection or gateway mapping.

7.2 Example: Changing a Server Name

Suppose an organization migrates a database from SQLSERVER01 to SQLSERVER02.

1. Open the report in Power BI Desktop.
2. Open Transform Data.
3. Identify the query connected to the old server.
4. Open the source step or Data source settings.
5. Update the server name where appropriate.
6. Verify the database name and authentication method.
7. Apply the changes and refresh the query.
8. Confirm that the report uses the correct source.
9. If published, update the corresponding cloud or gateway connection configuration as necessary.

Changing the connection in Desktop does not automatically change every connection configured in Power BI Service. The published model and gateway mapping must also be reviewed.

7.3 Using Parameters for Connection Management

Power Query parameters can store configurable values such as server names, database names, or supported environment URLs. They can help manage development, testing, and production environments.

For example, a parameter named ServerName could contain the server used by a query.

let
    Source = Sql.Database(
        ServerName,
        "SalesDatabase"
    )
in
    Source

This example assumes that ServerName is an existing Power Query parameter. The database and authentication details must be configured appropriately for the actual environment.

8. Managing Credentials in Power BI Service

Power BI Service is the online platform where users publish reports, manage semantic models, share dashboards, and configure data refresh. Credentials may need to be configured again after publishing.

8.1 Accessing Semantic Model Settings

1. Sign in to Power BI Service.
2. Open the workspace containing the published report.
3. Locate the semantic model associated with the report.
4. Open its settings.
5. Find the data source credentials or connection configuration section available for that model.
6. Review the source settings and authentication requirements.
7. Enter or update credentials where required.
8. Save the configuration and test refresh.

8.2 Why Must Credentials Be Configured Again?

Power BI Desktop and Power BI Service operate in different environments. Credentials stored on a developer's computer do not necessarily transfer as reusable credentials to the online service.

After publishing, the service must have a supported way to authenticate with the data source. This may involve service-managed credentials, a cloud connection, OAuth sign-in, or a configured gateway.

8.3 Important Considerations

• Confirm that the published semantic model points to the intended source.
• Check that the authentication method is supported by the service.
• Verify that credentials are valid and have sufficient permissions.
• Configure a gateway for supported sources that require one.
• Check whether the source URL or server name matches the gateway configuration.
• Run a test refresh and review the refresh history.

9. Understanding On-Premises Data Gateways

An on-premises data gateway provides a secure connection between supported data sources located within an organization's network and Power BI Service.

For example, a company may store its sales data on a SQL Server machine inside its office network. Power BI Service cannot necessarily access that server directly. A correctly configured gateway can enable supported queries and refresh operations.

9.1 Types of Gateway

Gateway availability and capabilities depend on the source, environment, and organizational configuration.

9.2 Configuring a Gateway Data Source

1. Install and configure the appropriate gateway when required.
2. Open Power BI Service.
3. Go to Settings > Manage connections and gateways.
4. Create a new connection or data source in the appropriate gateway configuration.
5. Select the correct data source type.
6. Enter the server, database, or other required source details.
7. Choose a supported authentication method.
8. Enter the credentials and configure any required privacy settings.
9. Save the connection.
10. Map the semantic model to the configured gateway connection.
11. Test the connection and refresh the model.

9.3 Matching Server and Database Names

The source details in Power BI Desktop must match the corresponding gateway data source details for Power BI to map them correctly.

For example, if the report uses an IP address as the server name, the gateway configuration should use the same server identifier. Likewise, differences in server instance names or database names can prevent the model from matching the gateway source.

10. Cloud Connections and Shared Connections

Cloud data sources may be accessible directly from Power BI Service without an on-premises gateway. The required configuration depends on the connector and the network accessibility of the source.

10.1 Personal Cloud Connections

A personal cloud connection is associated with an individual user's configuration. It can provide the connection information and credentials required by supported cloud data sources.

10.2 Shareable Cloud Connections

Power BI also supports shareable cloud connections for eligible scenarios. These connections allow authorized users to reuse centrally managed connection settings rather than configuring a separate personal connection for every supported artifact.

10.3 Creating a Cloud Connection

1. Open Power BI Service.
2. Select Settings > Manage connections and gateways.
3. Open the Connections area.
4. Select the option to create a new connection.
5. Choose the cloud connection type when appropriate.
6. Enter the connection details and authentication information.
7. Save the connection.
8. Assign appropriate permissions if the connection will be shared.
9. Map the supported semantic model or other eligible artifact to the connection.
10. Test the connection and refresh behavior.

10.4 Sharing Connections Securely

• Share connections only with trusted users or groups.
• Assign the minimum permission level required.
• Understand that sharing a connection can allow authorized users to use its configured credentials.
• Review the data access permissions of the identity used by the connection.
• Remove access when it is no longer needed.

11. Understanding Data Source Privacy Levels

Power Query privacy levels help control how data from different sources can be combined. They are designed to reduce the risk of unintentionally transferring sensitive information between sources during query evaluation.

11.1 Common Privacy Levels

11.2 Why Privacy Levels Matter

Suppose a Power BI query combines a private customer dataset with a public web API. Privacy settings help determine how Power Query handles the interaction between the two sources and whether information from one source can influence requests to another.

Incorrect privacy settings may result in errors, restrictions, or unintended data exposure risks.

11.3 Configuring a Privacy Level

1. Open Power BI Desktop.
2. Navigate to File > Options and settings > Data source settings.
3. Select the required data source.
4. Open the permissions or privacy configuration.
5. Select the privacy level that accurately reflects the source.
6. Save the changes.
7. Refresh the query and check the results.

Best practice: Never mark a source as Public simply to bypass a privacy-related error. Select a level based on the actual sensitivity and intended use of the data.

12. Import, DirectQuery, and Connection Behavior

Power BI supports different data connectivity modes. The mode influences when data is retrieved, how queries are processed, and which refresh or connection settings are needed.

Not every data source supports every connectivity mode. Available options depend on the connector and source capabilities.

13. Scheduled Refresh and Credential Maintenance

Scheduled refresh allows eligible Power BI semantic models to retrieve updated data at configured times. Successful refresh requires working credentials, source accessibility, and a supported connection configuration.

13.1 Configuring Scheduled Refresh

1. Publish the report to Power BI Service.
2. Open the workspace containing the semantic model.
3. Open the semantic model settings.
4. Configure the data source credentials.
5. Configure the gateway or cloud connection when required.
6. Open the refresh settings.
7. Enable and configure scheduled refresh if supported.
8. Save the schedule.
9. Run a manual test refresh.
10. Review the refresh history to verify success.

13.2 Reasons Scheduled Refresh Can Fail

• The password has changed.
• The authentication token has expired.
• The data source is unavailable.
• The gateway is offline or incorrectly configured.
• The gateway source name does not match the model's connection details.
• The API rate limit has been exceeded.
• The source requires an unsupported authentication method.
• The network or firewall blocks the connection.
• The account no longer has permission to access the source.

14. Troubleshooting Credential and Connection Errors

When Power BI cannot connect to a source, begin by identifying whether the issue concerns authentication, connectivity, permissions, privacy, or refresh compatibility.

14.1 A Step-by-Step Troubleshooting Process

1. Read the full Power BI error message.
2. Identify the affected data source and query.
3. Confirm that the server, URL, database, or file path is correct.
4. Test whether the source is accessible outside Power BI using an approved method.
5. Check the authentication method and credentials.
6. Confirm that the account has the required permissions.
7. Review privacy settings and gateway mapping.
8. Refresh the query in Power BI Desktop.
9. If the report is published, test refresh in Power BI Service.
10. Document the cause and corrective action.

15. Security Best Practices

Credential management is a critical part of Power BI security. Improperly shared credentials can expose sensitive business information or allow unauthorized access.

• Use the least-privilege principle when creating source accounts.
• Never include passwords or secret API keys in report visuals.
• Avoid storing secrets in shared plain-text files or public repositories.
• Use supported credential storage and authentication mechanisms.
• Follow organizational password and token rotation policies.
• Restrict access to gateway connections and shared cloud connections.
• Review connection owners and users periodically.
• Use appropriate privacy levels for all data sources.
• Verify that the connection uses the correct organizational identity.
• Remove unnecessary access when team members change roles.
• Monitor refresh history for repeated authentication failures.
• Follow organizational requirements for confidential and regulated data.

16. Practical Project: Configure and Test a Power BI Connection

Project Objective: Connect Power BI to a data source, configure its credentials, test the connection, and verify refresh behavior.

16.1 Requirements

• Power BI Desktop.
• A practice Excel file or authorized database.
• Access to the chosen source.
• Power BI Service access for the publishing and refresh exercises, if available.

16.2 Project Steps

1. Open Power BI Desktop.
2. Import an Excel file or connect to an authorized database.
3. Review the source details in Data source settings.
4. Inspect the current authentication method.
5. Verify that the account has the required access.
6. Load and transform the data.
7. Create a simple report visual.
8. Refresh the report to test the connection.
9. Publish the report if Power BI Service is available.
10. Configure the credentials and gateway or cloud connection as required.
11. Test refresh in the service.
12. Record the connection settings, authentication method, and test result without documenting secret credentials.

16.3 Expected Outcome

The learner should be able to explain how Power BI identifies a data source, how credentials are managed, why connection settings may differ between Desktop and Service, and how to troubleshoot a failed refresh.

17. Practice Exercises

Exercise 1: Manage Credentials

• Connect to an authorized practice data source.
• Open Data source settings.
• Identify the authentication method.
• Review the available credential and permission options.
• Refresh the report to verify connectivity.

Exercise 2: Change a Connection Setting

• Create a practice query using a configurable server, URL, or file path.
• Update the source location.
• Verify the source's permissions.
• Refresh the query and record the outcome.

Exercise 3: Configure Service Refresh

• Publish a practice report.
• Review the semantic model settings.
• Configure supported source credentials.
• Set up a gateway if required.
• Run a test refresh and review the history.

Exercise 4: Troubleshoot an Authentication Error

• Review a sample invalid-credential error in a practice environment.
• Identify whether the problem involves credentials or permissions.
• Correct the connection using the approved authentication method.
• Verify the result without sharing secret information.

18. Interview Questions and Answers

Q1. What are data source credentials in Power BI?

Answer: They are authentication details or identity information used by Power BI to access a data source.

Q2. What are connection settings?

Answer: Connection settings define how Power BI identifies and accesses a source, including server names, URLs, file paths, authentication methods, and connection mappings.

Q3. Where can you manage credentials in Power BI Desktop?

Answer: Open File > Options and settings > Data source settings, select the source, and review the available permissions and credential options.

Q4. What is the difference between current-file settings and global permissions?

Answer: Current-file settings relate to data sources used by the open report, while global permissions manage stored source permissions more broadly on the local Power BI Desktop environment.

Q5. Why might credentials need to be configured after publishing?

Answer: Credentials stored on a local computer do not necessarily provide reusable authentication in Power BI Service. The service must have a supported connection and authentication configuration.

Q6. What is an on-premises data gateway?

Answer: It enables supported cloud services such as Power BI Service to access data sources within an organization's network.

Q7. What is the purpose of a privacy level?

Answer: Privacy levels help Power Query manage how data from different sources can be combined and reduce the risk of unintended information transfer.

Q8. What is the difference between Import and DirectQuery?

Answer: Import loads data into the semantic model, while DirectQuery sends supported queries to the underlying source rather than importing the full dataset in the same way.

Q9. Why does a scheduled refresh fail?

Answer: Common causes include invalid credentials, expired tokens, source unavailability, gateway errors, permission problems, or unsupported authentication methods.

Q10. What is a cloud connection?

Answer: A cloud connection stores supported connection settings for accessing a cloud data source. Eligible shareable connections can be used by authorized users and supported artifacts.

Q11. How should API keys be managed?

Answer: Store and use API keys through supported secure mechanisms, limit access, avoid publishing secrets, and rotate them according to security policies.

Q12. What should you check when a gateway connection fails?

Answer: Check gateway status, server and database names, source credentials, network connectivity, source permissions, and the mapping between the semantic model and gateway connection.

Q13. Can multiple reports use the same connection?

Answer: Depending on the connection type and permissions, multiple reports or semantic models can use an appropriate shared or gateway connection.

Q14. Why is least-privilege access important?

Answer: It limits the data and operations available to a connection identity, reducing the impact of accidental exposure or unauthorized use.

Q15. What should you do after updating credentials?

Answer: Save the settings, refresh the data, verify the result, and confirm that any published semantic model uses the correct service-side credentials.

19. Quick Revision Checklist

• Understand credentials and connection settings.
• Identify authentication methods supported by each source.
• Open Data source settings in Power BI Desktop.
• Understand current-file settings and global permissions.
• Update credentials when they become invalid.
• Change server names, URLs, and file paths carefully.
• Configure Power BI Service credentials after publishing.
• Understand gateway and cloud connection configuration.
• Set suitable data source privacy levels.
• Understand Import, DirectQuery, and live connection behavior.
• Test scheduled refresh and inspect refresh history.
• Protect credentials and follow least-privilege principles.

20. Official Learning Resources

• Microsoft Learn: Power Query Connectors — information about supported connectors and their capabilities.
• Microsoft Support: Manage Data Source Settings and Permissions — guidance on credentials, permissions, and privacy levels.
• Microsoft Learn: Manage Gateway Data Sources — configure gateway connections, authentication, and data source access.
• Microsoft Learn: Connect to Cloud Data Sources — cloud connections, shared connection settings, and permissions.
• Microsoft Learn: Refresh Data in Power BI — refresh configuration and troubleshooting information.

21. Conclusion

Managing data source credentials and connection settings is essential for maintaining secure and reliable Power BI reports. Correct configuration helps Power BI access the intended sources, authenticate successfully, protect confidential information, and refresh data as required.

By understanding authentication methods, Desktop permissions, Power BI Service settings, gateway connections, cloud connections, privacy levels, and refresh behavior, learners can diagnose common connection problems and build more maintainable reporting solutions.

Explore the Course: Microsoft Power BI Training at JustAcademy

Register for a Demo: Register for a Course Demo

Visit JustAcademy: Official Website