How to Fix:Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate].

Question

How to Fix:Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate].

Satyam 25

I followed the documentation: https://learn.microsoft.com/en-us/sql/linux/quickstart-sql-server-containers-azure?view=sql-server-ver16&tabs=kubectl to deploy SQL server on AKS. But While connecting to it, getting the below error:

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate].

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection.

sql error

Note: My previous same question was deleted by Azure Community. This is not a good sign.

Anonymous

2023-06-29T02:38:26.22+00:00

Hi @Satyam

You can take a look at the answer below. If it can help you, please accept it.
Anonymous

2023-06-30T08:38:50.72+00:00

Hi @Satyam

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Satyam 25 Reputation points

2023-07-03T13:20:13.9066667+00:00

Hi This was not helpfull at all. Why dont microsoft add it to the official documentation then?
Juan Florez 60 Reputation points

2024-01-12T15:13:51.64+00:00
I had to connect using the -C flag, which according to the documentation of 'sqlcmd' corresponds to trusting the server certificate, but it was a lucky guess cause I have not found any documentation that indicated it is the correct way to proceed in this scenario.

user@pc_name:~/Desktop$ sqlcmd -?
Ale Ruiz 65 Reputation points

2024-03-14T22:50:04.76+00:00
As suggested by Juan, adding the -C flag: "Trust Server Certificate" works for me.

sqlcmd -S localhost -U [username] -C

2 answers

Your answer

Anonymous

2023-06-29T02:38:26.22+00:00

Hi @Satyam

You can take a look at the answer below. If it can help you, please accept it.
Anonymous

2023-06-30T08:38:50.72+00:00

Hi @Satyam

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Satyam 25 Reputation points

2023-07-03T13:20:13.9066667+00:00

Hi This was not helpfull at all. Why dont microsoft add it to the official documentation then?
Juan Florez 60 Reputation points

2024-01-12T15:13:51.64+00:00

I had to connect using the -C flag, which according to the documentation of 'sqlcmd' corresponds to trusting the server certificate, but it was a lucky guess cause I have not found any documentation that indicated it is the correct way to proceed in this scenario.

user@pc_name:~/Desktop$ sqlcmd -?
Ale Ruiz 65 Reputation points

2024-03-14T22:50:04.76+00:00

As suggested by Juan, adding the -C flag: "Trust Server Certificate" works for me.

sqlcmd -S localhost -U [username] -C

Answer 1

Sreeju Nair 12,666

It seems the problem is with the certificate. With ODBC driver 18.0, the default value for the Encrypt is set to true. I recommend you to read the following blog post.

https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228

Try connecting the SQL Server instance by specifying no encryption. see the following part from the documentation that may help you.

The action item if you are affected by the Encrypt change is to either (in order of recommendation):

    Install a trusted certificate on your server.
    Change your client's Encrypt connection string setting (or connection property) to optional/no.

If you are using a self-signed certificate and the Force Encryption setting on the server to ensure clients connect with encryption, you will need to do one of the following (in order of recommendation):

    Change to a certificate that is trusted as part of the client's trust chain.
    Add the self-signed certificate as a trusted certificate on the client.
    Change your client's TrustServerCertificate connection string setting (or connection property) to yes.

Refer: https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-utility?view=sql-server-ver16

Jaap Titulaer 0 Reputation points

2024-05-05T09:24:26.5366667+00:00

The error also occurs on new MS Windows 11 64 bit install using 64bit ODBC Data Source Administrator (as admin) with latest 64bit MS SQL Server ODBC version 18 installed, RDBMS is running locally (SQL Server 2022, Dev. Ed.).

The issue is with the driver, it does not allow any changes (e.g. column encryption is off by default), trying to change the default DB to the one needed for the ODBC link leads to this error. Version 17 on same setup doesn't have this error. Ergo 18 assumes something that 17 doesn't.
Jaap Titulaer 0 Reputation points

2024-05-05T09:50:27.1233333+00:00
To repeat:

install MS Windows 11 Pro, all updates except preview.

install MS SQL Server 2022 (locally)

install MS SQL Server Management Studio 20

[can already create a successful ODBC 64 System DSN using the version 17 drivers, no errors]

additionally install latest ODBC version 18 drivers

try again to create a system DSN to same DB on same SQL Server instance: error 08001 error (which seems a certificate issue). SQL Server install a self-signed cert and in version 18 Encryption is ON by default, yet the cert hasn't been added to cert store upon install, I hazard to guess).

Programmatically this should be fixed by using:

"DRIVER={ODBC Driver 18 for SQL Server};SERVER=$url;DATABSE=$db;UID=$usr;TrustServerCertificate=yes;"

But you can't specify that when creating a DSN using ODBC Data Source Administrator (64 bit).

So far trying to resolve this by adding (to the account performing these actions) what seems to be the self signed certificate (MS_AgentSigningCertificate.cer in DATA) generated by SQL Server upon install doesn't seem to work. By the way that certificate expires in 2025, so hardly user friendly (e.g. NVDIA GameStream cert expires in 2044).

Only worked around that worked for me is using version 17.
Jaap Titulaer 0 Reputation points

2024-05-05T12:03:10.72+00:00

Update: you can't set the extra security settings on the next page (LOL), should have been directly after credentials page as it's now standard & mandatory. There you can click on Trusted Server Certificate.

So that is on the page AFTER where you can select the default DB, but that already requires all security setting including this cert business, which is on the next page (...).

Even when adding a cert as per https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16#sql-server-generated-self-signed-certificates

it still doesn't work without that, even though that cert is now in local computer, you still have to select the cert (named incl the computername as per above PS script) or simply click on Trusted Server Certificate as before.

Sequence of pages in ODBC DSN admin is naming, credentials, settings (including default DB to use), other settings including Connection Encryption (now essentially mandatory and part of security/credentials), which now should be moved to before the option to set the default DB (which is commonly used), not after as is now.

Answer 2

Angi Wang 0

In ODBC connection configuration, checked the "trust the server certificate" then pass the testing.
User's image

Share via

How to Fix:Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate].

2 answers

Your answer