Question

ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086]

I have the following error while trying to connect SQL Server:

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

I'm on Ubuntu 20, PHP 7.4 FPM, nginx.

Server  Microsoft SQL Server [11.00.3128
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
    Dec 28 2012 20:23:12 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
]

When I:

openssl s_client -connect myserverip:1433 -tls1

I get:

CONNECTED(00000003)

My openssl library version is:

OpenSSL 1.1.1f 31 Mar 2020 (Library: OpenSSL 1.1.1k 25 Mar 2021)

I have tried many things, like forcing system to use TLS1 in /etc/ssl/openssl.cnf or nginx configuration to downgrade to tls1.

 48  96100  48
1 Jan 1970

Solution

 74

As @AlwaysLearning said in the comments, you need to add TrustServerCertificate=yes; to your connection string.

So something along the lines of

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

should work.

2022-05-23

Solution

 74

As @AlwaysLearning said in the comments, you need to add TrustServerCertificate=yes; to your connection string.

So something along the lines of

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

should work.

2022-05-23

Solution

 37

If you are not using SQL Server command-line tools (sqlcmd or terminal) then make sure to install it from here: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15

Then add the -C option to trust the server certificate.

sqlcmd -S <server> -U <username> -P <password> -C

Ex:

sqlcmd -S localhost -U sa -P 'YourPassword' -C

Or as mentioned in Microsoft Docs:

Try using the IP address 127.0.0.1 instead. It's possible that localhost isn't properly mapped to this address

Ex:

sqlcmd -S 127.0.0.1 -U sa -P 'YourPassword' -C

List of the available options: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver16#available-options

2022-08-22

Solution

 37

If you are not using SQL Server command-line tools (sqlcmd or terminal) then make sure to install it from here: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15

Then add the -C option to trust the server certificate.

sqlcmd -S <server> -U <username> -P <password> -C

Ex:

sqlcmd -S localhost -U sa -P 'YourPassword' -C

Or as mentioned in Microsoft Docs:

Try using the IP address 127.0.0.1 instead. It's possible that localhost isn't properly mapped to this address

Ex:

sqlcmd -S 127.0.0.1 -U sa -P 'YourPassword' -C

List of the available options: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver16#available-options

2022-08-22