Tuesday, August 30, 2022

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

Issue

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://docs.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..


Solution

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



Answered By - sm925
Answer Checked By - Willingham (WPSolving Volunteer)