How to use Federated credentials with service principal to auth the Azure resources

[kachely26]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 3.10
• pyodbc: 5.0.1
• OS: linux ubuntu 18.04
• DB: Azure Synapse serverless DB
• driver: pyodbc

Issue

Due to security issue, I cannot use clientid + secret when I use service principal as the authentication method.
Then how can I use federated credentials to do the authentication?
Thank you!

Often it is easiest to describe your issue as "expected behavior" and "observed behavior".

[v-chojas]

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

[gordthompson]

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 ?

[v-chojas]

@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.

[kachely26]

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) })

[v-chojas]

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

[kachely26]

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?

[v-chojas]

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

[kachely26]

Hi @v-chojas @gordthompson, thanks for the previous help! I can use method here to connect Azure SQL Server.

def bytes2mswin_bstr(value: bytes) -> bytes:
    encoded_bytes = bytes(chain.from_iterable(zip(value, repeat(0))))    
    return struct.pack("<i", len(encoded_bytes)) + encoded_bytes

tokenstruct = bytes2mswin_bstr(token.encode())

conn = pyodbc.connect(connstr, attrs_before = {1256:tokenstruct}, timeout=30)

However, I tried to use same method to connect Azure Synapse serverless database but failed. Do you know if there is any limitation on Synapse?