Connecting Power BI to SQL Server: A Step-by-Step Guide

SQL Server is one of the most common sources behind Power BI reports, and connecting to it takes a minute. Connecting to it well takes a little more thought: what to query, how to authenticate, how to keep Power Query's steps running inside the database, and how the report will refresh once published. This guide goes through each step, using the Chinook sample database, which is published with a SQL Server script (Chinook on GitHub).

Before you start

Collect four things:

• The server name, including any instance name, such as sqlprod01\reporting.
• The database name.
• Credentials. The connector supports database logins, Windows authentication, organisational accounts where the server supports Microsoft Entra ID, and service principals (Microsoft Learn: SQL Server connector).
• Network access from your machine, and later from the gateway, to the server's port.

Ask for a read-only login limited to the data the report needs. A reporting account that can change data is a risk nobody needs.

Step 1: Give Power BI a view, not raw tables

The most robust pattern is to create views in a dedicated schema that present exactly the rows and columns the report needs. The view becomes a stable contract: the database team can change the underlying tables, and as long as the view keeps its shape, the report keeps working. For the Chinook sales fact:

CREATE SCHEMA reporting;
GO
CREATE VIEW reporting.Sales AS
SELECT il.InvoiceLineId, i.InvoiceId, i.InvoiceDate, i.CustomerId, il.TrackId,
       il.UnitPrice, il.Quantity, il.UnitPrice * il.Quantity AS LineAmount
FROM dbo.InvoiceLine AS il
INNER JOIN dbo.Invoice AS i ON i.InvoiceId = il.InvoiceId;
GO
CREATE ROLE report_reader;
GRANT SELECT ON SCHEMA::reporting TO report_reader;

Add views for the dimensions, such as customers and tracks, in the same way, and grant the reporting login only the report_reader role.

Step 2: Connect from Power BI Desktop

1. Select Get data, then SQL Server database.
2. Enter the server and, optionally, the database.
3. Choose Import or DirectQuery. Import is the default for good reasons; DirectQuery and composite models explains when DirectQuery is worth its costs.
4. Choose the authentication type and enter credentials.
5. In the Navigator, select the reporting views, then Transform Data to review them in Power Query.

The dialog's advanced options are worth knowing:

Step 3: Deal with encryption and certificates

If the connection is not encrypted, Power BI asks whether to continue unencrypted. The better answer is to enable encrypted connections on the server. If the server uses a self-signed certificate, connections can fail with "The certificate chain was issued by an authority that is not trusted". Microsoft documents three fixes: a certificate from a trusted authority, which is best; adding the server to the PBI_SQL_TRUSTED_SERVERS environment variable in Power BI Desktop; or the SqlTrustedServers setting in the gateway's configuration file.

Step 4: Keep the work folding into SQL Server

Power Query tries to translate each step into SQL that the server runs, which Microsoft calls query folding; the result can be full folding, partial folding or none, in which case Power Query downloads the data and does the work itself (Microsoft Learn: query folding). SQL Server supports folding well, so keep steps foldable: select columns, filter rows, rename and change types early. Right-click a step and choose View Native Query; if the option is available, that step folds. A typical folded query:

let
    Source = Sql.Database("sqlprod01\reporting", "Chinook"),
    Sales = Source{[Schema = "reporting", Item = "Sales"]}[Data],
    Kept = Table.SelectColumns(Sales, {"InvoiceId", "InvoiceDate", "CustomerId", "TrackId", "LineAmount"}),
    Recent = Table.SelectRows(Kept, each [InvoiceDate] >= #datetime(2023, 1, 1, 0, 0, 0))
in
    Recent

Native SQL statements: use sparingly

The SQL statement option runs your own query. It is convenient for complex logic that already exists, but query folding on top of native queries is limited to some connectors, queries written by someone else prompt for approval before running, and statements that change the database's structure are not supported (Microsoft Learn: native database queries). A view gives the same result with none of these drawbacks, and it lives where the database team can see it.

Step 5: Check what arrived

After loading, compare row counts and totals with the source. For the Chinook view, the expected values can be calculated independently. The release's SQLite file holds the same invoice lines as the SQL Server script, but the SQL Server script dates each invoice exactly 12 years later, so the code aligns the dates:

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
view_select = """
SELECT il.InvoiceLineId, i.InvoiceId, i.InvoiceDate, i.CustomerId, il.TrackId,
       il.UnitPrice, il.Quantity, il.UnitPrice * il.Quantity AS LineAmount
FROM InvoiceLine AS il
INNER JOIN Invoice AS i ON i.InvoiceId = il.InvoiceId
"""
sales = pd.read_sql(view_select, sqlite3.connect("chinook.sqlite"), parse_dates=["InvoiceDate"])
sales["InvoiceDate"] = sales["InvoiceDate"] + pd.DateOffset(years=12)   # SQL Server script dates
print(f"rows: {len(sales)} | invoices: {sales['InvoiceId'].nunique()} | line amount total: {sales['LineAmount'].sum():.2f}")
print(f"invoice dates: {sales['InvoiceDate'].min():%Y-%m-%d} to {sales['InvoiceDate'].max():%Y-%m-%d}")
print("line IDs unique:", sales["InvoiceLineId"].is_unique)

rows: 2240 | invoices: 412 | line amount total: 2328.60
invoice dates: 2021-01-01 to 2025-12-22
line IDs unique: True

In Power BI, a table visual with a count of rows, a distinct count of invoices and the sum of LineAmount should show 2,240, 412 and 2,328.60. If it does not, look for filters in Power Query, a join that duplicated rows, or a type change that turned values blank.

Step 6: Publish and refresh

Once published, the Power BI service refreshes imported data on a schedule. For a SQL Server on your own network, it needs an on-premises data gateway, a Windows application that acts as a bridge between on-premises sources and Microsoft's cloud using only outbound connections (Microsoft Learn: on-premises data gateway). Install it on an always-on machine that can reach the server, add the SQL Server data source with its credentials, and map the published semantic model to it. The Power BI gateway explained and scheduled and incremental refresh cover both in detail.

Troubleshooting

For the model to build on top of this data, see star schema in Power BI, and for a full report on Chinook, building a sales dashboard from scratch. Power BI with SQL is covered in the Power BI course and in the integration module of the SQL Server course.

Sources

• Power Query SQL Server connector (Microsoft Learn) — Microsoft Learn
• Query evaluation and query folding in Power Query (Microsoft Learn) — Microsoft Learn
• Import data from a database using native database query (Microsoft Learn) — Microsoft Learn
• What is an on-premises data gateway? (Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub