SSL Provider: The received certificate has expired

Question

Double posting my ServerFault question: https://serverfault.com/questions/1116324/ssl-provider-the-received-certificate-has-expired

I get the following error while running a stored procedure (<my_stored_procedure_name> in the error log):

   Error: Executing the query "<my_stored_procedure_name>" failed with the following error: "An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: The received certificate has expired.  
   [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection  
   An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: The received certificate has expired.  
   [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection  
   The statement has been terminated.  
   The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  

The stored procedure connects to an Azure managed SQL Server via a job defined on a local Windows Server 2019 Machine, running MS SQL Server 2019 Standard Edition. Then Azure SQL Server connects to another local Windows Server 2019 Machine, running MS SQL Server 2019 Standard Edition. This is where the error occurs.

How to fix this?

EDIT

It has been suggested in the comments that I replace the SSL certificate.

That much is clear to me from the error log. Perhaps I wasn't very precise in my question. I would like to know how and where I have to replace this certificate.

Answer 1

Hi, @Janez Kuhar Thanks for your patience, Below are the steps you can follow
login to the machine where SQL server is running and Open SQL Server Configuration Manager navigate to SQL Server Network Configuration and expand it to see "Protocols for MSSQLSERVER" and go to properties to see a new dialogue box appear as shown

on certificates open the drop-down to verify if the correct certificate is selected or not. If no certificate is selected you can select the desired certificate. If you do not want to use any certificate, then leave it empty and move forward with the rest of the steps.
Make a note of the name and thumbprint of the certificate that is supposed to be used.you can skip this if you do not want to use any certificate
Now go to the registry editor and navigate to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCENAME>{=html}\MSSQLServer\SuperSocketNetLib
double click on Certificate to see a dialogue box shown in below screen

See the field "Value data". This shows the thumbprint of the certificate. Validate this with the one collected in as mentioned above It should be the same.
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.
Once done please restart the sqlserver process so that the changes can be picked up. Please note that you may not do it immediately if you have dependencies such as the production server/critical environment.
you can restart it at your convenience and verify to see if the refresh issue is fixed or not.
sometimes applying the patches to the operating system or to sql server can cause this failure during refresh.
Restarting the machine where the gateway is hosted has also fixed issues in the past. We can try restarting the machine and see if that fixes the problem.

once it is installed restart the gateway machine and perform the refresh again

Hope this will help. Please let us know if any further queries.
Regards
Geetha
Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.