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

Question

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?

Answer 1

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.