Support for specifying Token for Entra ID directly

[aersam]

Is your feature request related to a problem? Please describe.

In Azure Databricks, I can create service Credentials that are kind of User-assigned Managed identities. To use those, one gets a TokenCredential from a special Databricks-method: https://learn.microsoft.com/en-us/azure/databricks/connect/unity-catalog/cloud-services/use-service-credentials#python-example

Describe the solution you'd like

To use that with mssql_python, there is need to be able to specify a Token directly when connecting, or even to pass a TokenCredential instance somehow (which would be how many azure python sdk libraries work). Best would be to use a credential as all other Azure Python Libraries do

Describe alternatives you've considered

ServicePrincipal's would work too, but I don't like managing secrets this much

Additional Context

In .Net there is a callback for this: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver17#using-accesstokencallback

[jahnvi480]

Thanks for raising this!
mssql_python already supports Azure AD authentication via access tokens, which works well with Databricks-managed service credentials.
You can use the access token retrieved from your Databricks TokenCredential and pass it using the standard ODBC attribute SQL_COPT_SS_ACCESS_TOKEN

import mssql_python
import struct
import os

from databricks.sdk import WorkspaceClient
from databricks.sdk.service.catalog import TokenCredential

def get_conn():
    w = WorkspaceClient()
    token_credential: TokenCredential = w.service_credentials.get("my-service-credential")
    access_token = token_credential.get_bearer_token("https://database.windows.net/.default").token

    # Encode token in UTF-16LE as expected by the ODBC driver
    token_bytes = access_token.encode("utf-16le")
    return struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)

auth_token = get_conn()
SQL_COPT_SS_ACCESS_TOKEN = 1256

conn_str = os.getenv("DB_CONNECTION_STRING")
conn = mssql_python.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: auth_token})
cursor = conn.cursor()
cursor.execute("SELECT TOP 5 * FROM sys.databases")
print(cursor.fetchall())
cursor.close()
conn.close()

So, you can absolutely use your Databricks-managed identities and tokens directly — no need to fall back to service principals or secrets.
I’ll go ahead and close this issue since the feature is already supported.
If you have any more questions or run into anything else, please feel free to open a new issue. Happy to help!

[aersam]

Ok, thats great, I'll try. That beeing said, I think this deserves a more highlevel API

[sumitmsft]

Hello @aersam Thanks for your feedback!
Our goal is to make the experience consistent with other Azure SDKs, so supporting an optional credential parameter (which can accept a TokenCredential object) should address this scenario without additional complexity. If you have specific use cases that would require a higher-level abstraction beyond this, we’d love to hear more about them. For now, we’ll focus on making the credential integration as seamless as possible.

[jahnvi480]

Hello @aersam

Just wanted to follow up and see if you were able to get things working with the SQL_COPT_SS_ACCESS_TOKEN approach for your Databricks-managed identities. Was it able to solve your use case?

Also, echoing what @sumitmsft mentioned — our goal is to keep this consistent with other Azure SDKs by supporting a TokenCredential directly, so it’s great to know this is something that would help you too. If you have specific scenarios where a higher-level abstraction would make things even easier, we’d really love to hear more.

Thanks again for your feedback and for trying this out!

[aersam]

Hi there
I finally had the opportunity to replace pyodbc with mssql_python in our project and everything works with the SQL_COPT_SS_ACCESS_TOKEN approach. Thanks!