How to use Federated credentials with service principal to auth the Azure resources about pyodbc HOT 7 CLOSED

If you're using the msodbcsql (17 or 18) driver you can give it the access token directly: https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory#authenticating-with-an-access-token

from pyodbc.

Hey @v-chojas - Would this be the same issue as documented in the wiki here

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#authenticate-using-an-access-token-from-azure-active-directory

making this a duplicate of #228 ?

from pyodbc.

@gordthompson I wouldn't call this a duplicate without knowing more details about what the OP is trying to accomplish ("federated credentials" is vague and can refer to various things) or indeed if this is the msodbcsql driver being used.

from pyodbc.

Thank you so much @v-chojas and @gordthompson!
I think this is what I want https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#authenticate-using-an-access-token-from-azure-active-directory
Besides, when I use access token, should I set the connection as something like below?
Driver={ODBC Driver 18 for SQL Server};Server={server};Authentication=ActiveDirectoryIntegrated;Encrypt=yes;?
Which Authentication type should I use for access token authentication?
Should I set the token when calling connect function? Is it possible to set it in the connection string?
conn = pyodbc.connect(connstr, attrs_before = { 1256:bytearray(tokenstruct) })

from pyodbc.

Do not specify authentication mode when using access token.
https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16#authenticating-with-an-access-token

from pyodbc.

I got the access token from

https://review.learn.microsoft.com/en-us/identity/microsoft-identity-platform/federated-identity-credentials?branch=main&tabs=python#msal-python

Does the access token a correct one?

I see it returns me Login failed for user \'<token-identified principal>\'. The server is not currently configured to accept this token. (18456) (SQLDriverConnect)")\n(Background on this error at: http://sqlalche.me/e/13/rvf5)

Could you provide some insights here?

from pyodbc.

You need to add the corresponding account on the server with CREATE USER ... FROM EXTERNAL PROVIDER

from pyodbc.