Connecting Power BI to SQL Server: Writing Native Queries, Selecting Tables, and Managing Credentials for Reliable Data Loads
Introduction
Picture this: your company's sales data lives in a SQL Server database. It's been there for years — carefully maintained, indexed, and structured by a database team that knows exactly what they're doing. Your job is to build a report that shows regional sales performance by quarter. You open Power BI Desktop, and suddenly you're faced with a choice: do you drag in every table from the database and figure out the joins later, or do you write a targeted SQL query that fetches exactly the data you need?
This decision matters more than most beginners realize. How you connect to SQL Server — and what you ask it to return — shapes everything downstream: how fast your report loads, how much memory it uses, whether your numbers are correct, and whether your report continues to work six months from now when someone rotates the database password. Getting this right from the start saves you from painful rework later.
By the end of this lesson, you'll be able to connect Power BI Desktop to a SQL Server instance, choose between selecting tables directly and writing native SQL queries, manage credentials properly so refreshes don't silently fail, and troubleshoot the most common connection problems. This is foundational infrastructure — the plumbing that everything else in your report depends on.
What you'll learn:
- How to configure a SQL Server connection in Power BI Desktop, including server name and database settings
- The difference between selecting tables from the Navigator pane and writing native SQL queries — and when to use each
- How to write a native query that filters and shapes data before it reaches Power BI
- How to manage Windows and SQL Server credentials, and why credential mismatches cause refresh failures
- How to switch between Import mode and DirectQuery mode for SQL Server connections
Prerequisites
You should have Power BI Desktop installed. If this is your first time opening it, take a quick pass through Your First Power BI Report in 30 Minutes before continuing — that lesson covers the basic layout and workflow you'll need to navigate this one comfortably.
You'll also need access to a SQL Server instance. This could be SQL Server Express running locally on your machine (free to download from Microsoft), a company development server, or an Azure SQL Database. The connection steps are nearly identical for all three.
How Power BI Talks to SQL Server
Before you click anything, it helps to understand what's actually happening when Power BI connects to SQL Server.
SQL Server is a database engine that stores data in tables and responds to queries written in Transact-SQL (T-SQL), which is Microsoft's dialect of the SQL language. When Power BI connects to SQL Server, it acts as a client: it sends a query to the database, the database processes it and returns rows, and Power BI loads those rows into its in-memory model.
This means every table, every view, and every native query you configure in Power BI ultimately becomes a SQL statement that runs against your database. Even when you click a table name in the Navigator pane, Power BI generates a SELECT * FROM [schema].[tablename] statement behind the scenes.
Understanding this shapes how you think about performance. A query that returns 10 million rows is doing 10 million rows worth of work — on both the database side (reading and sending data) and the Power BI side (loading and compressing it). Writing targeted queries instead of importing entire tables is how professional analysts keep their models lean.
Key insight: Power BI's Import mode loads data into a compressed, in-memory columnar store at refresh time. DirectQuery mode skips that — every visual sends a live query to the database when a user interacts with the report. Each approach has tradeoffs you'll want to understand before committing to one for a production dataset. See Understanding Power BI Storage Modes: Import, DirectQuery, and Live Connection Compared for a thorough breakdown.
Making Your First Connection
Open Power BI Desktop. On the Home ribbon, click Get Data. A search box and a list of connectors will appear. Type "SQL Server" in the search box and select SQL Server database from the results, then click Connect.
You'll see the SQL Server database dialog with two fields:
Server — This is the hostname or IP address of your SQL Server instance. For a default local instance, type localhost. If you're running a named instance (common with SQL Server Express), the format is localhost\SQLEXPRESS or SERVERNAME\INSTANCENAME. For an Azure SQL Database, you'll enter the full hostname like myserver.database.windows.net.
Database (optional) — You can leave this blank to see all databases on the server, or specify a database name like SalesDB to scope the connection. Specifying a database is a good habit — it limits what the Navigator shows you and makes the connection more intentional.
Below those two fields, you'll find an expandable Advanced options section. This is where things get interesting. Inside Advanced options, you'll find a text area labeled SQL statement (optional). Leave this empty for now — we'll return to it in the next section.
You'll also see a Data Connectivity mode option with two radio buttons: Import and DirectQuery. Leave it on Import for now.
Click OK.
Authenticating: Windows vs. SQL Server Login
Power BI will now prompt you to authenticate. You'll see a dialog with three tabs along the left:
- Windows — Uses your current Windows user account (or a different Windows account you specify) to authenticate. This works when SQL Server is configured for Windows Authentication, which is common in corporate environments using Active Directory.
- Database — Uses a SQL Server login (a username and password stored inside SQL Server itself, not tied to Windows). This is common for application accounts and Azure SQL Databases.
- Microsoft account — Used for Azure-based services. Skip this for standard SQL Server.
For most corporate SQL Server connections, Windows authentication with your current account is the right choice. For SQL Server Express on your local machine, either works depending on how you configured it during installation. For Azure SQL Database, you'll nearly always use the Database tab and enter the SQL login credentials your DBA provided.
After selecting the appropriate tab and entering credentials, click Connect.
Warning: Choose your authentication method carefully, because this choice gets stored with the connection. If you connect using your personal Windows account and your report is published to Power BI Service where a shared service account needs to refresh it, you'll hit an authentication failure. We'll cover credential management in detail shortly.
Navigating the Navigator Pane
After authenticating successfully, the Navigator pane opens. On the left you'll see a tree structure showing the database (if you specified one) or all databases on the server. Expand the database node and you'll see folders for Tables, Views, and potentially other object types.
This is where many beginners make their first significant decision: they check the boxes next to every table that sounds relevant. Resist that impulse.
Each table you select becomes a separate query that Power BI will execute every time you refresh. If you select 20 tables, Power BI runs 20 full table scans during refresh. If those tables are large, this gets slow and memory-intensive fast.
Instead, look at your tables and think: which rows from this table do I actually need? For a sales report covering the last two years, you probably don't need six years of transaction history. For a product report focused on active SKUs, you don't need discontinued products. Filtering at the source — before data enters Power BI — is always more efficient than filtering inside Power BI after loading.
For relatively small lookup tables (like a product category table with 50 rows, or a date dimension), selecting them directly from the Navigator is perfectly appropriate. The effort of writing a custom query for a table that tiny just adds complexity without benefit.
Select one table to preview it in the right panel. The preview shows sample rows and helps you verify you've chosen the right object. When you're ready, click Load to import directly or Transform Data to open Power Query Editor first.
Tip: Almost always choose Transform Data instead of Load. This opens Power Query Editor where you can inspect data types, rename columns, and apply filters before the data hits your model. Making these corrections at the Power Query stage prevents type errors and bad data from propagating downstream. You can learn the full workflow in Importing and Transforming Your First Dataset in Power Query: A Step-by-Step Beginner Walkthrough.
Writing Native SQL Queries
Here's where Power BI's SQL Server connector becomes genuinely powerful. Instead of selecting a table and hoping the data is already in the shape you need, you write a SQL query that does exactly the filtering, joining, and aggregating you want — and Power BI loads the result.
Go back to Get Data → SQL Server database, enter your server and database details, expand Advanced options, and paste your SQL into the SQL statement text box.
Here's a realistic example. Suppose you're connecting to a SalesDB database and you need sales transactions from the last two calendar years, joining to customer and region data, and you only want orders with a status of 'Completed':
SELECT
o.OrderID,
o.OrderDate,
o.TotalAmount,
o.Status,
c.CustomerName,
c.CustomerSegment,
r.RegionName,
r.Country
FROM dbo.Orders o
INNER JOIN dbo.Customers c
ON o.CustomerID = c.CustomerID
INNER JOIN dbo.Regions r
ON c.RegionID = r.RegionID
WHERE o.Status = 'Completed'
AND o.OrderDate >= DATEADD(YEAR, -2, GETDATE())
When you click OK, Power BI will execute this query against your database and show you a preview in the Navigator. You won't see a table tree — you'll see a single result set labeled with the query itself. Click Transform Data to open it in Power Query Editor.
Why Native Queries Are Better Than Doing It in Power Query
You might wonder: couldn't you just load the full Orders table and then filter it in Power Query? Technically yes. But there's an important difference.
When Power BI loads a full table and filters it in Power Query, the database still sends all the rows to Power BI — the filtering happens after the data travels across the network. When you filter in a native SQL query, the database applies the filter before sending any data. For a table with 5 million rows where only 200,000 pass the filter, the native query approach is sending 96% less data across the wire. At scale, this is the difference between a 10-second refresh and a 3-minute refresh.
Joins work the same way. Combining tables in SQL and sending Power BI a single clean result set is almost always faster than loading three separate tables and defining relationships in the Power BI model — though the model relationship approach has its own advantages for certain report patterns.
Note: Native queries run against the database as-is. If your query references a view that does something expensive, Power BI inherits that cost. If your query hits an unindexed column in the WHERE clause, you'll get a slow query. Power BI doesn't optimize your SQL — it runs exactly what you write. Talk to your DBA about indexing strategies if you're running queries against production servers.
The Folding Caveat
Power Query has a feature called query folding where it tries to push transformations you apply in Power Query Editor back down into the SQL query, so they run on the database rather than in Power BI's engine. When you use a native SQL query as your source, query folding is disabled — Power Query can't modify a query you wrote by hand. This means any transformations you add in Power Query Editor will run in Power BI's engine instead of the database.
This is usually fine. The native query already does the heavy lifting, and Power Query handles column renaming, type conversion, and minor reshaping efficiently. Just be aware that if you add complex transformations on top of a native query, you're doing that work in Power BI's memory rather than on the database server.
Managing Credentials for Reliable Refreshes
Getting the connection working on your laptop is step one. Keeping it working after you publish the report — especially for scheduled refreshes — requires careful credential management.
Where Credentials Are Stored
In Power BI Desktop, credentials are stored locally on your machine, associated with the data source (identified by server name and database name). You can view and update them by going to File → Options and settings → Data source settings.
In this dialog, you'll see a list of all data sources Power BI Desktop has connected to. Select a SQL Server entry and click Edit Permissions to see which credential is currently stored for it and change it if needed.
This separation matters: the credentials in Power BI Desktop are only used when you're working locally. When you publish to Power BI Service and set up a scheduled refresh, you configure credentials separately in the Service. We'll come back to this.
Windows Authentication vs. SQL Server Authentication in Production
In a production environment, the credential strategy depends on how your SQL Server is configured and where your report will run.
Windows Authentication with a gateway: If your SQL Server is on your company's internal network (not in Azure), Power BI Service can't reach it directly. You need an On-premises Data Gateway — a piece of software that runs on a server inside your network and acts as a secure tunnel. When the refresh runs, Power BI Service asks the gateway to connect, and the gateway authenticates to SQL Server using a Windows service account. Power BI Gateway: Complete Guide to Connecting On-Premises Data to the Cloud covers this in full detail.
SQL Server Authentication: If you're using a SQL login (username and password), you'll enter those credentials when configuring the dataset in Power BI Service. Go to the dataset settings in Power BI Service, find the Data source credentials section, click Edit credentials, and enter the SQL login username and password there.
Warning: Never use your personal SQL Server login as the credential for a shared production dataset. If your account password changes or you leave the company, every refresh fails until someone updates the credential. Use a dedicated service account — a SQL login created specifically for Power BI data access, with only the permissions it needs.
What Happens When Credentials Fail
When a refresh runs with bad credentials, Power BI Service logs an error in the refresh history and sends a failure notification email. The error message will usually say something like "Data source credentials are not set" or "Login failed for user."
In Power BI Desktop, if credentials fail you'll see a yellow warning banner in the query editor or an error in the data preview. Click the error to see the details — it usually pinpoints which data source is failing and why.
Credential errors are among the most common issues in production Power BI environments. The fix is almost always: go to dataset settings in Power BI Service, update the credential under Data source credentials, and trigger a manual refresh to confirm it works.
Switching Between Import and DirectQuery
When you set up the connection, you chose Import mode. It's worth understanding what DirectQuery means for SQL Server connections specifically.
In Import mode, Power BI pulls all the data into its in-memory engine at refresh time. Your visuals query that local cache — they're extremely fast, and your reports work even if the SQL Server is temporarily unavailable. The tradeoff: data is only as fresh as your last refresh.
In DirectQuery mode, Power BI sends a live query to SQL Server every time a user interacts with a visual. Data is always current, but every click triggers a database query, which means your SQL Server needs to handle that load — and slow queries mean sluggish reports.
For most beginner and intermediate use cases, Import mode is the right choice. DirectQuery makes sense when your data is too large to import, when you need real-time freshness, or when database security policies require live connections. You can read a thorough comparison in Understanding Power BI Storage Modes: Import, DirectQuery, and Live Connection Compared, and see how to combine both approaches for complex scenarios in Mastering Power BI Composite Models: Combining DirectQuery and Import Mode for Real-Time and Historical Data Analysis.
Hands-On Exercise
Work through this exercise against any SQL Server instance you have access to. If you're using SQL Server Express locally, the AdventureWorks sample database (free download from Microsoft) works perfectly.
Step 1: Open Power BI Desktop. Go to Home → Get Data → SQL Server database.
Step 2: Enter your server name (e.g., localhost\SQLEXPRESS) and database name (e.g., AdventureWorks2019). Leave Data Connectivity mode on Import. Click OK.
Step 3: Authenticate using Windows authentication. When the Navigator opens, browse to the Tables folder. Select HumanResources.Employee and preview it. Note how many columns it contains — far more than you'd need for any single report.
Step 4: Cancel out of the Navigator. Go back to Get Data → SQL Server database and this time open Advanced options. Paste in this native query:
SELECT
e.BusinessEntityID,
e.JobTitle,
e.HireDate,
e.Gender,
e.MaritalStatus,
d.Name AS Department,
d.GroupName AS DepartmentGroup
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.BusinessEntityID = edh.BusinessEntityID
AND edh.EndDate IS NULL
INNER JOIN HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID
WHERE e.CurrentFlag = 1
Step 5: Click OK, authenticate again if prompted, and click Transform Data when the Navigator shows your result set. In Power Query Editor, verify the column types: HireDate should be a Date type. If it shows as DateTime, right-click the column header, choose Change Type → Date, and replace the current conversion.
Step 6: Rename the query from the auto-generated name to something meaningful like Employees_Active. Click Close & Apply.
You now have a clean, filtered employee dataset that joins three tables at the database level, returns only current employees, and has proper data types — all with one SQL query.
Common Mistakes & Troubleshooting
"Unable to connect to the server" — Check the server name format. Named instances need the backslash format: SERVERNAME\INSTANCENAME. Also verify SQL Server Browser service is running (it's what allows named instance connections). If connecting remotely, TCP/IP protocol needs to be enabled in SQL Server Configuration Manager.
"Login failed for user [NT AUTHORITY\ANONYMOUS LOGON]" — This appears when Windows Authentication is selected but the credentials aren't being passed correctly. This often happens when connecting through a gateway or VPN. Switch to SQL Server Authentication or check the gateway service account configuration.
Native query returns no preview / blank Navigator — The query may have a syntax error, or it may reference objects the authenticated user doesn't have permission to access. Test your SQL in SQL Server Management Studio (SSMS) first using the same credentials to confirm it runs successfully.
Refresh fails in Power BI Service but works in Desktop — Almost always a credential issue. The Desktop uses your local Windows credentials; the Service uses whatever credential you configured in dataset settings. Open the dataset settings in Power BI Service and re-enter the credentials under Data source credentials.
Query runs slowly — If your native query is slow, the issue is on the SQL Server side: missing indexes, expensive joins, or a poorly-written query. Use SSMS's query execution plan to diagnose. Adding appropriate indexes on columns in your WHERE clauses can dramatically speed up Power BI refresh times.
Data types look wrong after loading — This is extremely common when loading from SQL Server. A SQL datetime column might come through as datetime2 in Power BI, or a numeric column might be read as text. Always open Power Query Editor and verify types before building your model. The patterns to look for are covered in Understanding Power Query Data Types and Column Profiling: Preventing Errors Before They Reach Your Report.
Tip: Keep a text file with your native SQL queries alongside your Power BI project files. When you need to update the query six months from now — to add a new column or change a filter — you'll thank yourself for not having to extract it from Power Query Editor.
Summary & Next Steps
You've learned how to configure a SQL Server connection in Power BI Desktop, navigate the distinction between selecting tables and writing native SQL queries, and manage credentials in a way that keeps scheduled refreshes working reliably. These skills form the foundation of connecting Power BI to any production database environment.
The most important mindset shift from this lesson: think of your SQL query as the first transformation in your data pipeline, not an afterthought. The more precisely you define what data you need at the source — filtering rows, joining only the necessary tables, excluding deprecated columns — the cleaner and faster everything downstream will be.
From here, the natural next steps depend on what you're building:
