Forum Discussion
Data flows connected to SQL are not refreshing on schedule but refreshing manually.
Hi Everyone.
I conneted my data flows from SQL Source and published to power bi service using my credentials. where my gateway is configured with service account. from past few days my scheduled refresh is failing for all linked data flows( ex: daily weekly, monthly flows created). but if I edit credentials and trigger again manually they are working sucessfully. I have DB access, Service access but not sure why this issue is happening. kindly help me to understand the route cause.
Error: Data source credentials are missing invalid.Please update the credentials for SQL in dataflows settings page and try again.[SQL Path].
11 Replies
Hi Prasanth460
This is a credential/token issue. Manual refresh works because it uses your current login token, but scheduled refresh fails because the stored credentials expired.
Please do these 3 steps:
1. Dataflow Credentials:
Power BI Service > Workspace > Dataflows > Settings > Data source credentials
Click Edit for your SQL source > Sign In again > Save2. Gateway Credentials:
Settings > Manage connections and gateways > Your Gateway > Data Sources
Click Edit for your SQL source > Re-enter credentials > Test Connection > Apply3. Service Account Check:
Make sure the service account running the gateway has db_datareader access on SQL DB and password is not set to expire. Also ensure MFA is not enabled on that account.Root cause: Dataflow and Gateway credentials must be same and valid. After updating both, do 1 manual refresh then check next scheduled run.
This should fix it. Let me know if you still get the error.
Hi Gautam_Kumar01 Thanks for the response. and detailed steps. Could you help me to know where can i find Token expiry details.
and yes Data flow and Gate way credentials are valid but not same. Gateway is configured with Service account and data flow is connected with User account.
Point 3: service accoint hase access to SQL DB.
can I directly change ownership of flow to service accoint in power BI service--> settings
or can i change by edit flow in Service and save with service account credentials.
Thanks in advanceHi Prasanth460
You nailed the root cause. Because Dataflow and Gateway credentials are different, scheduled refresh will always fail.
Ans 1: Token Expiry
There is no UI in PowerBI to see "token expiry". For SQL it depends on your DB password policy. For OAuth it’s auto-refreshed. The main issue here is not token expiry, it's mismatched accounts.Ans 2: Yes, that is the problem
Gateway running as Service Account cannot use your User Account token for scheduled runs. Both must be same.Ans 3: How to change Ownership - Yes you can do both
This is the best practice fix. Use Option B.Option A: Change Gateway to your User Account
`Settings > Manage gateways > Data Source > Edit > Sign in with your User account`Option B: Change Dataflow Ownership to Service Account - (Recommended)
1. Add the `Service Account` as `Admin` to your Workspace first
2. Go to `Workspace > Dataflows > ... > Settings > General > Take over`
3. Now go to `Data source credentials` and Sign in with the `Service Account`
4. Ensure the same `Service Account` is configured in Gateway Data Source with SQL login
5. Important: For the Service Account in AD/SQL, set `Password Never Expires` and disable `MFA`After Option B, do 1 manual refresh. Your daily/weekly scheduled refresh will start working.
Let me know after trying Option B.
Prasanth460
This is more likely your credential mapping between PBI service and gateway configuration connection.
You need to verify this step by step.
1- Configure Gateway connection with your local a/c and test the connection, if this successfull it means we have diagnose correctly.Data source type = SQL Server
Gateway connection = your enterprise gateway
Authentication method = Windows / Basic / OAuth (depending on your setup)
User name = service account or local a/c2 Dataflow credential settings in Power BI Service.
Sometimes Dataflow uses cached credentials. That can cause a problem during PBI refresh settings.
3 Gateway SQL data source mapping.
Gateway SQL data source mapping issue
4 Service account password/lock status
5 Parent dataflow credentials
If you are using nested dataflow, then you might need to check parent dataflow connection settings.
6 Gateway logs
Finally, the Gateway logs needs to be verified, as you DBA he will check the on prem Gateway logs byrunning few commands.
If this helps, ✓ Mark as Kudos | Help OthersHi Prasanth460
One further thing to mention is if you can confirm if your service account has the permissions to connect to your SQL server source. One way to test this is to login to your gateway and connect to the database using the service account details to see if you can login and connect successfully.
Hi GilbertQ , Thanks for your response, I tested with Gateway connection ad they are working and connecting with SQL DB
Hi All, Thanks for your response. Issue here is using diiferent accounts for DB and Data flows.
Solution: Make sure all the Credentials within the Flows and Power BI are matching. this resolved scheduled refresh failure IssueHi Prasanth460,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Gautam_Kumar01, Murtaza_Ghafoor and GilbertQ for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi @Prasanth460,
We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
