“Login timeout expired” error when accessing MS SQL db via sqlalchemy and pyodbc

10
votes

So I have some trouble getting sqlalchemy and pyodbc working with a remote MS SQL Server. Local sqlcmd worked properly but not when I try to read the db via python code. Any help would be appreciated.

Environment:

  • Centos 7
  • SQLCmd version: Version 17.1.0000.1 Linux
  • MS SQL Server 6.01.7601.17514
  • Python 2.7

The following sqlcmd worked properly

sqlcmd -S {Host},{Port} -U {USER} -P {PWD} -Q "use {Database};"

Attempts to work with sqlalchemy or pyodbc directly didn't work. Error:

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

Code: Attempt with pyodbc

conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=HOST,PORT;'
    r'DATABASE=DATABASE;'
    r'UID=UID;'
    r'PWD=PWD'
    )

Attempt with sqlalchemy:

create_engine('mssql+pyodbc://{user}:{password}@{host}:{port}/{database}?driver={driver}'.format(
        user=user,
        password=password,
        host=host,
        database=database,
        port=port,
        driver="ODBC+Driver+17+for+SQL+Server"
    )).connect()

I can reproduce the error with sqlcmd if I remove the port from the command, so maybe the conn_string I am passing to pyodbc is not in the correct format?

3
sql-serversqlalchemypyodbcsqlcmd
I am having the same problem but on my Mac machine. Only difference is that I am using OBDC driver 13 instead. Did you find a solution to this problem? – Solaiman
Even i'm facing the same problem with ODBC driver 13 on redhat. Did you get any solution ? – Rachel
@Solaiman late reply here, but the answer below is correct. It ended up being a typo-caused DNS issue for me. It is worth trying the endpoint outside the python code first before debugging this. – ttback

3 Answers

2
votes

The problem might be DNS related, as you can read here.
Try to use an IP address, instead of the hostname, in the connection string, or check your DNS configuration.

1
votes

In my case, this happened when I didn't properly escape passwords that has special characters. This was my solution:

from urllib.parse import quote
...
passwd = 'p@ssw0rd!'
...
engine_string = f"mssql+pyodbc://{user}:{quote(passwd)}@{host}/{name}?driver=ODBC+Driver+17+for+SQL+Server"
-1
votes

What does your python code do? Problem might be multiple Connections calls. Dont open the connection in a loop. Or conn.close() at the wrong point. Other problem could be a firewall rule issue, check it.

I use pymssql to acces to my sql server. Read the documentation and install pymssql and freetds-dev on your centos system. Maybe u need to edit freetds.conf and add the ip and port of your sql server.