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

Plasterwork

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

Does this answer your question? Linux python3 - Can't open lib 'SQL Server' – Buncombe

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

Try adding Encrypt=no to your connection string. – Fischer

@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

@GordThompson That also didn't helped. I suppose it has some issues with TLS settings because I'm connecting to SQL 2014 – Mauceri

Pretty sure this was the same issue without answer yet #73872713 – Mauceri

Perhaps try connecting using pymssql and see if that works. – Fischer

@GordThompson will try this but take a look on first comment here. I'm pretty sure this was my issue also . #73872713 – Mauceri

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

@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

@GordThompson What do you think can I try older version and which one ? – Mauceri

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

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

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

@Gilmagilman yes. You were right output includes -DOPENSSL_TLS_SECURITY_LEVEL=2 – Mauceri

@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

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

Answer 1

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

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

Answer 2

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

Answer 3

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)

Counterproof

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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