pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

[sponaugle]

Environment Hi, I am running the following --

• Python: 3.6 (64 bit)
• pyodbc: 4.0.27
• OS: Linux (Redhat 7.6)
• driver: ODBC Driver 17 for SQL Server

and am trying to access a SQL SERVER (version 10.0.1600.22) hosted on a Windows NT 5.2 (XP/Server 2003) machine.

Code

`def connectToSQLServer(server_name, username, password, db_name):

conn = pyodbc.connect(
                      'Driver={ODBC Driver 17 for SQL Server};'
                      f'Server={server_name};'
                      f'Database={db_name};'
                      f'UID={username};'
                      `f'PWD={password};'`
                      'Mars_Connection=Yes;'
                     )
return conn

conn = connectToSQLServer(server_name='<server_name>', username='',password=,db_name='<db_name>')`

Issue

Issue I am receiving the following error --

'Driver={ODBC Driver 17 for SQL Server};' pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

I am able to access several other SQL Server machines using the same environment and code without issue, although they are using more recent Operating Systems (Windows NT 6.0+) and SQL Server versions (10.5+).

I understand that Windows NT 5.2 and SQL Server (10.0.1600.22) are very old and no longer supported. I read the following ticket (https://github.com/Microsoft/msphpsql/issues/252) and some people suggested the Operating System needs to be upgraded. So I'm wondering if that may be the case here.

However, I am a little skeptical because I am able to establish connection when I run the same code but from my local Windows 10 laptop, which uses the Windows equivalent of the same ODBC driver. My local Windows 10 machine also uses the same Python and pyodbc versions. So my real question is, how come I can establish a connection from my Windows 10 machine but not from the Linux machine? Could there be something else at play here?

Thanks a lot for your help.

[v-makouz]

Just to rule out pyodbc itself, can you try to connect directly using the ODBC driver? To do so run isql -v -k "<Your connection string>", and see if it gives the same error.

Also it may be useful to look at ODBC traces from the successful Windows connection and the failed connection. (ODBC tracing Linux: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/data-access-tracing-with-the-odbc-driver-on-linux?view=sql-server-2017, Windows: https://docs.oracle.com/cd/E17952_01/connector-odbc-en/connector-odbc-configuration-trace-windows.html)

[sponaugle]

Hi v-makouz,

The isql command returns the same error --

[08001][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 [08001][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection [ISQL]ERROR: Could not SQLDriverConnect

I also did the tracing for both the successful windows 10 connection and the failed linux one. I attached those logs to this message (and removed the private connection information). I looked them over but didn't see anything stick out but it's my first time ever reading those type of logs.

Since we've ruled out that it is not pyodbc related I understand if you have to close the ticket. But if you or anyone else can point me in the right direction I would greatly appreciate it.

Thank you, Zack

database_connection_trace_linux.txt database_connection_trace_windows_10.LOG

[gordthompson]

Does it make any difference if you explicitly specify Encrypt=no in your connection string?

[v-chojas]

What version of OpenSSL are you using on the RedHat machine? You can run openssl version to check.

Could you do a packet capture with Wireshark in both the working and non-working cases? The difference could be due to TLS configuration.

[sponaugle]

Adding "Encrypt=no" to the connection string still returns the same error.

OpenSSL version is OpenSSL 1.0.2k-fips on the Red Hat machine.

I will work on setting up wireshark to analyze both cases.

Thank you both.

[adamtabrams]

@sponaugle Did you ever find a solution? I'm having the same issue.

[v-chojas]

@adamtabrams could you provide your environment details, and Wireshark if possible?

[adamtabrams]

Environment

• Python: 3.7
• pyodbc: 4.0.27
• unixodbc-dev 2.3.6
• msodbcsql17 17.4.1.1-1
• OS: Linux (Debian Buster-Slim 10.1)
• Running as a Docker container
• driver: ODBC Driver 17 for SQL Server

Issue With this environment, I was getting the same error as sponaugle when I was trying to connect to the database. I was actually able to fix the problem for my use case by switching to Debian Stretch-Slim 9.11. One slight difference that I noticed is that Stretch has unixodbc-dev 2.3.7 available, but it seems that only 2.3.6 is available for Buster (even though it's newer). Now that I have it working, I'm done tinkering with it, but I hope that's enough info to point everyone in the right direction. Thanks!

[v-chojas]

You may be seeing the effect of the OpenSSL changes in Debian: https://wiki.debian.org/ContinuousIntegration/TriagingTips/openssl-1.1.1

Also note that you can get the unixODBC 2.3.7 packages here too: https://packages.microsoft.com/debian/10/prod/pool/main/u/unixodbc/

[roirodriguez]

Hi, I was experiencing the same problem, and fixed thanks to @v-chojas comment above. Just edited /etc/ssl/openssl.cnf and changed:

MinProtocol = TLSv1.0
CipherString = DEFAULT@SECLEVEL=1

[aap82]

Confirmed this worked for me:

https://stackoverflow.com/questions/57265913/error-tcp-provider-error-code-0x2746-during-the-sql-setup-in-linux-through-te/57453901#57453901

System Info Python: 2.7 pyodbc: 4.0.26 OS: Ubuntu 18.04 driver: ODBC Driver 17 for SQL Server

[sponaugle]

My openssl.cnf file in Red Hat 7 does not have any existing MinProtocol or CipherString options. Also its path is at /etc/pki/tls. Not sure if that matters.

[sonnehansen]

@sponaugle I can confirm that @roirodriguez 's solution works. It seems that there are different ways to input default values. Deleting them (or not having them) it one of them. At least on Ubuntu 18.04 it works with no entries and openssl v1.1.1g

A third option is mentioned in the Debian release issues, which is just a different take on the info already provided by @v-chojas.

Thanks a million to all of you for pointing me in the right direction :)

For future reference for conda users. My problem arose when upgrading miniconda bases image from 4.5.12 (Debian 9) to 4.7.12 (Debian 10), which was required due to some package resolving issues in existing environments.

[atroiano]

I can also confirm @roirodriguez worked in Debian 10

[jfmontanaro]

Just wanted to chime in here and mention that at least in my case, the issue wasn't due to SQL Server lacking support for TLSv1.2 but rather to the server's certificate having too weak a key.

The ideal solution in this case is obviously to update the server certificate, but failing that it also works to set CipherString = DEFAULT@SECLEVEL=1 as mentioned by @roirodriguez and leave MinProtocol alone.

[sondrelg]

In case anyone (like me) stumbles onto this thread without knowing even the basics of linux; this is how I ended up implementing @roirodriguez's fix in my Dockerfile:

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

sed -i replaces text in a file in-place, while the chmod-command was necessary to give the current user access rights to the file.

[trs80]

Even with 'encrypt=no' I see a TDS7 pre-login message - TLS exchange sent by the client, followed by a FIN, ACK from the server. Setting CipherString = DEFAULT@SECLEVEL=1 in openssl.cnf allows the connection to proceed, there's further TDS7 pre-login messages showing the default self-signed certificate and a TLS exchange which appears to contain the login.

So a) the default certificate is too weak and b) something's attempting TLS even with encrypt=no.

[v-chojas]

See the TDS spec Encryption section: https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/60f56408-0188-4cd5-8b90-25c6f2423868

The default cert (and the TLS handshake) is present to provide some protection from passive attackers, if you need something stronger you can set up your own certificate: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine

[HARIKABANDARU]

The same error occurred when trying to connect from python using SQLAlchemy on windows.

[v-chojas]

This is a TLS configuration issue, and is unrelated to pyODBC.

[trs80]

Well can you document it somewhere then? Because clearly lots of people hit the problem. Ideally with the error messages so it's good Google bait. Also that encrypt=no isn't respected.

[v-chojas]

The behaviour of TDS Encryption is documented in the link I posted above.

[trs80]

Clearly nobody knows how to find those documents or that they're the root cause of their connection problems since people keep filing bugs. Perhaps on https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux or https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15 or https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/known-issues-in-this-version-of-the-driver?view=sql-server-ver15 ?

[shushko1]

after adding to /etc/ssl/openssl.cnf the lines MinProtocol = TLSv1.0 and CipherString = DEFAULT@SECLEVEL=1 what do we need to restart/refresh? I still get "TCP Provider: Error code 0x2746" and "Client unable to establish connection" after executing sqlcmd.

[v-chojas]

You will need to provide a lot more information about your environment for anyone else to be able to give you more assistance.

[shushko1]

my environment is Ubuntu 20.04 LTS, running nginx web service and PHP 7.4. from there I try to connect to M$ SQL Server 2014 database on a Windows 2012 R2 server. on the Ubuntu-box I have unixodbc-dev 2.3.7, msodbcsql17 17.5.2.1-1, openssl 1.1.1f. as far as I understood, this SQL connection is using TLS v1.2 by default, which is causing problems, and we need to force the connection to use TLS v1.0 - and that is achieved by modifying /etc/ssl/openssl.cnf ?

[v-chojas]

Everything in that list supports TLS 1.2. Make sure your server has this update: https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

[samueldominguez]

Still running into this issue even after changing TLS configuration on Debian 10. My connection string is the following: DRIVER={ODBC Driver 17 for SQL Server};SERVER=sqlserver,1936;UID=user;PWD=password where the port is specified as per the library sqlserverport when getting the instance name via sqlserverport.lookup(server_name, instance_name).

If I change the connection string to DRIVER={ODBC Driver 17 for SQL Server};SERVER=sqlserver;PORT=1936;UID=user;PWD=password I get the following error instead: pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]ODBC Driver 17 for SQL Server does not support connections to SQL Server 2000 or earlier versions. (22) (SQLDriverConnect)') even though I am using MS SQL 2008 R2.

[v-chojas]

"port" is not a valid connection string keyword.

If you could post a packet trace, that can help determine if it is due to TLS configuration or something else.

[ghost]

If someone is using CENTOS 8 or similar, the command below and a reboot solved my issue (as a workaround):

update-crypto-policies --set LEGACY

It makes openssl run in compatibility policy with legacy protocols.