Ravindra BagaleCourses & study guides Track your progress

Guides

How to Connect SQL Server to Power BI Desktop

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

Connect SQL Server with Home → Get data → SQL Server, enter the server, choose Import (copy into the model) or DirectQuery (live queries), pick tables in Navigator, then Load or Transform — that is the whole beginner path.

Friends! Excel is comfortable; SQL Server is where many company facts actually live. Why learn this carefully? Because the Import vs DirectQuery click is easy to rush and hard to undo casually. How? We walk Get data → mode → Navigator with a fictional FreshBasket warehouse database. Slow and clear.

Quick answer

Vertical checklist:

  1. Home → Get data → SQL Server.
  2. Enter server (and optional database).
  3. Choose Import or DirectQuery.
  4. Authenticate with the account your DBA gave you.
  5. Tick tables/views in Navigator.
  6. Transform data if types need fixing; otherwise Load.
Get data → SQL Server → Import | DirectQuery → Navigator → Load

Why SQL, what happens after you click

A shop that sells in Mumbai and Nashik may keep orders in Excel at first. Real companies often keep the same facts in SQL Server.

Tiny picture:

Order ID City Amount
A Mumbai 100
B Mumbai 40
C Nashik 80
  1. Those three rows live in FreshBasketDB on a company SQL Server.
  2. When you choose Import, Power BI copies them into your model. A card can show Mumbai 140 offline.
  3. When you choose DirectQuery, the rows stay in SQL. Each visual asks SQL again. Freshness is higher. Your laptop talks more.
  4. Navigator is the window where you tick Sales and Customer before Load.

Why you care:

  1. Finance and ops systems often land in SQL first.
  2. Refresh and governance talk about databases, not only Excel dumps.
  3. The storage mode choice starts on this dialog.
SQL Get data Get data SQL Server steps Get data SQL Server Import/DQ Navigator connect

SQL Server path: Get data → SQL Server → enter server (and database) → pick Import or DirectQuery → Navigator → Load or Transform.

What do I need before this guide?

Before and after (look at the tables first)

Before mode choice SQL returns three rows totaling 190.

Before

After Import filter Import keeps 2025 rows; total 140.

After

Import copies rows into the model (here Year = 2025 → 140). DirectQuery leaves data in SQL and queries live — pick based on size and freshness.

Step-by-step connection (classroom pace)

1) Open the connector

  1. Home → Get data.
  2. Select SQL Server database (More… → Database if needed).

2) Server box

  1. Type the server name your admin provided (HOST or HOST\\INSTANCE).
  2. Optional: database name to skip hunting later.
  3. Advanced options → SQL statement only when you truly need a custom query.

3) Import vs DirectQuery (the fork)

Import vs DirectQuery Import copies; DirectQuery stays live Importcopy into model DirectQuerylive on SQL Server mode

Import copies tables into the model; DirectQuery leaves data in SQL Server and sends queries live.

  1. Import — Power BI copies tables into the model. Fast visuals; refresh to update.
  2. DirectQuery — data stays in SQL Server; visuals send queries live.

Beginners: prefer Import for learning unless your tutor says otherwise.

What happens with our tiny table:

  1. Import → Mumbai card shows 140 from the model copy.
  2. Someone inserts a new Mumbai order of 10 in SQL.
  3. Import report still shows 140 until refresh.
  4. DirectQuery report can show 150 on the next visual query (if the DB allows it).

4) Credentials

  1. Windows / Database / Microsoft account — match your environment.
  2. Wrong password looks like “empty Navigator” or hard failures — fix auth before reshaping M.

5) Navigator

SQL Navigator Navigator pick tables then load Navigatortables / views Transformoptional Load pick

Navigator shows databases and tables/views; tick what you need, then Transform data to shape before the model.

  1. Expand the database.
  2. Tick fact and dimension tables you need (start small).
  3. Preview a few rows — confirm Amount looks like money, not text.
  4. Transform data to set types; Load when clean enough.

Import vs DirectQuery — decision lines

  1. Training laptop + sample DB → Import.
  2. Multi-million-row warehouse + need freshness → consider DirectQuery.
  3. Need full DAX freedom and speed → Import usually wins.
  4. Mixed models (Dual) → next guide; master one mode first.

Gateway note (Service later)

  1. Desktop on the office network can often see SQL directly.
  2. The Power BI Service talking to on-premises SQL usually needs an on-premises data gateway.
  3. Do not panic on day one — connect in Desktop first.

Mistakes and calm fixes

Symptom Likely cause Fix
Cannot find server VPN / firewall / typo Confirm server string in SSMS first
Login failed Wrong auth mode Match Windows vs SQL auth with DBA
Navigator empty No permission / wrong DB Request read rights; pick correct catalog
Visuals slow (DQ) Heavy visuals + live SQL Simplify measures; consider Import
Folding surprises Native SQL / complex M Prefer table select; learn folding later

Ghabru naka 😅 — if SSMS fails with the same account, Power BI is not the villain.

Ravindra Bagale's Tip

Many students pick DirectQuery because it sounds “more professional”, then fight slow slicers all week. Start Import for learning. Switch when size or freshness truly forces you. Do not forget — mode is a design choice, not a badge.

Practice task

  1. Connect to a lab SQL database with Import.
  2. Load one fact and one dimension table.
  3. Create one card measure that shows a city total (like Mumbai 140 on sample data).
  4. Write three lines: why you chose Import for this lab.
  5. Skim the DirectQuery-in-practice course lesson without implementing yet.

Learn it properly

Course lessons:

Related guides: Import vs DirectQuery vs Dual · Excel/CSV

Got it? SQL path = Get data → server → Import or DirectQuery → Navigator → Load. Prefer Import while learning. Let's continue storage modes in depth.

Frequently asked questions

Import or DirectQuery for SQL?

Import for smaller/faster models and full DAX features; DirectQuery when data is huge or must stay near real time on the server.

Do I need a gateway?

On-premises SQL refreshing or live in the Service usually needs an on-premises data gateway. Desktop on the same network can often connect directly.

Can I write SQL in the connector?

Yes via Advanced options → SQL statement, but start with table selection; native SQL can limit folding and reuse.

Navigator is empty — why?

Wrong server name, firewall, permissions, or credentials. Test with SSMS or Azure Data Studio using the same account.

Mixed mode later?

Composite models can mix Import and DirectQuery; learn single-mode first (see the Import vs DirectQuery vs Dual guide).

Course lessons?

SQL Server connection and DirectQuery-in-practice lessons in the databases chapter.