"Login timeout expired" when connecting to Azure SQL Server with Python using SQLAlchemy when using ActiveDirectoryPassword authentication

Question

I am encountering an issue connecting to an Azure SQL Server instance with Python using SQLAlchemy.

Here is how I am constructing my connection string (using example values):

import sqlalchemy as sa

username = "username@domain.com"
password = "abc123"
host = "hostname.database.windows.net"
database = "databasename"
authentication = "ActiveDirectoryPassword"
conn_string = sa.engine.url.URL(
    "mssql+pyodbc",
    username=username,
    password=password,
    host=host,
    port=1433,
    database=database,
    query={"driver": "ODBC Driver 17 for SQL Server", "authentication": authentication},
)

Then once I have the connection string, I am connecting to the database with the following code:

engine = sa.create_engine(conn_string, pool_timeout=30)
connection = engine.connect()

But that last line results in the following exception after 30 seconds:

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

However, I do not encounter the issue when logging in as an administrator using the SqlPassword authentication mode, so I think I have ruled out any connection issues, firewalls, etc. I have also tried changing my password to ensure it does not contain any special characters, in case they were being improperly escaped in the connection string.

If SQLAlchemy may be the issue, is there any tool I can use to test the connection? I have tried Azure Data Studio and that works, but seems to use federated auth rather than ActiveDirectoryPassword. I also found this tool on the connection troubleshooting page, but it will not run on my M1 Macbook's CPU.

The client is running on a Debian Linux Docker container on an M1 Macbook. Here are the versions of tools I am using:
Python: 3.7.11
SQLAlchemy: 1.3.5
Microsoft ODBC connector: 17.8

Answer 1

Try to set the timeout to 30 seconds as shown here.

Please add the domain to the user name (UID) as shown below:

con = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"               
               "Server=tcp:sql-serrver.database.windows.net,1433;"
               "Database=db;"
               "UID=login@domain.com;"
               "PWD=pass123;"
               "Authentication=ActiveDirectoryPassword")