Credentials & Privacy Levels — Set credentials and privacy levels appropriately (conceptual)

Historical Context & Motivation

The challenge of securely connecting analytical tools to heterogeneous data sources is as old as the client-server model itself. When organizations first began pulling data from remote databases in the 1990s, ODBC drivers required users to embed plaintext credentials directly into connection strings—an approach that, while functional, created serious security vulnerabilities. As data warehousing matured and self-service BI platforms emerged in the 2000s and 2010s, the problem intensified: analysts now routinely combine data from cloud APIs, on-premises databases, flat files, and SaaS platforms within a single report, each source carrying its own authentication scheme and sensitivity classification.

Microsoft Power BI, released in 2015 as a unified self-service analytics platform, inherited this complexity and introduced two complementary abstractions to manage it: data source credentials and privacy levels. Credentials govern who can authenticate against a data source and under what identity context. Privacy levels govern how the mashup engine may combine data from different sources, preventing inadvertent information leakage. Together, these mechanisms enforce the principle of least privilege while enabling flexible data integration—a design heavily influenced by earlier work in the Power Query (M) language engine and the on-premises data gateway architecture.

The fundamental question this lesson addresses is: How do you configure credentials and privacy levels in Power BI so that data connections are both secure and functionally correct, preventing unauthorized access on one hand and unintended data leakage during query evaluation on the other? Understanding these mechanisms conceptually is essential before you ever open the Data Source Settings dialog, because misconfiguration can silently produce incomplete query results or expose sensitive records across trust boundaries.

Core Principles & Definitions

Before diving into configuration details, it is important to establish the foundational concepts that underpin Power BI's approach to secure data connectivity. The system distinguishes between two orthogonal concerns: authentication (proving identity to a data source) and data isolation (controlling how data from different sources may be combined during query evaluation). These two concerns map directly onto credentials and privacy levels, respectively.

Visual Explanation — Credential & Privacy Architecture

The visual above clarifies a common source of confusion: credentials and privacy levels operate at different layers and serve fundamentally different purposes. Credentials are negotiated at connection time between the Power Query engine and the external data source—they are about identity and access. Privacy levels, on the other hand, are enforced at query evaluation time within the mashup engine—they are about data isolation and information flow control. You can think of the mashup engine as an intermediary that first authenticates to each source using the configured credentials, then checks privacy-level compatibility before combining any data. If the privacy levels are incompatible (for example, merging a Private source's values into a query sent to a Public source), the engine raises a Formula.Firewall error, which prevents the query from completing.

How Credentials & Privacy Levels Work

Credential Resolution Process

When Power Query encounters a data access function such as Sql.Database("server", "db") or Web.Contents("https://api.example.com"), it derives a data source path from the function's arguments. This path serves as the lookup key into the credential store. The engine searches for a stored credential whose scope matches or is a prefix of the derived path. If no credential is found, Power BI prompts the user to supply one. Critically, the path determines credential granularity: connecting to Sql.Database("server1", "Sales") and Sql.Database("server1", "HR") may share a single server-level credential or require separate database-level credentials, depending on how the user has scoped them.

Authentication Method Taxonomy

Privacy Level Firewall Logic

The Power Query firewall partitions all data source references in a query into firewall partitions based on their data source paths. Each partition is assigned the privacy level configured for that source. Before the engine sends any data from one partition into a query targeting another partition, it checks a compatibility matrix. The core rule can be expressed concisely: data may flow from a less-restrictive level into a more-restrictive context, but never from a more-restrictive level into a less-restrictive one. Formally, if we assign ordinal values—Private = 3, Organizational = 2, Public = 1—then a merge of source A into source B is permitted only if level(A) ≤ level(B) or if both sources share the same partition. When the condition is violated, the engine blocks the query with a Formula.Firewall error.

Privacy Level Compatibility Matrix & Classification

The interaction between privacy levels is best understood through a compatibility matrix. When Power Query evaluates a query that combines two data sources, it looks up their assigned privacy levels and determines whether the combination is allowed, buffered (data is pulled locally first to prevent query folding across sources), or blocked. The following table and diagram make this explicit.

Several important subtleties emerge from this matrix. First, Private sources are completely isolated: they can never share data with any other source, and even merging two Private sources from different paths is blocked. This is by design—Private is intended for sources containing highly sensitive data such as employee salaries or patient records, where no cross-source leakage is tolerable. Second, Organizational sources can merge with other Organizational sources and can receive data from Public sources, but cannot send data to Public sources. This models the typical enterprise scenario where internal data can incorporate public reference data but should not be exposed to external APIs. Third, Public sources are the most permissive: they can be merged with anything except Private.

Worked Example — Configuring a Multi-Source Report

Consider the following scenario: you are building a Power BI report that combines employee data from an on-premises SQL Server database with exchange rate data from a public REST API and departmental budget information from a SharePoint Online list. Each source has different authentication requirements and data sensitivity. Let us walk through the correct configuration.

Strengths, Limitations & Common Pitfalls

Connection to Advanced Architecture & Future Directions

The conceptual framework of credentials and privacy levels in Power BI Desktop is the starting point for a broader set of enterprise-grade data governance capabilities. As you move from self-service Desktop reports to production-grade deployments in the Power BI Service and Microsoft Fabric, several advanced mechanisms build upon these foundations.

As you progress into data engineering and enterprise BI architecture, you will encounter scenarios where the simple three-tier privacy model is insufficient—for example, when a single lakehouse contains data at multiple classification levels, or when row-level security (RLS) must interact with privacy levels. Microsoft Fabric's unified architecture is beginning to address these gaps by introducing workspace-level trust boundaries and managed VNet gateways that enforce network isolation in addition to logical privacy levels. Understanding the foundational concepts in this lesson will make these advanced patterns much more tractable.

Practice Problems

Lesson Summary