Data Source Settings and Credentials: The Foundation of Reliable Data Preparation

When you begin any data project, your first instinct is likely to start building visualizations or performing complex statistical analysis. However, before a single chart can be rendered or a single model trained, you must establish a reliable, secure, and maintainable connection to your data. This is where data source settings and credentials come into play. While they might seem like administrative hurdles, they are actually the structural beams of your entire data architecture. If these settings are misconfigured, your reports will break the moment you share them, your scheduled refreshes will fail, and you may even inadvertently expose sensitive information.

Understanding data source settings involves more than just knowing where a file is stored or what your password is. It requires a deep dive into how different applications manage connections, how privacy levels prevent data leakage, and how to handle credentials in a way that is both secure and scalable. In this lesson, we will explore the nuances of connection management, the importance of privacy levels, and the best practices for handling credentials in professional environments. Whether you are working with local Excel files, cloud-based SQL databases, or complex web APIs, the principles we cover here will ensure that your data pipeline remains robust and secure.

Understanding the Scope: Global vs. Local Settings

Most modern data preparation tools, such as Power Query in Excel or Power BI, distinguish between global settings and file-specific settings. This distinction is critical for maintaining a clean workspace and ensuring that your individual projects do not conflict with one another. Global settings apply to every project you open on your machine, while local settings (often called "Current File" settings) apply only to the specific document you are currently working on.

Global settings typically handle things like your default privacy levels and cached credentials. For example, if you connect to a specific SQL server, your computer remembers those credentials globally so you don't have to re-enter them every time you start a new report. Local settings, on the other hand, allow you to override these defaults for a specific project. You might want a specific report to have stricter privacy rules than your default settings, or you might need to point a specific file to a different data gateway.

Callout: Global vs. Current File Settings

Global Settings are stored in the application's configuration on your local machine. They are "sticky" across all projects. Use these for settings that rarely change, like your preferred regional settings or your default data load behavior.

Current File Settings are saved within the metadata of the specific file (e.g., a .pbix or .xlsx file). Use these for project-specific requirements, such as enabling "Fast Combine" for a specific data mashup or setting a unique data locale for a specific client's dataset.

When troubleshooting connection issues, always check the Current File settings first. A common mistake is assuming that because a connection works in one file, it will work in another. If the second file has different local settings—perhaps a different privacy level or a different data source path—the connection will fail even if your global credentials are correct.

The Architecture of Credentials

Credentials are the "keys" that allow your data tool to unlock a data source. Depending on the source, these keys can take many forms. Understanding the different types of credentials is vital because choosing the wrong one can lead to security vulnerabilities or authentication failures when you try to publish your work to the cloud.

Common Credential Types

1. Anonymous Access: This is used for public data sources that do not require any login. A common example is a public website or a government data portal. While it is the easiest to set up, it is rarely used in corporate environments for internal data.
2. Windows Authentication: This uses your current Windows login tokens to access resources on your local machine or your organization's internal network. It is common for local SQL Server instances or files stored on a shared network drive.
3. Basic Authentication: This is the classic username and password combination. Many legacy databases and some web services still use this. While simple, it is less secure than modern methods because the credentials are often sent in a less protected format unless the connection is encrypted.
4. Web API Key: When connecting to web services (like a weather API or a marketing tool), you are often provided with a long string of characters called an API Key. This key identifies your account and grants access to the service without requiring a full username and password.
5. Organizational Account (OAuth2): This is the gold standard for cloud-based services like SharePoint, Dynamics 365, or Azure SQL. It uses a secure "handshake" process (OAuth2) where you log in through a standard Microsoft or third-party login screen. This method supports Multi-Factor Authentication (MFA), making it significantly more secure than Basic authentication.
6. Database Credentials: Specific to database engines like MySQL, PostgreSQL, or Oracle. These are separate from your Windows login and are managed by the database administrator.

Managing and Clearing Credentials

As you work on different projects, your machine will accumulate a library of stored credentials. Over time, passwords change, or you might accidentally enter the wrong information. Knowing how to manage this library is a key skill. In most data tools, you can access the "Data Source Settings" menu to view every source you have ever connected to.

From this menu, you can "Edit Permissions" to update a password or "Clear Permissions" to force the system to ask you for your login details again the next time you connect. Clearing permissions is often the first step in troubleshooting a "Login Failed" error that persists even after you are sure you are typing the password correctly.

Note: When you share a file with a colleague, your credentials are NOT shared with them. The file contains the "map" to the data (the data source settings), but the colleague will be prompted to provide their own "key" (credentials) the first time they try to refresh the data on their machine.

Privacy Levels: Preventing Data Leakage

One of the most misunderstood aspects of data source settings is Privacy Levels. This feature is designed to prevent a specific type of security breach: data leakage during a "mashup" operation. A mashup occurs when you take data from one source (like an internal SQL database) and use it to filter or query another source (like a public Web API).

Without privacy levels, the data engine might send a list of your private customer IDs to a public API to get their shipping status. If that API is malicious or insecure, your private data has just been "leaked" to the public internet. Privacy levels allow you to categorize your data sources so the engine knows what can be shared and what must remain isolated.

The Four Privacy Levels

• Public: Anyone can see this data. It is safe to use this data to filter or join with any other source. Examples include public websites or open-government data.
• Organizational: This data is private to your company but can be shared across internal sources. For example, you can join an Organizational SharePoint list with an Organizational SQL database.
• Private: This is the most restrictive level. Data from a Private source cannot be used in any operation that involves another data source, even an Organizational one. This is for highly sensitive data like HR records or financial secrets.
• None: This is the default state for new connections. The system will usually prompt you to select one of the other three levels before it allows a complex mashup to run.

The "Fast Combine" Setting

In the "Current File" settings, you will often find an option called "Ignore Privacy Levels and potentially improve performance." This is colloquially known as Fast Combine. While it can speed up your data processing by allowing the engine to optimize queries without worrying about privacy boundaries, it should be used with extreme caution. In a professional environment, you should generally keep privacy levels enabled and correctly configured to ensure data security. Only disable them if you are working with entirely non-sensitive data and are facing significant performance bottlenecks.

Working with Data Source Paths and Parameters

A frequent pain point in data preparation is the "Hardcoded Path" problem. This happens when you connect to a file on your local machine, such as C:\Users\YourName\Documents\Data.xlsx. When you send this file to a colleague, the refresh will fail because they don't have a folder named YourName.

To solve this, we use Data Source Settings to modify the paths or, even better, we use Parameters. Parameters allow you to turn a static file path or server name into a variable that can be easily changed without digging into the underlying code.

Step-by-Step: Creating a Parameterized Data Source

1. Define the Parameter: In your data preparation environment, look for the "Manage Parameters" option. Create a new parameter named FolderPath and set its type to Text. Enter your current local path as the default value.
2. Apply to Source: Open the "Source" step of your query. Instead of the quoted text string representing the path, replace it with the name of your parameter.
3. Update via UI: Now, if you move your data folder, you don't need to edit the query. You simply go to the "Edit Parameters" menu and paste the new path.

Example: M Code for Parameterized SQL Connection

In Power Query (the M language), a standard SQL connection looks like this:

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

To make this more flexible for moving between Development, Testing, and Production environments, you can use parameters:

let
    // 'pServer' and 'pDatabase' are parameters defined in the UI
    Source = Sql.Database(pServer, pDatabase),
    Navigation = Source{[Schema="dbo", Item="SalesData"]}[Data]
in
    Navigation

By using this approach, you can change the entire data source for your report by simply changing the parameter values. This is an industry standard for professional report development, as it allows for a smooth transition through the software development lifecycle (SDLC).

Connecting to Cloud Sources and On-Premises Gateways

When you move your data preparation from your local desktop to a cloud service (like the Power BI Service or Excel Online), the way credentials are handled changes significantly. Your desktop environment can access local files and internal servers because it sits behind your company's firewall. The cloud service, however, cannot "see" your internal network.

The Role of the Data Gateway

To bridge this gap, organizations use an On-Premises Data Gateway. The gateway acts as a secure messenger. When the cloud service needs to refresh data from an internal SQL server, it sends a request to the gateway. The gateway then uses the credentials stored within the gateway settings to access the data and send it back to the cloud.

Callout: Personal vs. Enterprise Gateways

Personal Gateways are meant for individual use. They run as an application on your specific computer. If your computer is turned off, the data cannot refresh.

Enterprise Gateways (Standard Mode) are installed on a dedicated server by an IT department. They support multiple users, multiple data sources, and are designed for 24/7 reliability. Always prefer the Enterprise Gateway for production-level reports.

Credential Mapping in the Cloud

When you publish a report, you must navigate to the dataset settings in the cloud portal to map your data sources to a gateway and provide credentials. Even if the credentials worked on your desktop, you must re-enter them in the cloud environment. This is a security feature; it ensures that the person publishing the report has the authority to grant the cloud service access to that specific data source.

Best Practices for Credential Security

Security should never be an afterthought. When managing data source settings and credentials, follow these industry-standard best practices to protect your organization's data.

1. The Principle of Least Privilege

Never use an "Admin" account to connect to a database for reporting purposes. Instead, use a dedicated "Service Account" that has "Read-Only" access to only the specific tables and views required for the report. If the credentials for the report are ever compromised, the attacker will be limited in what they can do.

2. Avoid Hardcoding Credentials

Never, under any circumstances, type a username or password directly into a query or a script (e.g., Password = "MySecret123"). Data preparation tools provide dedicated credential managers for a reason. Hardcoded credentials are saved in plain text within the file's metadata and can be easily stolen by anyone who has access to the file.

3. Use Organizational Accounts (OAuth2) Whenever Possible

If a data source supports "Organizational Account" login, use it. This method is more secure because it doesn't store your password. Instead, it stores a "token" that can be revoked at any time. It also allows your IT department to monitor access and enforce security policies like MFA.

4. Regularly Audit Data Source Settings

Over time, projects change. You might have old connections to servers that no longer exist or files that have been moved. Periodically open your "Global Data Source Settings" and clear out any sources that are no longer in use. This reduces your "attack surface" and keeps your environment clean.

Common Pitfalls and How to Avoid Them

Even experienced data professionals run into issues with data source settings. Being aware of these common mistakes will save you hours of frustration.

The "Path Not Found" Error

This usually happens when a file is moved or a network drive is re-mapped.

• The Fix: Use the "Data Source Settings" menu to change the file path globally for that source, or use parameters as discussed earlier to make the path dynamic.

The "Access to the Resource is Forbidden" Error

This error often occurs when your credentials are correct, but you don't have the specific permissions required to access the data, OR your privacy levels are conflicting.

• The Fix: Check your privacy levels first. If you are trying to merge a "Private" source with a "Public" source, the engine may block the request and throw a generic "Forbidden" error. If that's not the issue, contact your database administrator to verify your "Read" permissions.

Credentials Expiring in the Cloud

You might find that your report refreshes perfectly on your desktop but fails in the cloud after a few weeks.

• The Fix: This is often due to a password change policy. If you used your personal account for the cloud refresh, the refresh will fail the moment you change your Windows password. Solution: Use a dedicated Service Account with a non-expiring password (if permitted by IT policy) for cloud-based refreshes.

Firewall Blockage

Sometimes, your settings are perfect, but the connection still fails. This is common when trying to connect to a cloud database (like Azure SQL) from a local machine.

• The Fix: Ensure that your IP address is whitelisted in the data source's firewall settings. Many cloud databases block all incoming traffic by default unless specifically allowed.

Troubleshooting Step-by-Step

When a connection fails, follow this logical flow to identify the root cause:

1. Check Physical Connectivity: Can you ping the server? Can you open the file in its native application (e.g., Excel)? If the answer is no, the issue is not in your data tool; it's a network or file system issue.
2. Clear and Re-enter Credentials: Go to "Data Source Settings," clear the permissions for the problematic source, and try to connect again. This rules out cached, expired, or incorrect passwords.
3. Verify Privacy Levels: Set all sources in the current file to "Organizational" or "Public" as a test. If the query suddenly works, you have a privacy level conflict.
4. Test with a Simple Query: Instead of running your complex 50-step transformation, try to connect to the source and just "Select Top 10" rows. This determines if the issue is with the connection itself or a specific transformation step.
5. Check the Gateway (Cloud Only): If the failure only happens in the cloud, check the gateway status. Is the gateway online? Are the credentials stored in the gateway up to date?

Advanced Topic: Dynamic Data Sources and the "Hand-off"

In some advanced scenarios, you might need to connect to a data source where the name changes every day—for example, a daily CSV export named Sales_2023-10-27.csv. While you can use M code to calculate the filename, this can sometimes cause issues with "Dynamic Data Sources" in cloud environments.

Cloud services like Power BI Service often require "static" data source strings to perform a refresh. If your connection string is built entirely at runtime using variables, the service might not know which credentials to apply.

Tip: To avoid the "Dynamic Data Source" error in the cloud, ensure that the root of your connection is static. For example, connect to the folder (which is a static path) and then use your logic to filter for the specific file within that folder. This allows the cloud service to validate the credentials for the folder while still giving you the flexibility to pick different files.

Summary and Industry Perspective

In the world of data engineering and business intelligence, data source settings and credentials are often treated as "set it and forget it" tasks. However, as organizations grow and data security becomes more paramount, these settings become a central part of a professional's daily life. Managing these settings correctly is the difference between a "hobbyist" project and an "enterprise-grade" solution.

When you move into a professional role, you will likely spend significant time coordinating with IT departments to set up Service Accounts, configuring Gateways, and ensuring that your data mashups comply with corporate privacy policies. By mastering these concepts now, you are building the foundation for a career in data that is defined by reliability, security, and scalability.

Key Takeaways

• Distinguish between Global and Local settings: Use Global settings for machine-wide preferences and Local settings for project-specific overrides.
• Prioritize OAuth2 (Organizational Accounts): Whenever possible, use token-based authentication for cloud services to enhance security and support MFA.
• Understand Privacy Levels: These are not just administrative settings; they are a vital security feature designed to prevent sensitive data from leaking to public sources during mashups.
• Use Parameters for Paths and Servers: Avoid hardcoding file paths or server names. Using parameters makes your files portable and easier to maintain across different environments.
• Service Accounts are Essential: For production reports, move away from personal credentials and toward dedicated service accounts to prevent refresh failures when passwords change.
• Clear Credentials to Troubleshoot: Many "Login Failed" errors are caused by corrupted or expired cached credentials. Clearing permissions in the Data Source Settings menu is a standard first step in troubleshooting.
• Gateways Bridge the Gap: Understand that cloud services require an On-Premises Data Gateway to access data behind your local firewall, and credentials must be configured specifically within that gateway.
• Keep Paths Static for Cloud Refresh: To avoid dynamic data source errors in the cloud, ensure the base connection (like a folder or server) is static, even if the specific file or database is selected dynamically.