SSL Provider: The received certificate has expired

Janez Kuhar 26 Reputation points
2022-11-22T19:21:06.517+00:00

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.

Azure SQL Database
{count} votes

Accepted answer
  1. GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator
    2022-11-24T16:51:14.323+00:00

    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
    264023-image.png
    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

    263983-image.png
    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 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.