One or more cloud data sources for this dataset have been deleted

[lauralgt]

TLDR: PowerBI Service does not recognize when two reports are using the same on-premise data source, thus giving me errors and I have to manually re-assign them to every single report. It used to do that automatically for reports using the same data source.

Dear community,

for a month now, whenever I publish a new report from PowerBI desktop (using an SQL Server as on-premise data source and the PowerBI Gateway in Standard Mode) to the PowerBI Service, I get the following error: 

Clicking on the link does nothing, however unter the "Gateway connection" dropdown of my dataset, I can manually assign a previously added data source. Sometimes, I then get the warning: "Your data source can't be refreshed because the credentials are invalid. They are used to skip test connection". In the past, I didn't need to assign data sources manually for every single report I published because PowerBI recognized when reports used the same underlying on-premise source. 

I tried with Windows as well as Standard connection as well as with an ODBC connection string, same issue. I tried on different machines with separate gateway installation instances, same issue. I tried first creating the cloud datasource under "Manage gateways" as well as create a new cloud data source with a newly published report, same issue. I tried adjusting the security levels of the cloud data source and the on-premise data source of the report, same issue. I know the credentials are the same, I never changed them and I can still connect to the on-premise SQL server with PowerBI Desktop, SSMS and I can add it as a cloud data source manually. 

Any tips would be much appreciated since I am at a loss.

Best regards!

[v-yalanwu-msft]

Hi， @lauralgt ；

A customer has recently experienced similar problems, I would suspect this is happening because when you take over a cloud dataset the previous credentials were linked to the user who owned the dataset.

Check the datasource in Manage Gateway or you need to remap the dataset to it in gateway connection.

https://community.powerbi.com/t5/Service/How-do-I-remove-a-ghost-gateway/m-p/2156128#M143153

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

hi @lauralgt 

Can I please suggest that you follow the following guide below. I have had this running for years without any issues

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs

[zuma]

That guide was most helpful! Thank You! 🤖

[lauralgt]

Hey @GilbertQ 

thanks for your reply. I've been using the exact same steps and it works for the first report I publish with a new data source. However, PowerBI doesn't reognize it anymore when I'm using the same connection again, so now I have to manually assign the gateway connection to every following report I publish. PowerBI used to do this automatically, however now I get the "One or more cloud data sources for this dataset have been deleted" error message each time. Do you know how to fix this?

Best regards

[GilbertQ]

Hi @lauralgt 

I have actually not come across this in the past, it normally works once I set it up once even when I update and upload new PBIX files.