'Pyodbc: Login Timeout Error

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

sql-serverpython-3.xubuntu-16.04pyodbc


Solution 1:[1]

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

Solution 2:[2]

I ran into same problem when working with SQL Server docker container inside VS Code development container. Finding the IP of SQL Server container (credits for that goes to freecodecamp.org How to get docker container IP address) and replacing server name with IP solved the problem

    conn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};" + "SERVER=172.xx.x.x;"
            "DATABASE=xxx;"
            "UID=sa;"
            "PWD=xxx;")

Solution 3:[3]

What solved in my case was to configure in the "SQL Configuration Manager" --> SQL Server Network Configuration --> Protocols for XYX --> TCP/IP --> Properties --> IP Addresses --> IPAII (Here set empty in TCP Dynamic Ports and 1433 (or the port that you want to use) at TCP Port)

You can have more details here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vin Odh
Solution 2 M.wol
Solution 3 Claudio Shigueo Watanabe

Related Questions