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

Asked 6/12, 2022 at 19:23 Answered 1/8 at 19:40

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

Plasterwork answered 6/12, 2022 at 19:23 Comment(18)

Why are you trying to use "SQL Server Native Client 18.0" when you know that the driver is named "ODBC Driver 18 for SQL Server"? – Fischer 6/12, 2022 at 19:26

Does this answer your question? Linux python3 - Can't open lib 'SQL Server' – Buncombe 6/12, 2022 at 19:26

View the question I posted above .. Especially the answer: "replace DRIVER={SQL Server} with DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1}" Of course you'll replace that path/file with the one from your current setup .. – Buncombe 6/12, 2022 at 19:42

Try adding Encrypt=no to your connection string. – Fischer 6/12, 2022 at 19:52

@Buncombe I'm still getting the same issue here [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)') I didn't mentioned that I'm trying to connect to Microsoft SQL Server 2014˙˙ – Mauceri 6/12, 2022 at 19:53

@GordThompson That also didn't helped. I suppose it has some issues with TLS settings because I'm connecting to SQL 2014 – Mauceri 6/12, 2022 at 19:54

Pretty sure this was the same issue without answer yet #73872713 – Mauceri 6/12, 2022 at 19:58

Perhaps try connecting using pymssql and see if that works. – Fischer 6/12, 2022 at 20:49

@GordThompson will try this but take a look on first comment here. I'm pretty sure this was my issue also . #73872713 – Mauceri 6/12, 2022 at 20:55

Yeah, I looked at that. If the SQL Server instance to which you are trying to connect is too old and unpatched for TLS 1.2 then the newer ODBC drivers may simply not work. – Fischer 6/12, 2022 at 20:57

@GordThompson Checking that now

pymssql._pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (x1434)\n')

– Mauceri 6/12, 2022 at 21:17

@GordThompson What do you think can I try older version and which one ? – Mauceri 6/12, 2022 at 21:17

I doubt that you can get an older version of Microsoft's ODBC driver (msodbcsql) on Ubuntu 22.04. I'm surprised that pymssql doesn't work, because on my Ubuntu 20.04 machine I cannot connect to SQL Server 2012 using msodbcsql17 but I can connect with pymssql. The only other thing I can think of would be to install DBeaver and try connecting with that, since it uses JDBC instead of ODBC. That would at least try to verify whether you can connect to the SQL Server instance at all. – Fischer 7/12, 2022 at 0:31

Please Edit your question to include the output from openssl version -a on your Ubuntu 22.04 machine. If the output includes -DOPENSSL_TLS_SECURITY_LEVEL=2 then it means that the library has not been built with TLS 1.0 or TLS 1.1 support, so no amount of tweaking the openssl.cnf file will allow you to connect to older servers without TLS 1.2 support. That fact that you're on SQL Server 2014 and still having this problem suggests that the server owner is bad at installing service packs and updates - it really should have TLS 1.2 support. – Gilmagilman 7/12, 2022 at 12:25

For TLS 1.2 support on SQL Server 2014 point your DBAs at KB3135244 - TLS 1.2 support for Microsoft SQL Server and give them a hard time for failing to install even service pack 2 - which was available back in 2016. – Gilmagilman 7/12, 2022 at 12:29

@Gilmagilman yes. You were right output includes -DOPENSSL_TLS_SECURITY_LEVEL=2 – Mauceri 7/12, 2022 at 12:30

@Gilmagilman

The version of SQL Server instance NAME does not match the version expected by the SQL Server update. The installed SQL Server product version is 12.0.2000.8, and the expected SQL Server version is 12.3.6024.0.

– Mauceri 7/12, 2022 at 12:41

version 12.0.2000.8 indicates that the instance has never been patched. It sounds like you'll need to apply a Service Pack first. – Fischer 9/12, 2022 at 14:38

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

Sponge answered 10/1, 2023 at 12:41 Comment(1)

didn't work for me, but with slight edits as per github.com/microsoft/msphpsql/issues/… .. it works ``` In /etc/ssl/openssl.cnf file : 1/ change openssl_conf = openssl_init to openssl_conf = default_conf 2/ add those lines at the end of file [ default_conf ] ssl_conf = ssl_sect [ssl_sect] system_default = system_default_sect [system_default_sect] MinProtocol = TLSv1.2 CipherString = DEFAULT@SECLEVEL=0 Then test it with isql -v -k "Driver={ODBC Driver 18 for SQL Server};Server=xx;Database=xx;UID=xx;PWD=xx;Encrypt=no" ``` – Acquaint 29/4 at 18:49

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)

Organza answered 22/3, 2023 at 18:32 Comment(0)

Connect to MSSQL from ubuntu (22.04)

At first, install pyodbc using pip (preferred)
Install Microsoft ODBC Drivers, can be found here
open /opt/ssl/openssl.conf and update [system_default_sect] section with the following

CipherString = DEFAULT:@SECLEVEL=0
Restart the system
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)

Counterproof answered 11/4, 2023 at 13:42 Comment(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

Voyage answered 26/2, 2023 at 20:56 Comment(0)

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

Leanoraleant answered 22/6, 2023 at 1:1 Comment(0)

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;'

Beardless answered 8/4 at 18:7 Comment(0)

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

Speculator answered 1/8 at 19:40 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags