sql server - 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;