Python connection to cloud-based SQL Server with IAM authentication and access token

Question

I am trying to connect to our cloud-based SQL Server database using Python. How to pass to pyodbc or mssql connector the ODBC driver name, IAM authentication and access token information? If it cannot be done with pyodbc and mssql, can SQLAlchemy be the solution?

I tried with pyodbc and it worked when I provided the DSN. I run the code locally (on my desktop) and Python looked up the windows registry where the detailed information of the DSN is located. However, when I used the same code online, in a code repository file, it did not work as it could not access my local registry.

Answer 1

Here's how to connect with an Access Token:

def get_conn():
    credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False)
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn

Connect to and query Azure SQL Database using Python and the pyodbc driver