We have been piloting the use of Datamarts at my company. Recently we have this error when trying to connect to the SQL endpoint:
This error is seemingly random and only occurs to certain users across certain sql endpoints, meaning its not all users on a specific endpoint or all endpoints on a specific user.
Additionally I have tried connecting from online serverices (Dataflow, dataset), PBI Desktop, and Excel. All of these have these present the same error. Resetting datasource credientials or privacy settings has not helped.
I am completely stuck and have found no resources online for this error currently.
I am using multiple Datamarts with SQL endpoint for the users to query the data through Excel and PowerBI. Everything was working fine for nearly a year until this week when the users reported the exact same error you got. However, everything was working fine for me. So, I thought this must be user access related as I was the only admin.
I am not sure how you gave the users access, but I was using the Manage Permissions option available by the three dots icon next to the Datamart name. I've tried to add a user all permissions, but that still didn't work. So I was looking around in the PowerBI platform for any changes and I've noticed the new icon OneLake data hub, (maybe it was there for some time, but I haven't noticed it before).
Then, in an Explorer view click on the Datamart you want to edit, and there is an option to Share:
When you click Share datamart button, it actually says that this is used to give users permission to query SQL endpoint, and when I shared the Datamart with a user the queries started working as expected.
NOTE: you can also Share directly from the Workspace, when you hover over the Datamart name.
So, I think there was a change over the weekend within the PowerBI platform and the SQL can be now queries only with Share not the access permissions used before. Not sure if it was comunicated in any way and it would be great if Microsoft, would just transfer the current users to the new shared pool.