Hi all,
Attempting to setup SSAS live connection with a test report published on PBIS but I keep getting the error, “there was a data source error”:
I know the problem is related to my user not being able to connect to the SQL server because it’s not authenticated. I’ve reviewed this site and watched the videos:
Manage SQL Server Analysis Services data sources - Power BI | Microsoft Learn
But I don’t need UPN mapping as my entra ID is the same as my UPN. Even if I use the replace option to change my effectiveusername to the same as my sql server login (I use replace * with effectiveusername) this still doesn’t help.
My SQL Server is a very basic setup as I’m just running it on my laptop. I’ve added my “DOMAIN\username” to the security login on my SQL server. I’m just using the windows login for analysis server, which is the same login used when creating my tabular model in Visual studio.
My impersonation for the tabular model in VS is the same windows user name & password too. The VS connection works all fine and the PBI file connection is all fine. Everything is ok until I upload the report to PBIS.
The connection in PBIS is ok (no error on the connection screen in PBIS) , the refresh works, as it’s live connection but it’s just the final opening of the report in PBIS that gives that error.
Where am I going wrong here? Is it the gateway setup? The gateway works fine for other connections, I have a direct query to SQL server that runs via the gateway, all good, import also fine.
It’s just SSAS that gives me problems.
I know there’s a lot of moving parts so it’s difficult to diagnose just by reading this but I hope someone can point me in the right direction as I really don’t know where to go next.
Any help is appreciated.