Forum Discussion
Connecting to data source failed in Analysis Services live connection
Hello all, I hope for your help,
The configurations:
- The data is stored in MS SQL Server (2019 Standard edition), Analysis Services multidimensional OLAP cube in a local Windows Server
- The report was built in Power BI desktop, up to date version using a PBI Pro user
- The Power BI Gateway Standard mode was installed on the same Windows server as the MS SQL Server
- In the PBI Service connections configurations page, I created a connection to the Analysis Services date base using the PBI Gateway with a username (domain\ user) who has server admin access to the Analysis services instant on the SQL server
More facts:
- There is no problem viewing the data while in the desktop application
- All checks in the PBI service are green
- Reports that are connected to SQL server DB or external files (Direct Query or Import mode), work fine
Any thoughts?
Hi hoffavi ,
Thank you for reaching out to the Microsoft Community Forum.
Please try below general workarounds to fix the issue.
1. Please check the data source is configured as Analysis Services not SQL Server. Server name and database must match your .pbix connection string exactly, including the instance name.
2. In the gateway data source --> Advanced settings, enable "Use the effective username for live connections". The credentials account must be an SSAS Server Administrator only server admins can impersonate users via
EffectiveUserName.
3. Add Gateway Account as SSAS Admin In SSMS --> connect to Analysis Services --> right-click server --> Properties --> Security --> add your gateway service account like NT Service\PBIEgwService.
4. Assign SSAS Cube Roles to Users, every PBI Service user viewing the report needs at least Read permission via an SSAS database role. The user is identified by their UPN ([email protected]), which must match their Azure AD identity.
5. Check Gateway Logs On the gateway machine, check C:\Users\<GatewayServiceAccount>\AppData\Local\Microsoft\On-premises data gateway\GatewayErrors*.log. Search for EffectiveUserName or ConnectionException to find the exact error.Note: If still not resolved your issue. Please share Power BI Service error message. And is your SSAS is default or named instance? Is your SSAS running under Local System or domain account?. could you please check the above things and share the details, it will help us to fix the issue. Do not include sensitive information.
Regards,
Dinesh
9 Replies
I’ve seen this before with Analysis Services live connections — it’s almost always an authentication or gateway issue, not the model itself.
In most cases the problem comes down to one of these:
The gateway isn’t properly configured or mapped to the SSAS source
Credentials are wrong or expired in the Service
Using a fixed Windows account instead of proper impersonation (this breaks EffectiveUserName)
Permissions missing in SSAS for the user or service account
Or even TLS / certificate trust issues between gateway and SSASIn short: Power BI Service → Gateway → SSAS has to be fully aligned. If any layer fails (auth, permissions, or trust), the connection breaks.
What worked for me:
Reconfigure the data source in the gateway
Remove fixed credentials and allow proper delegation
Re-enter credentials in the dataset
Validate SSAS permissions for the account being usedAfter that, the connection usually starts working again.
Hi Juan-Power-bi , thank you for your answer. re entering the credentials dosen't worked as the problam is from day one. i know there something to do with the authentication between the gateway and the server but couldn't find what exacly
Hi hoffavi
I would highly recommend following the Microsoft documentation below to make sure that you have got all the items configured correctly. Manage SQL Server Analysis Services data sources - Power BI | Microsoft Learn
HI GilbertQ, thamk you for your answer but the document dosn't gave much help or any new ideas
Hi hoffavi ,
Thank you for reaching out to the Microsoft Community Forum.
Hi GilbertQ and Juan-Power-bi , Thank you for your prompt responses.
Hi hoffavi , could you please try the proposed solutions shared by GilbertQ and Juan-Power-bi ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.
Regards,
Dinesh
Hi hoffavi ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi v-dineshya , still nothing worked for me
Hi hoffavi ,
Thank you for reaching out to the Microsoft Community Forum.
Please try below general workarounds to fix the issue.
1. Please check the data source is configured as Analysis Services not SQL Server. Server name and database must match your .pbix connection string exactly, including the instance name.
2. In the gateway data source --> Advanced settings, enable "Use the effective username for live connections". The credentials account must be an SSAS Server Administrator only server admins can impersonate users via
EffectiveUserName.
3. Add Gateway Account as SSAS Admin In SSMS --> connect to Analysis Services --> right-click server --> Properties --> Security --> add your gateway service account like NT Service\PBIEgwService.
4. Assign SSAS Cube Roles to Users, every PBI Service user viewing the report needs at least Read permission via an SSAS database role. The user is identified by their UPN ([email protected]), which must match their Azure AD identity.
5. Check Gateway Logs On the gateway machine, check C:\Users\<GatewayServiceAccount>\AppData\Local\Microsoft\On-premises data gateway\GatewayErrors*.log. Search for EffectiveUserName or ConnectionException to find the exact error.Note: If still not resolved your issue. Please share Power BI Service error message. And is your SSAS is default or named instance? Is your SSAS running under Local System or domain account?. could you please check the above things and share the details, it will help us to fix the issue. Do not include sensitive information.
Regards,
Dinesh
