sqlalchemy won't use username with ActiveDirectoryIntegrated?

Question

I'm trying to use an Azure Active Directory credential to connect to my database, but SqlAlchemy throws an error. Here is my code:

import sqlalchemy

connexion_str = sqlalchemy.engine.URL.create(
    "mssql+pyodbc",
    username="[email protected]",
    password="password",
    host="our-db.database.windows.net:1433",
    database="db-name",
    query={
        "driver": "ODBC DRIVER 17 for SQL Server",
        "authentication": "ActiveDirectoryIntegrated",
    },
)
engine = sqlalchemy.create_engine(connexion_str)
engine.connect()

I get the following error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('FA002', "[FA002] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Impossible d'utiliser l'option d'authentification 'ActiveDirectoryIntegrated' avec les options Utilisateur ou Mot de passe. (0) (SQLDriverConnect); [FA002] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Impossible d'utiliser l'option d'authentification 'ActiveDirectoryIntegrated' avec les options Utilisateur ou Mot de passe. (0)") (Background on this error at: https://sqlalche.me/e/14/dbapi)

It's in French sorry, but this means that SqlAlchemy (or pyodbc?) won't use the 'username' or 'password' settings when used with 'ActiveDirectoryIntegrated'.

How can I connect to my DB using Azure AD credentials?

Answer 1

To connect using an Azure Active Directory account username and password, You need to specify Authentication=ActiveDirectoryPassword in the connection string.

Like this

server=Server;database=Database;UID=UserName;PWD=Password;Authentication=ActiveDirectoryPassword;

You can find more information here about using Azure Active Directory with the ODBC Driver