Connecting datasets to SQL database in Power BI Service for automatic refresh

[Anonymous]

Hi, 

I want to schedule automatic refresh in Power BI Service. I import several datasets to Power BI Desktop from different databases in SQL Server, and I have some append and merge queries in Power BI Desktob as well. I am aslo able to refresh the report without any problem in Desktop. However, when it comes to Power BI Service, I am getting an error message: 

Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Information is needed in order to combine data"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"SQL\",\"path\":\"<ip> ip address here</ip>;database name \"},{\"kind\":\"SQL\",\"path\":\"<ip>ip address here</ip>;database name here\"}....

The error message goes like this with giving me names of databases that I use and ends like this: {"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacySetting"}}],"exceptionCulprit":1}}} Table: All_Funds.

All_Funds is a table in which I combined all other tables. 

I would really appreciate if anyone can help me to solve this problem. 

Thank you!

[Anonymous]

It worked. Thank you very much!

[Anonymous · Accepted solution]

Hi @Anonymous ,

It seems the error message you are receiving is related to a privacy setting in Power BI Service. By default, Power BI Service applies a privacy level to each data source based on the data source type and the location of the data source. If you are combining data from multiple data sources with different privacy levels, you may encounter this error.

To resolve this issue, you can try adjusting the privacy levels for your data sources in Power BI Desktop before publishing the report to Power BI Service. You can do this by going to "File" -> "Options and settings" -> "Options" -> "Security" -> "Data source settings". From there, you can select each data source and adjust the privacy level as needed.

Once you have adjusted the privacy levels in Power BI Desktop, you can publish the report to Power BI Service and set up automatic refresh. To do this, go to "Settings" -> "Datasets" -> select your dataset -> "Schedule Refresh". From there, you can set up a refresh schedule and configure any necessary credentials or gateways.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.