SQL Server connection in Power BI - Step-by-Step Guide

Introduction

This feature lets you connect Power BI to a SQL Server database. It helps you get data directly from your database to create reports and dashboards without manual exports.

When you want to build a sales report using live data stored in SQL Server.

When your team updates data daily in SQL Server and you want your dashboard to reflect changes automatically.

When you need to combine SQL Server data with other data sources in Power BI.

When you want to avoid exporting data manually from SQL Server to Excel before analysis.

When you want to schedule data refreshes from SQL Server in Power BI service.

Steps

Step 1: Click

- Home tab > Get Data button

The Get Data window opens showing data source options

Step 2: Select

- Get Data window > Database category > SQL Server database

The SQL Server database connection window appears

Step 3: Type

- SQL Server database window > Server field

The server name is entered for connection

Step 4: Type

- SQL Server database window > Database field (optional)

The specific database name is entered to narrow data source

Step 5: Choose

- SQL Server database window > Data connectivity mode

Select either Import or DirectQuery mode for data access

Step 6: Click

- SQL Server database window > OK button

Power BI connects to SQL Server and shows Navigator pane with tables

Step 7: Select

- Navigator pane

Chosen tables or views appear ready to load or transform

Step 8: Click

- Navigator pane > Load button

Data loads into Power BI for report building

Before vs After

Before

Power BI has no data loaded; the report canvas is empty

After

Power BI shows tables from SQL Server loaded and ready for report creation

Settings Reference

Server

📍 SQL Server database connection window

Specifies the SQL Server instance to connect to

Database

📍 SQL Server database connection window

Limits connection to a specific database on the server

Data connectivity mode

📍 SQL Server database connection window

Determines if data is imported or queried live

Default: Import

Authentication method

📍 SQL Server database connection window (after clicking OK if prompted)

Defines how Power BI authenticates to SQL Server

Default: Windows Authentication

Common Mistakes

Entering incorrect server name or missing instance name

Power BI cannot connect if the server name is wrong or incomplete

Verify the exact server name and instance from your database admin before connecting

Choosing Import mode when live data is needed

Import mode loads data once and does not reflect real-time changes

Select DirectQuery mode to query live data if up-to-date info is required

Not selecting the correct authentication method

Connection fails if authentication does not match SQL Server settings

Confirm with your admin which authentication to use and enter correct credentials

Summary

Power BI connects directly to SQL Server to fetch data for reports.

You can choose to import data or query it live with DirectQuery.

Make sure to enter correct server details and authentication to connect successfully.

Practice

1. What is the main purpose of connecting Power BI to a SQL Server database?

easy

A. To import or query data directly for creating reports

B. To design the SQL Server database schema

C. To write SQL Server stored procedures

D. To backup the SQL Server database

Solution

1. Step 1: Understand Power BI data sources

   Power BI connects to data sources like SQL Server to get data for reports.

2. Step 2: Identify the purpose of connection

   The connection allows importing or querying data to visualize it in Power BI.

3. Final Answer:

   To import or query data directly for creating reports -> Option A

4. Quick Check:

   Power BI connection = data import/query [OK]

Hint: Power BI connects to get data, not to manage database [OK]

Common Mistakes:

• Confusing Power BI with database design tools
• Thinking connection is for database backup
• Assuming Power BI writes SQL code

2. Which of the following is the correct way to specify the server name when connecting Power BI to SQL Server?

easy

A. ServerName/InstanceName

B. ServerName\InstanceName

C. ServerName-InstanceName

D. ServerName.InstanceName

Solution

1. Step 1: Recall SQL Server instance naming

   SQL Server uses backslash \ to separate server and instance names.

2. Step 2: Match correct syntax

   The correct format is ServerName\InstanceName, not with slashes or dots.

3. Final Answer:

   ServerName\InstanceName -> Option B

4. Quick Check:

   SQL Server instance separator = \ [OK]

Hint: Use backslash \ between server and instance [OK]

Common Mistakes:

• Using forward slash or dot instead of backslash
• Omitting instance name when required
• Adding spaces around the backslash

3. You connect Power BI to SQL Server using DirectQuery mode. What happens when you refresh the report visuals?

medium

A. Power BI imports all data again from SQL Server

B. Power BI uses cached data and does not query SQL Server

C. Power BI queries the SQL Server live for updated data

D. Power BI disconnects from SQL Server after refresh

Solution

1. Step 1: Understand DirectQuery mode

   DirectQuery mode means Power BI sends live queries to the database on demand.

2. Step 2: Identify refresh behavior

   Refreshing visuals triggers live queries to SQL Server for current data.

3. Final Answer:

   Power BI queries the SQL Server live for updated data -> Option C

4. Quick Check:

   DirectQuery = live queries on refresh [OK]

Hint: DirectQuery means live data queries, not cached data [OK]

Common Mistakes:

• Confusing DirectQuery with Import mode
• Thinking refresh always imports all data
• Assuming Power BI disconnects after refresh

4. You try to connect Power BI to SQL Server but get an error: "Cannot connect to server." What is the most likely cause?

medium

A. Incorrect server or database name entered

B. Power BI does not support SQL Server connections

C. Your computer is offline but server is reachable

D. You forgot to install Power BI Desktop

Solution

1. Step 1: Check connection error meaning

   "Cannot connect to server" usually means wrong server or database info.

2. Step 2: Rule out other causes

   Power BI supports SQL Server; offline computer means no connection; Power BI Desktop must be installed.

3. Final Answer:

   Incorrect server or database name entered -> Option A

4. Quick Check:

   Connection error = wrong server/database name [OK]

Hint: Double-check server and database names for typos [OK]

Common Mistakes:

• Assuming Power BI lacks SQL Server support
• Ignoring network or firewall issues
• Not verifying Power BI installation

5. You want to create a Power BI report that always shows the latest sales data from SQL Server without importing all data each time. Which connection mode should you choose and why?

hard

A. DirectQuery, because it imports all data once

B. Import, because it loads all data once and never updates

C. Import, because it queries live data on demand

D. DirectQuery, because it queries live data on demand

Solution

1. Step 1: Understand Import vs DirectQuery

   Import loads data once; DirectQuery queries live data each time.

2. Step 2: Choose mode for latest data without full import

   DirectQuery fits because it fetches current data without importing all.

3. Final Answer:

   DirectQuery, because it queries live data on demand -> Option D

4. Quick Check:

   Latest data without import = DirectQuery [OK]

Hint: Use DirectQuery for live data, Import for snapshot [OK]

Common Mistakes:

• Confusing Import with live querying
• Choosing Import when data freshness is needed
• Misunderstanding DirectQuery behavior