CODEWITHSUNDEEP
pythonsql-serverubuntuodbcpyodbc
Anel Hodžić
Anel Hodžić

Reputation: 313

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

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

Upvotes: 6

Views: 15479

Answers (7)

BennyFriedman
BennyFriedman

Reputation: 31

Overcame this error by following the suggestions in here. For those who want a dockerfile example see my step below:

# Modify the OpenSSL configuration file
RUN sed -i 's/openssl_conf = openssl_init/openssl_conf = default_conf/' /etc/ssl/openssl.cnf && \
    echo "\n[ default_conf ]\nssl_conf = ssl_sect\n\n[ssl_sect]\nsystem_default = system_default_sect\n\n[system_default_sect]\nMinProtocol = TLSv1.2\nCipherString = DEFAULT@SECLEVEL=0" >> /etc/ssl/openssl.cnf

Upvotes: 3

Jairo Martínez
Jairo Martínez

Reputation: 525

In my case using MacOS Monterey, after many attempts, I followed the solution here:

Known issues for the ODBC driver on Linux and macOS

rm -rf $(brew --prefix)/opt/openssl
version=$(ls $(brew --prefix)/Cellar/[email protected] | grep "1.1")
ln -s $(brew --prefix)/Cellar/[email protected]/$version $(brew --prefix)/opt/openssl

That fix addressed only part of the problem; afterward, I had to incorporate @Edgar Pisani's suggestion to trust in Server Certificates:

Python code:

connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD};TrustServerCertificate=yes;Encrypt=yes;'

Upvotes: 1

Zac
Zac

Reputation: 720

I got the same error and added the following command in Dockerfile to resolve it.

RUN chmod +rwx /etc/ssl/openssl.cnf
RUN sed -i 's/TLSv1.2/TLSv1/g' /etc/ssl/openssl.cnf
RUN sed -i 's/SECLEVEL=2/SECLEVEL=1/g' /etc/ssl/openssl.cnf

Upvotes: 1

Gani
Gani

Reputation: 45

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)

Upvotes: 2

Chirag Daryani
Chirag Daryani

Reputation: 71

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)

Upvotes: 2

Hairy Ass
Hairy Ass

Reputation: 338

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

Upvotes: 1

Edgar Pisani
Edgar Pisani

Reputation: 81

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

Upvotes: 8

Related Questions