Question 38448 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Error code 0x2746 (10054) when trying to connect to SQL Server 2014 via ODBC from Linux – Ubuntu

Ubuntu 22.04.1 LTS

pyodbc 4.0.35

OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)

Followed steps on Install the Microsoft ODBC driver for SQL Server (Linux)

Installation successful. When I run this snippet

def select_driver():
    """Find least version of: ODBC Driver for SQL Server."""
    drv = sorted([drv for drv in pyodbc.drivers() if "ODBC Driver " in drv and " for SQL Server" in drv])
    if len(drv) == 0:
        raise Exception("No 'ODBC Driver XX for SQL Server' found.")
    return drv[-1]

print(select_driver())

Output is : ODBC Driver 18 for SQL Server

My connection string .

cnxn_str = ("Driver={SQL Server Native Client 18.0};"
            "Server=xx;"
            "Database=xx;"
            "UID=xx;"
            "PWD=xx")
myCon = pyodbc.connect(cnxn_str)

Edit: With new connection "Driver={ODBC Driver 18 for SQL Server};"

[Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

EDIT: root@vps:~# openssl version -a

OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022) built
on: Thu Oct 27 17:06:56 2022 UTC platform: debian-amd64 options:
bn(64,64) compiler: gcc -fPIC -pthread -m64 -Wa,–noexecstack -Wall
-Wa,–noexecstack -g -O2 -ffile-prefix-map=/build/openssl-WsPfAX/openssl-3.0.2=. -flto=auto -ffat-lto-object s -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat
-Werror=format-sec urity -DOPENSSL_TLS_SECURITY_LEVEL=2 -DOPENSSL_USE_NODELETE -DL_ENDIAN
-DOPENSSL_PI C -DOPENSSL_BUILDING_OPENSSL -DNDEBUG -Wdate-time -D_FORTIFY_SOURCE=2
OPENSSLDIR: "/usr/lib/ssl" ENGINESDIR:
"/usr/lib/x86_64-linux-gnu/engines-3" MODULESDIR:
"/usr/lib/x86_64-linux-gnu/ossl-modules" Seeding source: os-specific
CPUINFO: OPENSSL_ia32cap=0xffbaa2234f8bffff:0x400000283

Tags:

4

Answers


  1. 0

    I was having the same problem trying to connect from PHP 8.1 in a Ubuntu 22.04 server to a Microsoft SQL Server 2014.

    OpenSSL 3.0 (which comes with Ubuntu 22.04) changed the default behaviour of SECLEVEL. Now you need to specify 0 instead of 1. More info here: https://github.com/openssl/openssl/issues/17476

    Two different things fixed the problem for me:

    As explained above, in /etc/ssl/openssl.cnf

    Change:

    [system_default_sect]
    CipherString = DEFAULT:@SECLEVEL=2

    To:

    [system_default_sect]
    CipherString = DEFAULT:@SECLEVEL=0

    Also, when establishing the connection in PHP (and I imagine the same would work in Python too) to the SQL server, add the following options:

    "Encrypt"=>true, "TrustServerCertificate"=>true

    Login or Signup to reply.
  2. 0

    Ubuntu 20.04 works for me, somehow after upgrading to 22.04, everything breaks. Need to downgrade SSL to 1.1

    wget    https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.6.1.1-1_amd64.deb
wget    https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/mssql-tools/mssql-tools_17.6.1.1-1_amd64.deb

sudo dpkg -i msodbcsql17_17.6.1.1-1_amd64.deb
sudo dpkg -i mssql-tools_17.6.1.1-1_amd64.deb

wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.1f-1ubuntu2_amd64.deb
wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/openssl_1.1.1f-1ubuntu2_amd64.deb
sudo dpkg -i libssl1.1_1.1.1f-1ubuntu2_amd64.deb
sudo dpkg -i openssl_1.1.1f-1ubuntu2_amd64.deb
    Login or Signup to reply.
  3. 0

    March 2023

    I encountered the same issue after building a docker image based on ubuntu:20.04 for AWS Sagemaker processing jobs. I resolved the error by installing a specific version of openssl, namely, 1.1.1p.

    This was the command I added to my docker file:

    RUN wget https://www.openssl.org/source/openssl-1.1.1p.tar.gz -O openssl-1.1.1p.tar.gz && 
tar -zxvf openssl-1.1.1p.tar.gz && 
cd openssl-1.1.1p && 
./config && 
make && 
make install && 
ldconfig

    This line should be added before the command that installs the appropriate Microsoft SQL Server ODBC drivers (which was msodbcsql17 in my case)

    Login or Signup to reply.
  4. 0

    Connect to MSSQL from ubuntu (22.04)

    1. At first, install pyodbc using pip (preferred)

    2. Install Microsoft ODBC Drivers, can be found here

    3. open /opt/ssl/openssl.conf and update
      [system_default_sect] section with the following

      CipherString = DEFAULT:@SECLEVEL=0

    4. Restart the system

    5. Connection test in python:

    import pyodbc
import pandas as pd
cnxn_str = ("Driver={ODBC Driver 18 for SQL Server};"
            "Server=ServerName,Port;"
            "Database=DBName;"
            "UID=xx;"
            "PWD=xx;"
            "TrustServerCertificate=yes;")

# initialise connection
cnxn = pyodbc.connect(cnxn_str)  
# build up our query string
query = ("SELECT TOP(10) * FROM DB")
# execute the query and read to a dataframe in Python
data = pd.read_sql(query, cnxn)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search