Forum Discussion
Unable To Publish PowerBI Report Build With SSAS Live Connection
We are stuck with an issue when publishing a Power BI report built on Power BI Desktop using SSAS (SQL Server Analysis Services Live Connection). When we publish this report to the Power BI Service, it is not accessible and shows an error. The automatic connection created as a result of publishing this report is also offline. However, I have created another new data source connection in app.powerbi.com with my local SSAS and Power BI Gateway, which is online.
Do you have any idea or experience dealing with Power BI and SSAS live connections? If yes, can you help resolve this issue, or suggest any other way to publish a report with an SSAS live connection?
Hi SMMubashir01 ,
Thanks for clarifying that you’re using a local Windows account and not AD. In this setup, the issue is that UPN mapping does not work with local accounts. For SSAS live connections, Power BI sends the signed-in Power BI account as the EffectiveUserName. That value must resolve to a valid domain identity in Active Directory. Local accounts aren’t part of a domain, so the mapping you tried with Mubashir-PC\Mubashir won’t be recognized by SSAS, and the connection will continue to fail.
In other words, SSAS live connections through the gateway require the SSAS server to be domain-joined and the accounts to be domain identities. Without AD, the translation from Power BI identity to SSAS user identity can’t happen, which is why you’re seeing the persistent “data source access error.”
You can review Microsoft’s explanation of how authentication with Analysis Services live connections works here:
https://powerbi.microsoft.com/en-us/blog/how-authentication-with-analysis-services-live-connections-works/There’s also a similar discussion on this in the community:
https://community.fabric.microsoft.com/t5/Power-Query/SSAS-MD-Power-BI-Gateway-Effective-user-names/td-p/210235Hope this helps. Please reach out for further assistance.
Thank you.
10 Replies
The live connection must be able to rachide your SSAS instance. Is the SSAS on premises on your server or in Azure?
If is on premises you need a gateway
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Steps I Took:
1) Created an SSAS Database with a Cube
- Set up a SQL Server Analysis Services (SSAS) database with a cube on my local machine for testing.
2) Developed a Power BI Report with a Live Connection
Created a report in Power BI Desktop using a live connection to the local SSAS database.
3) Installed Power BI Gateway (Standard Version)
Deployed the Power BI Gateway (Standard) on my local machine to enable connectivity between on-premises data and the Power BI service.
4) Configured Power BI Service with a Pro Account
Signed into app.powerbi.com using a Power BI Pro account and set up the necessary workspace.
5) Connected the Gateway to Power BI Service
Linked the Power BI Gateway to the Power BI service.
Established a data source connection to the local SSAS server and database , which showed as online .
6) Published the Report – Encountered Errors
When publishing the report from Power BI Desktop to the Power BI Service , the published report failed to refresh and displayed errors.
7) New SSAS Connection Created (Offline)
After publishing, a new connection to the SSAS server was automatically generated in the Power BI service, but it remained in an offline state.
Hi SMMubashir01 ,
Thanks for posting in Microsoft Fabric Community.
Could you share the exact error message you’re getting when publishing the report in the Power BI Service?
If it’s similar to “There was a data source access error. Please contact your gateway administrator”, this can happen if the UPN mapping in your gateway data source isn’t set correctly.
In that case, review the UPN mapping configuration for the SSAS data source in your gateway and ensure the account used in the Power BI Service is mapped to a valid Windows account with access to the SSAS model.Manage your data source – Analysis Services documentation covers the steps in detail.
Also make sure that gateway is updated to latest version.
Similar discussion: Solved: SSAS Live Connection Publishing Error - Microsoft Fabric Community
Hope this helps. Please reach out for further assistance.
Thank you.
Hi SMMubashir01 ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.
Hi SMMubashir01 ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.Actually I have tryed a number of options I have added the user, "NT Service\PBIEgwService" and My system User " MUBASHIR-PC\Mubashir" to the user group with following description" Members in the group have the required access and privileges to be assigned as the logon account for the associated instance of SQL Server 2022 Analysis Services. " and then restarted the PowerBI on-premises Gateway. Deleted the report in PowerBI service and then republished the report from PowerBI desktop and still getting the same error in PowerBI service " There was data source access error, please contact the Gateway Administrator "
Hi SMMubashir01 ,
Thanks for the update.The error you’re seeing generally points to an identity mapping issue rather than a permissions problem with the service account you added. When Power BI connects to SSAS through a live connection, it passes your Power BI sign-in email as the effective username. SSAS expects this to match a valid UPN in your Active Directory. If it doesn’t, the connection fails even though the gateway shows online. Configuring UPN mapping in the data source settings usually resolves this. More details are here: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot and also explained in this community post: https://community.fabric.microsoft.com/t5/Service/There-was-a-data-source-access-error-Contact-gateway/m-p/3301383
It’s also important to confirm that the SSAS server and the gateway machine are in the same domain or in trusted domains. If they’re not, the user identity translation won’t succeed and you’ll see the same error.
If you want to validate this further, you can check the effective username being passed in the gateway logs and compare it with your AD UPN (you can run whoami /upn locally to see what SSAS expects). If they don’t align, mapping is required. Guidance is provided here: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot
Hope this helps. Please reach out for further assistance.
Thank you.
