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

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

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.

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

@shushko1 Try this for Ubuntu 20.04

You need to add this to the beginning of your config file at /etc/ssl/openssl.cnf

openssl_conf = default_conf

And then this to the end:

[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = system_default_sect

[system_default_sect] MinProtocol = TLSv1.2 CipherString = DEFAULT:@SECLEVEL=1

Refer https://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-level The safe way to do it is by following @v-chojas comments above. Lower the security level only if you do not have access to the server and you need to handle it from client side.

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

I have recently updated to Ubuntu 22.04 which now has openssl 3.0 installed by default The solution above editing the /etc/ssl/openssl.cnf works for 20.04 and openssl v1.1.x but not for openssl v3.0!!!

I discovered you have lower the security level again to 0 for this to work in Ubuntu 22.04 so change : CipherString = DEFAULT:@SECLEVEL=1

to

CipherString = DEFAULT:@SECLEVEL=0

in /etc/ssl/openssl.cnf and it should work

this is using ODBC Driver 17 for SQL Server

@shushko1 Try this for Ubuntu 20.04

You need to add this to the beginning of your config file at /etc/ssl/openssl.cnf

openssl_conf = default_conf

And then this to the end:

[ default_conf ] ssl_conf = ssl_sect [ssl_sect] system_default = system_default_sect [system_default_sect] MinProtocol = TLSv1.2 CipherString = DEFAULT:@SECLEVEL=1

Refer https://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-level The safe way to do it is by following @v-chojas comments above. Lower the security level only if you do not have access to the server and you need to handle it from client side.

Thanks! This worked fine for me.

Thanks to all who left comments above, they helped me resolve my issue.

I had a similar issue after installing gcloud on MacOS Big Sur.

The openssl version returned me LibreSSL 2.8.3. The which and where commands gave me nothing while they’re only showing the binary location.

The only thing that helped me to find a way to fix it was ls -la /usr/local/opt | grep openssl, which showed me that I have two OpenSSL versions installed on my computer.

lrwxr-xr-x   1 user  admin    30 Dec  2 09:30 openssl@1.1 -> ../Cellar/openssl@1.1/1.1.1l_1
lrwxr-xr-x   1 user  admin    27 Oct 31 21:07 openssl@3 -> ../Cellar/openssl@3/3.0.0_1

Then I just updated the symlink with the following command ln -s /usr/local/Cellar/openssl@1.1/1.1.1l_1 /usr/local/opt/openssl and voila - everything is working as before.

@shushko1 Try this for Ubuntu 20.04

You need to add this to the beginning of your config file at /etc/ssl/openssl.cnf

openssl_conf = default_conf

And then this to the end:

[ default_conf ] ssl_conf = ssl_sect [ssl_sect] system_default = system_default_sect [system_default_sect] MinProtocol = TLSv1.2 CipherString = DEFAULT:@SECLEVEL=1

Refer https://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-level The safe way to do it is by following @v-chojas comments above. Lower the security level only if you do not have access to the server and you need to handle it from client side.

Brow, this works for me in ubuntu 20.04. Thanks a lot!

adding these 2 lines to the end of /etc/ssl/openssl.cnf on MacOS Catalina works for me

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

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.

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 @eljirg I am using amazon Linux for deploying Python with elastic beanstalk.

I am facing the same problem.

In Red Hat (in my case amazon Linux), you must configure /etc/pki/tls/openssl.cnf.

First, add or change,

openssl_conf = default_conf

and add to last line

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1.2
CipherString = DEFAULT@SECLEVEL=1

I write a bash code doing this job.

       # Define the directory and file
        DIR="/etc/pki/tls/"
        FILE="openssl.cnf"
        BACKUP_FILE="openssl.cnf.bak"

        # Navigate to the directory
        cd $DIR

        # Backup the original file
        cp $FILE $BACKUP_FILE

        # Check if openssl_conf already exists and modify/add as necessary
        grep -q "openssl_conf" $FILE
        if [ $? -eq 0 ]; then
            # Line exists, modify it
            sed -i 's/^openssl_conf =.*/openssl_conf = default_conf/' $FILE
        else
            # Line doesn't exist, add it
            echo "openssl_conf = default_conf" >> $FILE
        fi

        # Check if [default_conf] already exists, if not add the additional configuration
        if ! grep -q "\[default_conf\]" $FILE; then
            cat <<EOL >>$FILE

        [default_conf]
        ssl_conf = ssl_sect

        [ssl_sect]
        system_default = system_default_sect

        [system_default_sect]
        MinProtocol = TLSv1.2
        CipherString = DEFAULT@SECLEVEL=1
        EOL
        fi

        echo "File updated successfully!"

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.

Hi @sponaugle

I am encountering same issue on RHEL 9 and OpenSSL 3.0.5 5 Jul 2022

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

Have tried adding these two lines below in /etc/ssl/openssl.cnf and /etc/pki/tls/openssl.cnf (these two are connected)

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

However it’s still now working. Changing the SECLEVEL to 0 also not working CipherString = DEFAULT@SECLEVEL=0

Tried also the Encrypt = False but also didn’t work.

Have you find working solution for Red Hat?

It was a huge pain getting this working on macOS Catalina with Python 3.9.7, but I eventually figured it out.

The first issue was my mac was running LibreSSL 2.8.3, while the ODBC Drive wants OpenSSL 1.0 or 1.1. I confirmed with openssl version. I installed OpenSSL 1.1.1 using Homebrew (I might have had to uninstall a newer version that was installed prior to that, I can’t recall). Instructions here: https://github.com/microsoft/homebrew-mssql-release/issues/59#issuecomment-928957200

If openssl version is still showing the wrong version, you can prepend the correct OpenSSL directory to your PATH as described at https://stackoverflow.com/a/62595252.

At that point I was able to test my ODBC connector and confirm it was working. isql -v -k "My Connection String"

Changing the SSL version broke Pyhon3 at some point, so I reinstalled. brew reinstall python

I also edited openssl.cnf per the instructions in this thread. Finally, pyodbc worked.

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 ?

@sponaugle @eljirg I am using amazon Linux for deploying Python with elastic beanstalk.

I am facing the same problem.

In Red Hat (in my case amazon Linux), you must configure /etc/pki/tls/openssl.cnf.

First, add or change,

openssl_conf = default_conf …

I can confirm that this works on Amazon Linux 2023, thanks @triplehoon.

@mybugubird yesterday I solved it. For my case the issue was that my flask application also was using mysql-connector-python. Its seems that when the mysql library is imported some dependencies are in conflict. Also would be a good idea check the working directory, It dependes from where you execute the app.

I had the same issue:

pyodbc.OperationalError: (‘08001’, ‘[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (-2146893019)’)

And it was solved adding Encrypt=no

cnxn = pyodbc.connect(r'DRIVER={ODBC Driver 18 for SQL Server};SERVER='+x+';DATABASE='+database+';UID='+username+';PWD='+password+';Encrypt=no')

How to fix this issue on Heroku?

2022-08-04T14:41:04.577720+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

Thanks a lot for your help.

I tried some solutions here and no one solved my problem. But, I found a solution here, upgrading OpenSSL to newer version 1.1.1. I was using 1.1.1f and upgraded to 1.1.1m. And now it works fine.

Environment:

• OS: Ubuntu 20.04 (under WSL)
• DB: SQL Server 2012
• Driver: ODBC Driver 18 for SQL Server

It worked for me with “Encrypt=no;”

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.

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