I am connecting to a SQL Serverless endpoint located in my Azure Synapse Account for my PowerBI Datasets. I am reading a SQL view that is composed of some parquet files in my data lake.
I am able to connect to the data fine when using PowerBI Desktop since it is using my Active Directory account. But now that I have the report published to a PowerBI Workspace online for other users in my organization I want to have it refresh the dataset daily without having to publish it myself.
Using the online refresh settings I am able to connect to SQL just fine using basic authentication (username/password) but it looks like when PowerBI renders the view it is also checking the credentials for the underlying datalake data which it does not have access to:
Microsoft SQL: Cannot find the CREDENTIAL 'https://mydatalake.dfs.core.windows.net/parquet/**', because it does not exist or you do not have permission. Could not use view or function 'dbo.MyView' because of binding errors.
What is the best way for me to give my PowerBI Workspace permissions to read this view (along with underlying datalake files)?
I'm also puzzled that in PowerBI Workspace I had options to schedule this data set but after a few failed attempts it now states:
You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
And there is nothing after the ":" so now it appears I cannot attempt to connect anymore.
I have the PowerBI Workspace set as a linked service but that does not seem to help since I believe that is only for Synapse to use to connect to the workspace and not the other way.
I was toying with the idea of having Synapse copy the view data to a SQL database outside of Synapse so it can be shared more easily - but my preference would be to avoid this extra layer.
