Question 8176 in PHP Versions Question posted in
You can find a list of all versions here.

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

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.

Tags:

4

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 …

    Login or Signup to reply.
  3. 0

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

    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://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver16#available-options

    Login or Signup to reply.
  4. 0

    for mssql-django database backend , use the config below:

    DATABASES['source'] = {
    'ENGINE': 'mssql',
    'NAME': env('SOURCE_DB_NAME'),
    'USER': env('SOURCE_DB_USER'),
    'PASSWORD': env('SOURCE_DB_PASS'),
    'HOST': env('SOURCE_DB_HOST'),       
    'OPTIONS': {
        'driver': 'ODBC Driver 18 for SQL Server',
        'extra_params': "Encrypt=no" #TrustServerCertificate=no
    },
}

    ref

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search