Gateway SSL Provider, error 0 – The received certificate has expired

[Anonymous]

Is there a way to disable ’encrypt connections’ when doing a data refresh through the On-Premise Data Gateway?

Our users have developed a report to a SQL server database that does not have encrypted connections enabled, by unchecking the encrypt connections box in PowerBI Desktop.  When they publish this report and schedule a refresh it fails with the following error:

“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).“

My DBA advised that we disable encrypted connections to that SQL server like they did in PowerBI desktop to get around this error. They will not be enabling encrypted connections on this SQL server.

Is there a way to disable this encryption for a specific data source on the Gateway server or from the PowerBI web portal?

[v-qiuyu-msft]

Hi @Anonymous, 

Please launch on-premises data gateway application and ensure the HTTPS mode is off. 

Sometimes applying the patches to operating system or to sql server can cause this failure during refresh. Please refresh again when SQL server and OS is free. 

Restarting of the machine where gateway is hosted has also fixed issues in the past. You can restart on-premises data gateway service then test again. 

Best Regards,
Qiuyun Yu

[Anonymous]

This is a global setting, is there a way to disable this per data source?  I would hate to affect all of my, otherwise working, refreshes and potantally introduce a security concern.  

[v-qiuyu-msft]

Hi @Anonymous, 

I'm afraid there is no such setting for each data source in Power BI service. 

May I know if the original issue is resolved now after turning off HTTPS mode in gateway application? 

Best Regards,
Qiuyun Yu

[Anonymous]

I have not changed this setting and am hesitant to do so.  I am concerned that I might 1. Violate some security protocol at my place of business or 2. Break one of our other production data sources. 

We are looking at adjusting this connection from SQL to OLE DB or ODBC where we can more define these settings. 

[Anonymous]

Microsoft gave us some intresting info on this ...

Please try the following steps and let us know if this helps in resolving the issue with the SQL server connector refresh in Power BI :

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 .