How to retrieve data from an SQL database using Python's Pyodbc with tokens？(Oauth)

Hello Shinichiro Takabe,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to retrieve data from an SQL database using Python's Pyodbc with token (Oauth).

My recommendation is that:

Use azure.identity.DefaultAzureCredential (works for managed identity, local dev via az cli, or service principal)

# Requires: pip install pyodbc azure-identity
import pyodbc, struct
from azure.identity import DefaultAzureCredential
SQL_COPT_SS_ACCESS_TOKEN = 1256
TOKEN_SCOPE = "https://database.windows.net/.default"
server = "yourserver.database.windows.net"
database = "yourdb"
# connection string: no UID/PWD/Authentication when using access token
conn_str = (
    f"Driver={{ODBC Driver 18 for SQL Server}};"
    f"Server=tcp:{server},1433;"
    f"Database={database};"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
)
# Acquire token
cred = DefaultAzureCredential()  # will try ManagedIdentity, env vars, Azure CLI, etc.
token = cred.get_token(TOKEN_SCOPE).token  # string
# Prepare token in the exact format the ODBC driver expects:
# expand to UTF-16-LE (each ASCII char becomes char + 0x00), then pack length
token_bytes = token.encode("utf-16-le")          # critical step (expands bytes)
token_struct = struct.pack("<I", len(token_bytes)) + token_bytes
# Connect
conn = pyodbc.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
cursor = conn.cursor()
cursor.execute("SELECT SUSER_SNAME(), ORIGINAL_LOGIN()")
print(cursor.fetchone())

Check the following links for more details:

• https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver17, https://docs.sqlalchemy.org/en/latest/dialects/mssql.html
• DefaultAzureCredentialis the recommended library for managed identities and local dev token acquisition. Useget_token("https://database.windows.net/.default")` for the Azure SQL resource - https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview-for-developers

The second option is to use MSAL Confidential Client (service principal / client credentials like the below:

# Requires: pip install pyodbc msal
import pyodbc, struct
from msal import ConfidentialClientApplication
SQL_COPT_SS_ACCESS_TOKEN = 1256
TOKEN_SCOPE = ["https://database.windows.net/.default"]
server = "yourserver.database.windows.net"
database = "yourdb"
client_id = "<app-client-id>"
client_secret = "<app-client-secret>"
authority = "https://login.microsoftonline.com/<tenant-id>"
conn_str = (
    f"Driver={{ODBC Driver 18 for SQL Server}};"
    f"Server=tcp:{server},1433;"
    f"Database={database};"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
)
app = ConfidentialClientApplication(client_id=client_id, authority=authority, client_credential=client_secret)
result = app.acquire_token_for_client(scopes=TOKEN_SCOPE)
access_token = result.get("access_token")
if not access_token:
    raise RuntimeError("Failed to acquire token: " + str(result))
# Expand token into the ACCESSTOKEN struct
token_bytes = access_token.encode("utf-16-le")
token_struct = struct.pack("<I", len(token_bytes)) + token_bytes
conn = pyodbc.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
cursor = conn.cursor()
cursor.execute("SELECT SUSER_SNAME(), ORIGINAL_LOGIN()")
print(cursor.fetchone())

Check the following links for more details:

• https://learn.microsoft.com/en-us/entra/msal/python/getting-started/acquiring-tokens

Thirdly, what to do or run if you still get 18456 is to confirm which identity SQL sees (run after you connect successfully or attempt connect with token):

SELECT SUSER_SNAME(), ORIGINAL_LOGIN();

-- If principal is not a DB user, create it (must be done by an Azure AD admin or server admin):
-- run as an admin connected to the target DB
CREATE USER [******@yourtenant.onmicrosoft.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [******@yourtenant.onmicrosoft.com];

-- Check this link for more details: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure
-- Also, if you changed roles, flush auth caches (after role changes, sometimes required):
DBCC FLUSHAUTHCACHE;
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS;

Check this link for server-login guidance tutorial - https://docs.azure.cn/en-us/azure-sql/database/authentication-azure-ad-logins-tutorial

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.