Troubleshooting Scheduled Refresh for Azure SQL Databases in Power BI
It is usually a Monday. Someone in finance opens the sales dashboard, sees last Thursday's numbers, and sends a short, polite email that is anything but relaxed. The model has been set up to refresh every morning from an Azure SQL Database. The refresh has been failing quietly for three days, and nobody noticed because the owner of the report went on leave.
This is one of the most common problems we get called in to fix on Power BI work for Australian organisations. Azure SQL is a sensible place to put a data warehouse or an operational reporting database, and the connection is not hard to set up. The trouble is that scheduled refresh runs from a completely different place to where you sit when you build the report, and that gap causes most of the failures.
Microsoft's documentation on this is brief. It covers one specific error and one specific fix, and it is a good starting point. But the page does not tell you why the fix works, what it costs you in security terms, or what to check when the fix is already in place and the refresh still fails. That is what I want to cover here, based on what we have seen work on client engagements.
The error you will see
When you set up scheduled refresh for an Azure SQL database, you enter credentials for the data source. If you then try to edit those credentials and get an error with code 400, the problem is almost never the username or password. The error is misleading in that way. The Power BI service is trying to reach your database to validate the connection, and it cannot get through.
The fix Microsoft documents is this:
- Sign in to the Azure portal.
- Open the Azure SQL database you are configuring refresh for.
- On the Overview page, select Set server firewall.
- On the Networking page, tick Allow Azure services and resources to access this server, then save.
Once that is saved, go back to the Power BI service, edit the credentials again, and the connection test should pass. In most cases, that is the end of the story.
Why this setting is needed at all
Power BI Desktop runs on your laptop, and it connects to the database from wherever your laptop happens to be. That is why a report can refresh fine in Desktop and then fail in the service. When the scheduled refresh runs, the Power BI service connects from infrastructure hosted in Azure, not from your office network and not from a gateway you control.
If your Azure SQL server has a firewall that only allows your office IP address, the service's connection is refused before it even gets to the authentication step. The error that comes back is about credentials because that is the step the setup screen was on, but the real problem is network reachability.
The Allow Azure services option tells the server to accept connections from Azure services. That is what the Power BI service needs.
The trade-off that most guides skip
I want to be direct about this, because I have seen teams click this option without understanding what they had just changed. Allowing Azure services is broader than it sounds. It is not limited to your subscription, your tenant, or your Power BI workspace. It permits connections from Azure services generally, which means the server accepts traffic from a wider set of sources than an IP allowlist would.
Whether that matters depends on what is in the database. For a development or test database holding anonymised data, I would not lose much sleep over it. For a production database holding customer records, payroll, or health information, I would be uncomfortable with the setting on its own.
The mitigations we usually recommend are these:
- Use Microsoft Entra ID authentication on the server, so access is tied to identities and can be revoked centrally, rather than SQL logins with shared passwords.
- Create a dedicated read-only account or service principal for the refresh, with permissions limited to the schemas and views the report actually uses. Do not use a database owner account because it was the one that worked during setup.
- Check whether private endpoints or a virtual network rule are already in use. If the database is locked down behind a private endpoint, the public Allow Azure services route may not be the right approach at all.
- If your security team does not want the broad setting, the alternative is an on-premises data gateway or a virtual network data gateway in a network path you control. That takes more setup, but it means the server only needs to trust one known source.
Some teams try to solve this with an IP allowlist containing the Power BI service IP ranges. It can work, but those ranges change, and you then own a recurring process to keep the firewall rules current. In our experience, that process gets forgotten within a year and the refresh breaks again at the worst possible moment. If you go that way, put the update on a calendar with a named owner.
Credentials that break quietly
Once the firewall is sorted, the next common failure is the credential itself. Two patterns come up again and again.
The first is SQL authentication with a password that gets rotated. Someone in the database team changes the password as part of a routine security exercise, the report keeps the old one, and the refresh fails from that moment on. The error message does not always make this obvious. You get a login failure, and it takes a while to trace it back to the rotation.
The second is a credential tied to a person who has left. If the refresh was configured using an individual's Entra ID account, and that account is disabled when they leave the business, the refresh stops. This is nobody's fault in particular, but it is a predictable outcome that gets missed in offboarding.
Where we can, we set refresh up using Entra ID authentication with a service principal or a dedicated service account that is not attached to a person. It takes a little more effort at setup, and it removes an entire category of 3am incidents. Your tenant admin may need to enable service principal access for Power BI first, so raise that early rather than halfway through a project.
When the firewall is fine and the refresh still fails
Suppose the Allow Azure services setting is in place, the credential test passes, and the refresh still fails intermittently. Here is the checklist we work through.
Serverless compute and auto-pause. If the database is on the serverless tier, it can pause after a period of inactivity. The first connection after a pause has to wait for the database to resume, and a refresh that starts in that window can time out. We see this most often with overnight refreshes against databases that are idle during the day. The fix can be as simple as a scheduled warm-up query shortly before the refresh, or moving to a provisioned tier if the workload justifies the cost. Be honest with the client about the cost difference either way.
Query timeouts. A Power Query step that runs fine against a small test table can take far too long against the full production table. Check the refresh history for the duration before the failure. A refresh that runs for twenty minutes and then fails is telling you something different from one that fails in ten seconds. Look at whether the query can be folded to the database, so that the work happens on the server and not in the refresh engine. If it cannot, consider moving the heavy transformation into a view on the database side.
Large tables without incremental refresh. If the model reloads a table with hundreds of millions of rows every night, you will eventually hit the limits. Incremental refresh is worth the setup effort for any table that grows over time. It is not glamorous, but it turns a fragile full reload into a predictable daily job.
Failures that only happen on one schedule. If the refresh works when run manually and fails only when scheduled, look for conflicts with other processes. A nightly database backup, an ETL job, or an index rebuild can lock tables or consume resources at the same time. Shift the refresh window, or coordinate it with the team that owns the database.
Monitoring so you hear about it first
The most avoidable problem in this whole area is finding out about a failed refresh from a stakeholder. Set up failure notifications for the semantic model and send them to a group mailbox or a team channel, not to one individual. If the owner of the report is the only recipient, the notifications will sit unread when they are on leave, which is precisely when they matter.
We also recommend a simple weekly check of refresh history for every production model. It takes ten minutes, and it catches the slow creep of increasing refresh duration before it becomes a timeout. That habit tends to matter more than any single fix.
What I would tell a client
If your scheduled refresh against Azure SQL Database is failing with error 400, start with the Allow Azure services setting, because it is the documented fix and it resolves the majority of cases. Then take the security trade-off seriously. Use Entra ID where you can, give the refresh its own least-privilege identity, and decide consciously whether the broad firewall setting is acceptable for that database. If it is not, plan for a gateway or private networking instead of hoping an IP list will keep up.
The refresh itself is the easy part. The hard part is making sure the next person who inherits the report knows what the credential is, who owns it, and what to do when it breaks. Document that before you go live.
If you have a Power BI estate that keeps failing refreshes and nobody quite knows why, our Power BI consultants do this kind of tidy-up regularly, and it is often a faster win than people expect. For broader reporting work, including how Power BI sits alongside the rest of your data platform, our AI for business intelligence team can help you plan it properly.
The original Microsoft article is Troubleshoot scheduled refresh for Azure SQL databases on Microsoft Learn. It is worth reading alongside this one, particularly for the exact steps in the Azure portal.