The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure

[TimOtterbach]

Hi there,

I have a power bi dataset which queries four different data sources: 

1. Service Now API (https connection to public cloud with username and password; result is JSON formatted)
2. internal MS SQL database (connected through data gateway)
3. two excel documents stored in MS onedrive 

As long as I just have these separate queries refreshing the data works absolutely fine with both, power bi desktop and power bi cloud service. 

Now I want to combine those queries to one table - or one query. I.e. to show data from service now and MS SQL database on the same diagram with the date as x axis and so on. 

Here everyhting worked absolutely fine while working with powerbi desktop. 

Even publishing that report to the powerbi cloud is ok. 

But if I try to refresh data from within the cloud I now get the error message: 

Zugrunde liegender Fehlercode: -2147467259
Zugrunde liegende Fehlermeldung: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Detail: https://xxxxxx.service-now.com/api/now/table/task_time_worked?sysparm_query=work_dateRELATIVEGT%40ye...
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Cluster-URI: WABI-WEST-EUROPE-redirect.analysis.windows.net
Aktivitäts-ID: fde4b8a3-4221-4dc9-a927-7868afcc2f62
Anforderungs-ID: d0919a33-ae71-a89b-5903-6bce8ad9b081
Zeit: 2022-08-15 23:08:39Z

The strange thing about it is, that this happens everytime I try to combine service now data with "join" with any other query. 

That means: there is no new query! 

The only difference is that I join service now data with other data with "join". 

Then the just the refresh of data in the cloud happens to fail with that error message. 

Refreshing the data in powerbi desktop still works fine. 

I´m sorry, but this doesn´t make any sense to me. 

It seems that power bi tries to query the data source a second time without proper usage if you combine queries. 

Any help is appreciated.

[Daryl-Lynch-Bzy]

Hi @TimOtterbach - here are some good resources to learn more about Dataflows:

Solved: When to use Dataflow vs Dataset? - Microsoft Power BI Community
What are Power BI Dataflows and their Use Cases? - RADACAD
Dataflows – BI Polar (ssbipolar.com) - Matthew Roach

Note the Dataflows are just Power Query that this used by the Power BI Desktop / Datasets.  The idea here is to breakup you Extracts, so "As long as I just have these separate queries refreshing the data".  Once each table, it available in the Dataflow table, you will link to them to transform and combine etc.

I have an example where data is combined from LinkedIn, Google, Bing and Twitter, I pull the data from each independently to my staging dataflows.  I then combine into one table by reading the data from the Dataflows (not the original source).  Read 4 Data Sources then write to 4 Dataflows then read 4 Dataflows to my single Dataset.

[Daryl-Lynch-Bzy]

Hi @TimOtterbach - it is likely that you are running into Power BI Privacy restrictions so it is prevent the combination of these Data Sources.  I am not sure that you need to combined into one table.  I don't think this is really necessary, but it will depend on your Data Models requirements.

If combining is necessary, I would advise using Power BI Dataflow to pull from the separate data sources, then combine the separate Dataflow tables.  Hopefully, this will help you get around the error message.

[TimOtterbach]

Hi @Daryl-Lynch-Bzy ! Thanks for your reply. 

I tried to figure out how to solve that with dataflows. But I couldn´t find a good ressource to get an overview about it. 

Do you know where I can learn more about it?