Power BI Online - Missing credentials for dataset on Azure SQL DB Managed Instance

[Anonymous]

Hi all,

I have built a report in Power BI Desktop using Direct Query on an Azure SQL Database Managed Instance. To log in, I use Database credentials as we have created a user with read access to only the needed database. This works fine, I can refresh the report and data is loaded correctly.

When I publish this report to Power BI Online I get the following error:

Data source error: Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh.

Unfortunately, when I go to the dataset settings to edit credentials this is not possible, as the credentials section is greyed out:

Note: this is in a shared workspace (new type), when I tried this is my personal workspace I could edit credentials but only got the 'basic' and Oauth2 type logins. Not the database login I need and which I could use in Power BI Desktop. Also, because the datasource is in the cloud, a gateway is not needed for this specific report.

It seems as if the login type I use in Power BI Desktop is not recognized and not available in Power BI Online. I am now stuck and unable to refresh the dataset for this report. Anyone that has run into this issue, or knows how I can solve this? Thanks in advance.

Bart

[nickyvv · Accepted solution]

Hi @Anonymous,

for a connection to Azure SQL DB to use Basic authentication you need to add it to the (Enterprise, or now Standard) Gateway. That's also how we set it up. After that you can select the gateway in the settings of the dataset and it works fine.

If you want to read more about the setup, we used the approach of Securing the connection between Power BI and Azure SQL Database, with the gateway installed in the same VNet as mentioned in this article.

[Anonymous]

Thanks @nickyvv!

I've added it to our gateway and now the dataset can be refreshed, it is solved! I was trown off by the part 'You don't need a gateway for this dataset, because all of its data sources are in the cloud' under the gateway settings of the dataset. Next to that, it took very long to load our gateways on this screen. Because of that I thought I couldn't add a gateway, but when I waited it was possible and I've done as you suggested.

 Thanks!

[nickyvv]

Great to hear @Anonymous, glad you got it solved!

[collinq]

Hi @Anonymous ,

The Owner of the dataset should be able to modify the credentials.  Since it is grayed out, that indicates that you do not have permissions.  Also, when you look at the "refresh history" is that where you are getting the data credentials message or is there a different one in there?

[Anonymous]

Hi @collinq,

Thank you for your reply. I have checked my account, and am on the same account as is listed under 'This dataset is configure by ...', so I am on the owner account. I also checked the dataset settings again, and when I wait long enough, a few minutes, the data source credentials become clickable and I get a link to edit credentials.

However, now I get the same problem as I saw in my personal workspace. I get the option to sign in as 'Basic' or 'OAuth2', neither of which work with the database credentials I needed to sign in to the database on the Azure SQL Database Managed Instance. I get the following error:

It appears as if a connection cannot be made to the server, but it recognizes the source is in the cloud and a gateway is not needed, so I\m not sure why this would be.