{"id":1889,"date":"2021-10-01T10:50:39","date_gmt":"2021-10-01T10:50:39","guid":{"rendered":"https:\/\/www.exceldepartment.com\/?p=1889"},"modified":"2026-10-31T12:14:16","modified_gmt":"2026-10-31T12:14:16","slug":"connecting-power-bi-to-sql-server-and-access","status":"publish","type":"post","link":"https:\/\/www.exceldepartment.com\/en\/connecting-power-bi-to-sql-server-and-access\/","title":{"rendered":"Connecting Power BI to SQL Server and Access \u2013 Complete Guide"},"content":{"rendered":"
\n

Your data sits in a SQL Server database or an Access file. Power BI can visualise it. But how do you connect them the right way? And what is the difference between DirectQuery and Import? This guide shows exactly how.<\/p>\n

At Excel Department we have connected Power BI to over 100 databases \u2013 from small Access files to enterprise SQL Servers with billions of rows. We have made all the mistakes so you don’t have to. This guide gives you the methods that actually work.<\/p>\n

Why connect Power BI to a database instead of Excel?<\/h2>\n

Before we dive into the technical side, let’s talk about why.<\/p>\n

Excel limitations:<\/strong> – Max about 1 million rows (slow long before that) – Manual refresh or complex VBA – Risk of data becoming outdated – Hard to handle many users<\/p>\n

Database advantages:<\/strong> – Millions or billions of rows without problems – Always up-to-date data (Power BI reads directly from the source) – Structured and relational data – One source of truth for the whole organisation<\/p>\n

When you should connect to a database:<\/strong> – Data is updated continuously (sales, transactions, logging) – The data volume grows beyond Excel’s capacity – Several systems use the same data – You want to guarantee data consistency<\/p>\n

If you already have SQL-connected Excel<\/a> today, the step to Power BI is small but gives you much more power.<\/p>\n

SQL Server: DirectQuery vs Import mode<\/h2>\n

This is the first big decision point. Both approaches work, but they suit different situations.<\/p>\n

Import mode (recommended for most)<\/h3>\n

How it works:<\/strong> Power BI reads data from SQL Server, compresses it, and stores it locally in the Power BI model. When the report is opened, it reads from this copy, not from SQL Server.<\/p>\n

Advantages:<\/strong> – Much faster reports (data is local) – Works offline (data is in the file) – Less load on SQL Server – Power BI’s compression reduces data size (often 10x)<\/p>\n

Disadvantages:<\/strong> – Data is not real-time (updated on a schedule) – Limit on data volume (model size max 10 GB for Pro, 100+ GB for Premium) – Requires a refresh schedule<\/p>\n

Suitable for:<\/strong> – Data warehouse or reporting database – Data that is updated daily or weekly – Historical analysis – When fast performance is critical – Most business intelligence scenarios<\/p>\n

Example:<\/strong> A sales database with 5 million rows, updated every night at 02:00. Power BI fetches new data then. Users get lightning-fast reports during the day.<\/p>\n

DirectQuery mode<\/h3>\n

How it works:<\/strong> Power BI sends SQL queries directly to SQL Server every time you interact with the report. No data is stored locally.<\/p>\n

Advantages:<\/strong> – Truly real-time data (always current) – No data size limit – Security is handled by SQL Server<\/p>\n

Disadvantages:<\/strong> – Slower reports (every click = a SQL query) – Requires a constant connection to SQL Server – Higher load on the database – Limited DAX functions (some measures don’t work)<\/p>\n

Suitable for:<\/strong> – Real-time data where every minute counts (tills, trading, IoT) – Extremely large datasets (100+ GB) – Strict data security (data must not leave the server) – Operational reporting<\/p>\n

Example:<\/strong> A logistics dashboard showing current stock levels. Every time someone picks a product the database is updated and Power BI shows the new figure within seconds.<\/p>\n

Hybrid: Composite models<\/h3>\n

Since 2019 you can combine both. Some tables in Import, others in DirectQuery.<\/p>\n

Typical use case:<\/strong> – Large transaction data in DirectQuery (real-time) – Smaller master data (products, customers) in Import (performance)<\/p>\n

Complicated but powerful.<\/strong> We recommend consultant help for this.<\/p>\n<\/p>\n

Our recommendation<\/h3>\n

Start with Import mode in 95% of cases.<\/strong><\/p>\n

DirectQuery is for special cases. Most companies don’t need real-time data \u2013 a daily refresh is enough. Import gives better performance and is easier to manage.<\/p>\n

Switch to DirectQuery only if: 1. You truly need real-time data (prove it) 2. Your data is >50 GB and growing fast 3. Data security requires that data is not copied<\/p>\n

Step by step: Connect to SQL Server<\/h2>\n

Now it gets practical. Here is how to do it:<\/p>\n

Step 1: Prepare SQL Server<\/h3>\n

You need:<\/strong> – Server name (example: sqlserver.dinserver.se<\/code>) – Database name – Login credentials (Windows Authentication or SQL Server Authentication) – Network access to the server<\/p>\n

Test access first:<\/strong> Open SQL Server Management Studio (SSMS) and connect with the same credentials. If it doesn’t work there, it won’t work in Power BI either.<\/p>\n

Firewall rules:<\/strong> If SQL Server is on-premises behind a corporate firewall, you need to open port 1433 (the standard SQL Server port).<\/p>\n

For Azure SQL: Add your IP address in the Azure Portal under Firewall settings.<\/p>\n

Step 2: Connect from Power BI Desktop<\/h3>\n

Open Power BI Desktop.<\/p>\n

Home \u2192 Get Data \u2192 SQL Server<\/strong><\/p>\n

Fill in:<\/strong> – Server:<\/strong> sqlserver.dinserver.se<\/code> or dinserver.database.windows.net<\/code> (Azure) – Database:<\/strong> The name of the database – Data Connectivity mode:<\/strong> Import (recommended)<\/p>\n

Advanced options (optional):<\/strong> – SQL statement:<\/strong> If you want to write your own SQL query – Command timeout:<\/strong> Increase if large queries take a long time<\/p>\n

Click OK<\/strong>.<\/p>\n

Step 3: Authentication<\/h3>\n

Choose an authentication method:<\/p>\n

Windows Authentication:<\/strong> Uses your Windows login credentials. Works for on-premises SQL Server if you are logged in to the corporate network.<\/p>\n

Database:<\/strong> SQL Server username and password. Standard for Azure SQL.<\/p>\n

Microsoft Account:<\/strong> For Azure SQL you can use your Microsoft 365 account.<\/p>\n

Click Connect<\/strong>.<\/p>\n

Step 4: Select tables<\/h3>\n

The Navigator window shows all tables and views in the database.<\/p>\n

Tips:<\/strong> – Select only the tables you need (not the whole database) – Look at the data size (shown in Navigator) – Prefer views over base tables if IT has created them for you<\/p>\n

Select tables \u2192 click Transform Data<\/strong> (not Load directly).<\/p>\n

Step 5: Transform in Power Query<\/h3>\n

Now you see your tables in the Power Query Editor.<\/p>\n

Common transformations:<\/strong> – Remove columns<\/strong> you don’t need (reduces data size) – Filter rows<\/strong> (for example only the last 2 years) – Change data types<\/strong> if Power BI guessed wrong – Merge queries<\/strong> to create relationships<\/p>\n

Important:<\/strong> All filtering in Power Query reduces the data volume = faster reports.<\/p>\n

Click Close & Apply<\/strong> when done.<\/p>\n

Step 6: Set up automatic refresh (after publishing)<\/h3>\n

Publish the report to Power BI Service.<\/p>\n

Go to the dataset settings: – Scheduled refresh:<\/strong> Choose frequency and time – Gateway:<\/strong> If SQL Server is on-premises (see below) – Credentials:<\/strong> Enter the database credentials<\/p>\n

Note:<\/strong> For on-premises SQL Server a Power BI Gateway is required. More on that below.<\/p>\n

Connect to Microsoft Access<\/h2>\n

Access databases are common in small Swedish companies. Power BI can connect to them, but with limitations.<\/p>\n

Step 1: Connect to an Access file<\/h3>\n

Home \u2192 Get Data \u2192 Access Database<\/strong><\/p>\n

Browse to your .accdb or .mdb file \u2192 Open<\/strong>.<\/p>\n

Navigator shows tables and queries (saved queries in Access).<\/p>\n

Select what you need \u2192 Transform Data<\/strong>.<\/p>\n

Step 2: Transform data<\/h3>\n

Access databases are often less structured than SQL Server. Common problems:<\/p>\n

Problem 1: Blank rows and headers<\/strong> Access tables can have extra rows with formatting. Remove them in Power Query.<\/p>\n

Problem 2: Incorrect data types<\/strong> Access Text columns with numbers mixed in. Fix this in Power Query.<\/p>\n

Problem 3: Relationships<\/strong> Access relationships are not transferred automatically. You have to create them manually in the Power BI Model view.<\/p>\n

Limitations with Access<\/h3>\n

No automatic refresh in the cloud:<\/strong> Access files cannot be refreshed automatically from Power BI Service (the cloud).<\/p>\n

Solutions:<\/strong> 1. Manually:<\/strong> Republish from Power BI Desktop when Access has been updated 2. Gateway:<\/strong> Install Power BI Gateway on a computer that has access to the Access file 3. Migrate:<\/strong> Move the data to SQL Server or SharePoint (recommended long term)<\/p>\n

Our recommendation:<\/strong> If you use Access actively, consider migrating to SQL Server<\/a>. We help companies with this regularly.<\/p>\n

Temporary use:<\/strong> For smaller datasets (<50,000 rows) that are not updated often, Access works fine with manual publishing.<\/p>\n<\/p>\n

Power BI Gateway: The bridge to on-premises<\/h2>\n

If your SQL Server or Access database is on-premises (in your company network, not in the cloud) you need a Power BI Gateway<\/strong>.<\/p>\n

What is a gateway?<\/h3>\n

A gateway is a program installed on a computer in your network. It acts as a bridge between Power BI Service (the cloud) and your local database.<\/p>\n

The flow:<\/strong> 1. Power BI Service requests data 2. The request goes to the Gateway 3. The Gateway fetches data from the local database 4. The Gateway sends data to Power BI Service 5. The report is refreshed<\/p>\n

Two types of gateways<\/h3>\n

On-premises data gateway (Standard mode):<\/strong> – For the whole organisation – Requires installation by the IT department – Can be used by all report developers – Supports several data sources at the same time<\/p>\n

On-premises data gateway (Personal mode):<\/strong> – For a single user – Simpler installation – Only works for that user’s reports<\/p>\n

Recommendation:<\/strong> Standard mode for business use.<\/p>\n<\/p>\n

Install a gateway (simplified)<\/h3>\n

Step 1:<\/strong> Download from Microsoft (free)<\/p>\n

Step 2:<\/strong> Install on a Windows server or dedicated computer that: – Is always switched on – Has access to the database – Has a stable internet connection<\/p>\n

Step 3:<\/strong> Log in with a Power BI account<\/p>\n

Step 4:<\/strong> Register the gateway in Power BI Service<\/p>\n

Step 5:<\/strong> Configure data sources (server name, authentication)<\/p>\n

IT work:<\/strong> 1-2 hours the first time. After that, automatic.<\/p>\n

Performance: Best practices<\/h2>\n

Poor database performance ruins the user experience. Here is what we have learned:<\/p>\n

1. Filter early and hard<\/h3>\n

Bad:<\/strong> Importing 10 years of data when you only analyse the last year.<\/p>\n

Good:<\/strong> Filter in Power Query: WHERE Datum >= DATEADD(year, -1, GETDATE())<\/code><\/p>\n

Result:<\/strong> 90% less data = 10x faster reports.<\/p>\n

2. Use views for complex queries<\/h3>\n

Bad:<\/strong> Writing complex SQL in Power BI with many JOINs and subqueries.<\/p>\n

Good:<\/strong> Ask your DBA to create a view on SQL Server. Power BI connects to the view.<\/p>\n

Advantage:<\/strong> SQL Server optimises the query. Easier to maintain.<\/p>\n

3. Partition large tables<\/h3>\n

For datasets >5 million rows, use Incremental Refresh<\/strong> in Power BI.<\/p>\n

Instead of refreshing the whole table every time, only new\/changed rows are refreshed.<\/p>\n

Result:<\/strong> Refresh time from 2 hours to 10 minutes.<\/p>\n

4. Index the right columns<\/h3>\n

On the SQL Server side: make sure the columns you filter on (Date, CustomerID, etc.) have indexes.<\/p>\n

Test:<\/strong> If a SQL query takes >5 seconds in SSMS, Power BI will be slow.<\/p>\n

Contact your DBA to add indexes.<\/p>\n

5. Avoid SELECT *<\/h3>\n

Bad:<\/strong> SELECT * FROM F\u00f6rs\u00e4ljning<\/code><\/p>\n

Good:<\/strong> SELECT OrderID, Datum, KundID, Belopp FROM F\u00f6rs\u00e4ljning<\/code><\/p>\n

Fetch only the columns you actually use.<\/p>\n

Security and Row-Level Security (RLS)<\/h2>\n

When many users share reports you must control who sees what.<\/p>\n

Scenario: Sales dashboard<\/h3>\n

Requirements:<\/strong> – The sales manager sees the whole country – The regional manager sees their region – The salesperson sees only their own customers<\/p>\n

Solution 1: Security in SQL Server<\/h3>\n

Create SQL Server views with WHERE clauses based on the user:<\/p>\n

\n
<\/a>CREATE<\/span> VIEW<\/span> S\u00e4ljareVy AS<\/span><\/span>\n<\/a>SELECT<\/span> *<\/span> FROM<\/span> F\u00f6rs\u00e4ljning<\/span>\n<\/a>WHERE<\/span> S\u00e4ljare =<\/span> USER_NAME()<\/span><\/code><\/pre>\n<\/div>\n
Power BI connects to the view. SQL Server handles the security.<\/p>\n
Advantage:<\/strong> Centralised security. Disadvantage:<\/strong> Requires separate views for different roles.<\/p>\n
Solution 2: Row-Level Security in Power BI<\/h3>\n
Define roles in Power BI Desktop:<\/p>\n
Role: Salesperson<\/strong> DAX filter: [S\u00e4ljare] = USERNAME()<\/code><\/p>\n
Role: Regional manager<\/strong> DAX filter: [Region] = LOOKUPVALUE(Anv\u00e4ndare[Region], Anv\u00e4ndare[Email], USERNAME())<\/code><\/p>\n
After publishing, users are assigned roles in Power BI Service.<\/p>\n
Advantage:<\/strong> Flexible, controlled in Power BI. Disadvantage:<\/strong> RLS rules can become complex.<\/p>\n
Our recommendation:<\/strong> SQL Server security for simple scenarios. RLS for complex or dynamic permissions.<\/p>\n
Common problems and solutions<\/h2>\n
Problem: \u201cCouldn\u2019t connect to SQL Server\u201d<\/h3>\n
Causes:<\/strong> – Wrong server name (test in SSMS first) – Firewall blocks port 1433 – SQL Server does not accept remote connections<\/p>\n
Solution:<\/strong> – Verify the server name and port – Check the firewall rules – On SQL Server: Enable TCP\/IP in SQL Server Configuration Manager<\/p>\n
Problem: \u201cData source too large\u201d<\/h3>\n
Cause:<\/strong> The Import model exceeds 10 GB (the Power BI Pro limit).<\/p>\n
Solutions:<\/strong> 1. Filter more data in Power Query 2. Remove unnecessary columns 3. Use aggregations (summarise on the SQL Server side) 4. Upgrade to Power BI Premium (100 GB+ limit) 5. Switch to DirectQuery<\/p>\n
Problem: \u201cGateway is offline\u201d<\/h3>\n
Cause:<\/strong> The gateway computer is switched off or has lost its connection.<\/p>\n
Solution:<\/strong> – Check that the gateway computer is switched on – Test the internet connection – Restart the gateway service – Update the gateway to the latest version<\/p>\n
Problem: \u201cRefresh failed\u201d<\/h3>\n
Causes:<\/strong> – Authentication error (password changed) – SQL Server temporarily unavailable – Timeout (the query takes too long)<\/p>\n
Solution:<\/strong> – Update the credentials in the dataset settings – Increase the timeout in the gateway settings – Optimise SQL queries for faster execution<\/p>\n
Next steps<\/h2>\n
You now have the foundation for connecting Power BI to SQL Server and Access. Here is your checklist:<\/p>\n
For SQL Server:<\/strong> – [ ] Verify access with SSMS – [ ] Decide Import vs DirectQuery (Import for most) – [ ] Connect from Power BI Desktop – [ ] Filter data in Power Query – [ ] Publish and set up scheduled refresh – [ ] Install Gateway if on-premises<\/p>\n
For Access:<\/strong> – [ ] Connect to the Access file – [ ] Clean data in Power Query – [ ] Build the report – [ ] Consider migrating to SQL Server for a long-term solution<\/p>\n
Optimisation:<\/strong> – [ ] Filter data early (Power Query) – [ ] Remove unnecessary columns – [ ] Use SQL Server views for complex queries – [ ] Test performance and adjust – [ ] Implement security (RLS or SQL Server)<\/p>\n
Need help?<\/h2>\n
Database integrations can get complex. At Excel Department we have done this hundreds of times.<\/p>\n
We can help with:<\/strong> – Evaluating Import vs DirectQuery for your needs – Setting up Power BI Gateway – Optimising SQL queries for performance – Implementing Row-Level Security – Migrating from Access to SQL Server – Troubleshooting connection problems<\/p>\n
Contact us<\/a> for a free consultation (30 min).<\/p>\n
Read more:<\/strong> – Power BI for business: Complete guide<\/a> – SQL-connected Excel<\/a> – Access consultant<\/a><\/p>\n
\n
Contact Excel Department:<\/strong> – Phone:<\/strong> 010-264 20 20<\/a> – Email:<\/strong> info@exceldepartment.se<\/a> – Address:<\/strong> Slottsbacken 8, 111 30 Stockholm<\/p>\n<\/div>\n
\n
Need help with this?<\/strong> Our Power BI consultants<\/a> can help you \u2013 the first consultation is free of charge.<\/p>\n
Book a free call<\/a> or phone 010-264 20 20<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Connect Power BI to SQL Server, Azure SQL and Access. DirectQuery vs Import, best practices and performance optimisation. A practical guide from Excel Department.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-1889","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/1889","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/comments?post=1889"}],"version-history":[{"count":0,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/1889\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/media?parent=1889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/categories?post=1889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/tags?post=1889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}