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?
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