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

Question

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

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"? — Gord Thompson, Dec 06 '22 at 19:26
Does this answer your question? [Linux python3 - Can't open lib 'SQL Server'](https://stackoverflow.com/questions/38534154/linux-python3-cant-open-lib-sql-server) — Zak, Dec 06 '22 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 .. — Zak, Dec 06 '22 at 19:42
@Zak 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˙˙ — Anel Hodžić, Dec 06 '22 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 — Anel Hodžić, Dec 06 '22 at 19:54
Pretty sure this was the same issue without answer yet https://stackoverflow.com/questions/73872213/08001microsoftodbc-driver-18-for-sql-servertcp-provider-error-code-0x2746 — Anel Hodžić, Dec 06 '22 at 19:58
Perhaps try connecting using [pymssql](https://pypi.org/project/pymssql/) and see if that works. — Gord Thompson, Dec 06 '22 at 20:49
@GordThompson will try this but take a look on first comment here. I'm pretty sure this was my issue also . https://stackoverflow.com/questions/73872213/08001microsoftodbc-driver-18-for-sql-servertcp-provider-error-code-0x2746 — Anel Hodžić, Dec 06 '22 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. — Gord Thompson, Dec 06 '22 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')` — Anel Hodžić, Dec 06 '22 at 21:17
@GordThompson What do you think can I try older version and which one ? — Anel Hodžić, Dec 06 '22 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](https://dbeaver.io/download/) 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*. — Gord Thompson, Dec 07 '22 at 00:31
Please [Edit](https://stackoverflow.com/posts/74708033/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. — AlwaysLearning, Dec 07 '22 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](https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe) and give them a hard time for failing to install even service pack 2 - which was available back in 2016. — AlwaysLearning, Dec 07 '22 at 12:29
@AlwaysLearning yes. You were right output includes `-DOPENSSL_TLS_SECURITY_LEVEL=2` — Anel Hodžić, Dec 07 '22 at 12:30
@AlwaysLearning `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.` — Anel Hodžić, Dec 07 '22 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. — Gord Thompson, Dec 09 '22 at 14:38

score 3 · Answer 1 · answered Jan 10 '23 at 12:41

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

score 2 · Answer 2 · answered Mar 22 '23 at 18:32

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)

score 1 · Answer 3 · answered Feb 26 '23 at 20:56

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

Gani · Answer 4 · 2023-04-11T13:45:03.783

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)

score 1 · Answer 5 · answered Jun 22 '23 at 01:01

1

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

answered Jun 22 '23 at 01:01

Zac

598
6
11

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

5 Answers5

Linked