Example authenticating a daemon app

[fleming79]

Similar to #480 & #127

Would it possible to to provide an example of how to connect using tokens generated via app registration like in the MSAL example here.

[github-actions]

Hi @fleming79, thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[jahnvi480]

@fleming79 We have an example in our wiki that shows how to authenticate using an access token via the attrs_before parameter:

-> Microsoft Entra ID support – Authenticating with an Access Token

In our wiki example we are using DefaultAzureCredential to acquire the token, but the same pattern works with MSAL. You just need to acquire the token using MSAL's ConfidentialClientApplication (for app registrations with a client secret/certificate) and pass it through attrs_before in the exact same way. For example:

import struct
import msal
import mssql_python

# MSAL app registration config
app = msal.ConfidentialClientApplication(
    "your-client-id",
    authority="https://login.microsoftonline.com/your-tenant-id",
    client_credential="your-client-secret",
)

result = app.acquire_token_for_client(scopes=["https://database.windows.net/.default"])

if "access_token" not in result:
    raise Exception(f"Token acquisition failed: {result.get('error_description')}")

# Convert to SQL Server format (same as the wiki example)
token = result["access_token"]
token_bytes = token.encode("UTF-16-LE")
token_struct = struct.pack(f"<I{len(token_bytes)}s", len(token_bytes), token_bytes)

# Pass token via attrs_before
conn = mssql_python.connect(
    "SERVER=tcp:your-server.database.windows.net,1433;"
    "DATABASE=your-database;"
    "Encrypt=yes;",
    attrs_before={1256: token_struct},  # 1256 = SQL_COPT_SS_ACCESS_TOKEN
)

The key takeaway is that the driver doesn't care how you acquire the token, the attrs_before mechanism accepts any valid access token. So you can swap in MSAL, azure-identity, or any other token provider as long as you encode the token in the expected struct format shown above.

[fleming79]

@jahnvi480 - Thank you very much for providing the example.

I'm still seeing a RuntimeError:
RuntimeError("[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user ''.")

So far I've added the app to a security group which has write access to the database. I've confirmed the security group gives access with ActiveDirectoryPassword style authentication.

[dlevy-msft-sql]

@jahnvi480 - Thank you very much for providing the example.

I'm still seeing a RuntimeError: RuntimeError("[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user ''.")

So far I've added the app to a security group which has write access to the database. I've confirmed the security group gives access with ActiveDirectoryPassword style authentication.

Hi @fleming79 - It sounds like you might be trying to do service principal auth: https://github.com/microsoft/mssql-python/wiki/Microsoft-Entra-ID-support#4-using-service-principal-authentication. Have a look at that example and see if it is what you need.

[fleming79]

@dlevy-msft-sql - Thanks - yes that is what I needed!