4

I wanted to remotely connect to a sql-server database on my network in python. So I the installed pyodbc following the steps given on this page. It instructs to first "Install the Microsoft ODBC Drivers for SQL-Server" by following these instructions.

Having completed these steps (and only these steps) I identified the driver path and tried running this code in python.

import pyodbc as podbc
conn = podbc.connect("Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1;"
                 "Server=192.XXX.XXX.XXX;"
                 "Database=db_name;"
                 "uid=xxxx;"
                 "pwd=xxxx;")

On execution I am getting the error:

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

It would be helpful if you could tell me why am I getting this error and if I'm missing any steps in the installation process. Also, I am able to access the sql-server remotely through a windows machine, but whenever I'm trying to do it via an ubuntu machine I'm getting this error.

Siddhant Bane
  • 93
  • 3
  • 8
  • 1
    That doesn't look right. The driver you want to define is most likely `{ODBC Driver 17 for SQL Server}`. Have you installed the [Microsoft ODBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver15)? – Thom A Dec 24 '21 at 11:37
  • 1
    If you were following the instructions from your first link you should have noticed [Step 3: Proof of concept connecting to SQL using pyodbc](https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15) alongside it. As Larnu mentioned you should be using `DRIVER={ODBC Driver 17 for SQL Server};` and you should also be prefixing your server name/IP address with `tcp:` to force TCP/IP connections, i.e.: `Server=tcp:192.XXX.XXX.XXX;` – AlwaysLearning Dec 24 '21 at 13:18
  • @AlwaysLearning I am experiencing the same error on a similar linux (mint) setup and have used the driver name and forcing tcp as you've suggested. Something changed recently in the Linux (ubuntu) setup that caused my install to stop working with this error being the only hint as to what is going on... – DPSSpatial Feb 09 '22 at 22:03
  • @DPSSpatial If it has been working until recently and only just broke I'd suggest try adding `;Encrypt=no` to your connection string to see if that makes it work. If that fixes things then likely you're connecting to an (very) old/unpatched SQL Server instance that doesn't have TLS 1.2 support and you've had an update to the OpenSSL library installed that has disabled TLS 1.0 and TLS 1.1 protocols on the Linux client side. – AlwaysLearning Feb 10 '22 at 02:18
  • @AlwaysLearning thanks for the tip, unfortunately still stuck. I can't rule out an install error on this Mint box, while our CentOS server is running this fine, though the ODBC drivers were installed several years ago... I'll keep digging!! Thanks again! – DPSSpatial Feb 10 '22 at 21:18

1 Answers1

0

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)

Chirag Daryani
  • 61
  • 2