Login timeout error when connecting to Azure SQL server using linux VM

Question

I am trying to connect to Azure SQL server (authentication via active directory password) on a Linux VM using Python and sqlalchemy:

The error is:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)'
)
(Background on this error at: https://sqlalche.me/e/14/e3q8)

ODBC driver installed on VM:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

Connection string used:

driver= "ODBC Driver 18 for SQL Server", endpoint="####-####.database.windows.net", dbname="sql#####", user="xxxxx@xxx.com", password="abc123"

connection_string = "DRIVER={};SERVER={},port=1433;DATABASE={};UID={};PWD={};Authentication=ActiveDirectoryPassword;".format(driver, endpoint, dbname, user, password)

connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})

engine = sqlalchemy.create_engine(connection_url, fast_executemany=True, pool_size=100) conn = engine.connect()

Note:

1. SQL Server has only active directory password authentication
2. I have tried the endpoint with IP as well, no luck

Answer 1

I created linux virtual machine and sql database with Active directory authentication only. in Azure portal. I installed ODBC Driver 17 for SQL Server in the virtual machine, and I tried to connect Azure sql database with active directory authentication using below code

import sqlalchemy as sa
   
username = "server"
password = "password"
host = "dbservere.database.windows.net"
database = "db"
authentication = "ActiveDirectoryPassword"
conn_string = sa.engine.url.URL.create(
     "mssql+pyodbc",
     username=username,
     password=password,
     host=host,
     port=1433,
     database=database,
     query={"driver": "ODBC Driver 17 for SQL Server","authentication": authentication},
 )
engine = sa.create_engine(conn_string, pool_timeout=30)
connection = engine.connect()

I got the same error.

Image for reference:

I uninstall the ODBC Driver 17 for SQL Server in the virtual machine, and install ODBC Driver 13 for SQL Server in the virtual machine again I tried with the above changing odbc driver version as below

import sqlalchemy as sa
   
username = "username"
password = "password"
host = "server.database.windows.net"
database = "db"
authentication = "ActiveDirectoryPassword"
conn_string = sa.engine.url.URL.create(
     "mssql+pyodbc",
     username=username,
     password=password,
     host=host,
     port=1433,
     database=database,
     query={"driver": "ODBC Driver 13 for SQL Server","authentication": authentication},
 )
engine = sa.create_engine(conn_string, pool_timeout=30)
connection = engine.connect()

it connected successfully to the Azure sql database. Image for reference:

It worked for me kindly check from your side.