How to Connect SQL Server to Power BI

Power BI ships with a native SQL Server connector, so connecting is straightforward; the real decision is how you connect. Import mode pulls a compressed copy of the data into Power BI for fast, flexible analysis, while DirectQuery leaves the data in SQL Server and queries it live. For on-premises or private-network servers you also need the on-premises data gateway to enable scheduled refresh or live queries from the Power BI service. Getting these choices right is the difference between a snappy, trustworthy report and a slow one that frustrates users.

Connecting in three steps

In Power BI Desktop, choose Get Data, select SQL Server, and enter the server and database names. Pick a connectivity mode (Import or DirectQuery), authenticate with Windows or SQL credentials, and select the tables or write a query. For the published report to refresh, configure the gateway and data source credentials in the Power BI service. Prefer connecting to views or stored procedures over raw tables so the source can evolve without breaking your model.

Import vs DirectQuery

This is the choice that defines your report. Import loads data into Power BI's in-memory engine, giving the fastest interaction, the full DAX feature set and offline modelling. The trade-offs are dataset size limits and data only being as fresh as the last refresh. DirectQuery keeps data in SQL Server, so reports are near-live and there is no large import, but every visual interaction issues a query, performance depends on the source, and some DAX and modelling features are restricted.

A pragmatic middle ground is a composite model: import the dimensions and small fact tables, use DirectQuery for the largest fact, and add aggregations so common queries hit the imported summary first.

The data gateway

If SQL Server runs on-premises or in a private network, install the on-premises data gateway on a reliable machine close to the database, register it in the Power BI service and map your dataset to it. Use the standard (enterprise) gateway, not personal mode, for shared workloads. Azure SQL Database reached over a public endpoint does not need a gateway, though private-endpoint configurations may. Keep the gateway patched and monitored: a stalled gateway is a common cause of silent refresh failures.

Best practice for performance and security

• Push work to the source. Filter, aggregate and shape in SQL views rather than dragging entire tables into Power Query.
• Model with a star schema. Separate facts and dimensions and add a proper date table; this helps both Import and DirectQuery.
• Index for DirectQuery. Ensure the columns used in joins and filters are indexed, and review the queries Power BI generates.
• Use least-privilege credentials. Connect with a read-only account scoped to the needed objects, and apply row-level security where users should see different slices.
• Mind refresh limits. A Pro workspace allows up to eight refreshes a day and Premium or Fabric up to 48; schedule them outside peak source load.
• Use parameters. Parameterise the server and database so you can promote a report from development to production without rebuilding it.

When to bring in a warehouse

If multiple reports query the same operational SQL Server, or queries compete with the live application, stage data into a reporting database or warehouse and point Power BI there instead. This isolates reporting load, lets you model cleanly and supports history that the operational system may not retain. It is also the right foundation if you later blend SQL Server with cloud sources or want a governed Power BI estate.

As a rough UK guide, a scoped dashboard build starts from £6,000 with typical projects at £8,000–£14,000, and senior Power BI day rates run roughly £400–£850/day (as of 2026, confirm current pricing). The biggest wins usually come from modelling and source-side tuning, not licence upgrades.

A Trusted Numbers Review reviews your SQL Server connection, model and gateway, then recommends the Import, DirectQuery or hybrid approach that fits your data. Book one to turn your database into decisions you can trust.