sqlalchemy Azure SQL Token Authentication Failure

Question

We are trying to connect and insert data into our AZURE SQL DB using sqlalchemy and pandas.dataframe.to_sql using our service principal and token. The problem is that we are able to connect to one database perfectly but to another database we are getting the following error

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '<token-identified principal>'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '<token-identified principal>'. (18456)") (Background on this error at: https://sqlalche.me/e/14/rvf5)

We are trying to login via Service Principal using client id, client secret & Tenant ID. All the values have been verified and are working fine to connect to the database using Azure databricks.

Any help would be highly appreciated.

Answer 1

The problem is that we are able to connect to one database perfectly but to another database, we are getting the Login failed for user '<token-identified principal>' error.

According to documentation, if you are using any external provider then you need to create and map the required permissions to Azure AD identities.

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;

And after that, as suggested by Jason Pan , make sure to On Identity status on portal.

Updated answer:

According to Radiatelabs, the issue got fixed after copying the whole database from DEV to UAT and creating the UAT user in the database.

References: Login Failed for user ``, Login failed for user token-identified principal when web app is in an AAD Group ,and Login failed for user 'token-identified principal' but works in Data Studio