CentOS Question posted in
The official documentation can be found here.

Unable to set up SQL Server backend using pyodbc for airflow on a Centos 7 server

I’m trying to set up SQL Server backend for airflow. But getting this timeout error, when I do airflow initdb:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) (‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)

My connection string in airflow.cfg looks like:

sql_alchemy_conn = mssql+pyodbc://user:[email protected],1433/test_db?driver=ODBC+Driver+17+for+SQL+Server

I installed odbc drivers using:
https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#microsoft-odbc-driver-13-for-sql-server

My odbcinst.ini file looks like:

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

I went through these posts:

Pyodbc: Login Timeout Error

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

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

Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio

Most of these solutions are about: using SQL Server IP instead of instance name and appending port with IP. But, I’m trying to connect that way already.

When I’m trying to connect to sql server through python venv using:

import pyodbc
server = 'xx.xx.xx.xx'
database = 'test_db'
username = 'user'
password = 'password'
port = '1433'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';PORT='+port+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Tried above connection string without port as well. Still getting the same time out error.

Any help regarding this would be appreciated. Thanks..

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This problem got fixed by internal request, it was a firewall issue.


  2. 0

    I’d look to your connection string.

    For a start there’s a typo in the example you’ve given a comma before defining your PORT variable…. but it looks like there’s a different shape to connection strings based on your chosen SQL Server driver. And you look like you are using pymssql format rather than pyodbc despite using the ODBC driver.

    From SQLALchemy docs https://docs.sqlalchemy.org/en/13/core/engines.html#microsoft-sql-server

    # pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search