Pyodbc: Login Timeout Error

Question

I am trying to connect to MS SQL Server using pyodbc from a remote machine running Ubuntu 16.04.

import pyodbc 

conn = pyodbc.connect(r'DRIVER=ODBC Driver 17 for SQL Server; SERVER=xxxTest-SRV; PORT=51333; DATABASE=TestDB; UID=xxxx; PWD=xxxx;')

I'm getting following error:

pyodbc.OperationalError: ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

I tried using the server IP in the connection string but still no luck.

However, I am able to connect to using sqlcmd from the terminal
Following works:

sqlcmd -S xxxTest-SRV, 51333 -d TestDB -U xxxx -P xxxx

I didn't find any issue that gave an answer to my problem.

odbcinst.ini

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.1.so.1.1
UsageCount=1

There's always seems to be an issue connecting to MS SQL Server using pyodbc from a linux machine. Is there a way to connect to SQL Server from Python. I'll appreciate your help in solving this error. Thank you.

[UPDATE]

As per the below answer, I updated the connection string. But, now I get following error:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1' : file not found (0) (SQLDriverConnect)")

My odbcinst.ini file driver definition:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1
UsageCount=1

It has always been a nightmare to connect to MS SQL Server from a Linux machine. Can you please tell which pyodbc, unixODBC and Driver version is the most stable?

I have installed the driver following this Microsoft instructions. My pyodbc version is 4.0.23

Answer 1

Microsoft's ODBC drivers for SQL Server do not use a PORT= parameter. The port number, if any, is appended to the server name/IP with a comma, e.g.,

SERVER=xxxTest-SRV,51333;

Answer 2

I ran into the same kind of issue, but my scenario is connecting to SQL server hosted on ec2 instance through AWS Lambda function using PyOdbc module. For me, replacing the host name with IP address of ec2 instance fixed it.

I found out that it was not able to resolve dns. So if any of the above steps didn't work for you, please try using the ip address and comment here