My data refresh has stopped

[Misha]

And I got the following message

Fout in de gegevensbron: {"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":"Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The received certificate has expired.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"20"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"sql\\sql2019,55219;amelior_live_v5_bis"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The received certificate has expired.)"}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"-2146893016"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: vfaktuur.
URI cluster: WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activiteits-id: 15bd810a-8a23-445e-aff7-86ac7541b50a
Aanvraag-id: 8c161aee-1c47-2f56-644b-279068bac8c9
Tijd: 2023-03-29 11:16:32Z

It would be great if anyone could help me

Thanks in advance, yrs sincerely, Misha

[v-yanjiang-msft]

Hi @Misha ,

According to the error"the received certificate has expired", please change the encryption settings for the  data source option in  Power BI Desktop > Data Source Settings > uncheck the 'encrypt connections' check box (for the data source). 

According to the official document:

SQL Server only checks the validity of the certificates at the time of configuration. For example, you won't be able to use Configuration Manager on SQL Server 2019 (15.x) and later versions, to provision an expired certificate. SQL Server will continue to run without problems if the certificate expires after it has already been provisioned. But some client applications like Power BI check the validity of the certificate on each connection and will error out if the SQL Server instance is configured to use an expired certificate for encryption. We recommend that you don't use an expired certificate for SQL Server encryption.

Best regards,

Community Support Team_yanjiang

[Misha]

Tx for your reply @v-yanjiang-msft.  I've forwarded the topic to our external IT-partner that at the same moment resolved the problem and underneath I provide you their reaction

de encryptie afzetten is natuurlijk een workaround en geen oplossing. Er wordt gebruik gemaakt van up-to-date certificaten, maar er zat nog ergens een oud certificaat gekoppeld in het register.

De oplossing werd uiteindelijk ook gevonden op het powerBI forum. 

Gateway SSL Provider, error 0 – The received certi... - Microsoft Power BI Community

1. Please login to the machine where sql server is running
2. Open SQL Server Configuration Manager
3. navigate to SQL Server Network Configuration and expand it to see "Protocols for MSSQLSERVER"
4. Right click on Protocols for MSSQLSERVER" and go to properties 
5. click on certificates.
6. open the drop down to verify if the correct certificate is selected or not. If no certificate is selected please select desired certificate.
7. If you do not want to use any certificate, then leave it empty and move forward with rest of the steps.
8. Make a note of the name and thumbprint of the certificate that is supposed to be used. (step 9 is not applicable is you do not want to use any certificate)
9. Now go to registry editor and navigate to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCENAME>\MSSQLServer\SuperSocketNetLib
10. double click on Certificate 
11. See the field "Value data". This shows the thumbprint of the certificate. Validate this with the one collected in step # 7. It should be same.
12. If it is different just clear the data in the field named "Value data:" and click ok. If no certificate is used, then ensure that field is empty. If you see some value clear the contents and click on ok.
13. Restart the sqlserver process so that the changes can be picked up. Restart it at your convenience if the server is production server/critical environment and verify if the refresh issue is fixed .

[v-yanjiang-msft]

Hi @Misha ,

Thanks for your solution! Other user who has the same problem will benefit from that. As your problem was resolved, I will close the thread.

Best regards,

Community Support Team_yanjiang