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

user_16523529 0
Viewed 12779

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 (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.

openssl ubuntu sql-server php
3 Answers

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.

user_111482960

I was having this same error while using ODBC Driver 18 with sqlalchemy + python 3.9. My solution is the same as @Olsgaard - set the TrustServerCertificate parameter to yes. With sqlalchemy, you can set it at the connect_args key in create_engine:

conn_string = ...
engine = sqlalchemy.create_engine(
    conn_string,
    connect_args = {
        "TrustServerCertificate": "yes"
    }, echo=False)

I figured my answer is relevant since the link to this question pops up first when you google this exact error after encountering it with sqlalchemy ...

user_120938850

If you are using the terminal (sqlcmd) then add -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://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver16#available-options

user_9708300
Related