Publishing succeeded, but Power BI needs credentials for the data source

[LogicoCR]

Hello!

Systems and applications related: Power BI, Dynamics 365 FO, Connection Gateway.

Issue: I have created the gateway. It shows online. I added the DataSource and it shows Online. Yet, when I try to publish the report I received a "Publishing succeeded, but Power BI needs credentials for the data source".

If I go and check the report and dataset have been published but I am not able to run the report in Power BI.

The report does works on Desktop and it is using a local database in one of our sandbox VMs.

Any idea of what can I do or check?

[v-huizhn-msft]

Hi @LogicoCR,

In Power Bi service, please right the dataset->schedule refresh->Data source credentials, and check Data source credentials is connect successfully as @Ormesome said.

Best Regards,
Angelia

[LogicoCR]

Thank you for your reply Angelia!

I am not sure if I am doing this as you said, but in powerbi.com I went to the Dataset, Schedule Refresh but there are no Data Source Credentials in there.

See attachment.

[Ormesome]

I would check first that the server and database name in your connection string in Power BI Desktop match those in the gateway.

Then I would check that the account specified in the gateway has the same database permissions as the account you're using locally.

[LogicoCR]

Thank you for your reply Ormesome!

The database and server are the same. The users were not. I checked and the user I was using on the gateway did not have the correct permissions to the database.

So I corrected that (I can now connect with that user/pass to the Database using SQL Server Management) and also from Power BI Desktop, I changed the Source Settings of the report so they'll be using the same username/pass as the one I am using at the gateway. 

After I changed those permissions I was able to Refresh the report in Power BI Desktop but I am still getting the same message when trying to publish it.

Any other ideas?

[LogicoCR]

I think I am missing something.

Do I have to tell the report to use a specific gateway? If so, where or how do I do that?

The error message now it is this one

But I have a Gateway configured using same user/pass and server/database.

[Ormesome · Accepted solution]

I'm sure someone more knowledgeable can correct me if I'm wrong, but by understanding is that the data source will choose a gateway based on the name of the server and the name of the database. In your screenshot I assume that you've deleted the name of the server out of security concerns when posting on the the internet.

Ensure that you're using the actual server name, and not "localhost" or an IP address in the report.

[LogicoCR]

It worked!!!

Thank you! 🙂

[LogicoCR]

Thanks Ormesome.

I am using localhost in both ends. I will set the real server name in both and check.

Will let you know.