Support access token when creating new connection (SQL_COPT_SS_ACCESS_TOKEN)

[kafisatz]

Would it be possible to support a token_struct (SQL_COPT_SS_ACCESS_TOKEN) for Azure authentication?
See the snippet below

I note that I am happy to use PyCall to get the value of the token. (I am aware of Azure.jl, but the below functionality is probably not in there yet)

My question for the ODBC.jl developers is only whether ODBC.Connection could support a token similar to the python snippet below.
I tried using "Authentication=ActiveDirectoryIntegrated", but this does not work for me. "ActiveDirectoryInteractive" works, but it is cumbersome to enter the pw every time in the age of SSO.

username = "[email protected]"
azureDBserverurl = "tcp:xx.database.windows.net,1433"
odbc_string = "Driver={ODBC Driver 18 for SQL Server};Server=$(azureDBserverurl);Database=fortuna;Uid={$(username)};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=12;Authentication=ActiveDirectoryInteractive"
    conn = ODBC.Connection(odbc_string)

python snippet using a token

import time 
import struct
import urllib
from sqlalchemy import create_engine
from sqlalchemy import text
from sqlalchemy.engine import URL

from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential() # system-assigned identity

# Get token for Azure SQL Database and convert to UTF-16-LE for SQL Server driver
token = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token)}s', len(token), token)

# Connect with the token
SQL_COPT_SS_ACCESS_TOKEN = 1256

connection_string = """Driver={ODBC Driver 18 for SQL Server};Server=tcp:xxx.database.windows.net,1433;Database=fortuna;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=10;""" 
params = urllib.parse.quote(connection_string)
engine = create_engine("mssql+pyodbc:///?odbc_connect={0}".format(params),connect_args={'attrs_before': {SQL_COPT_SS_ACCESS_TOKEN:token_struct}})

with engine.connect() as connection:
    result = connection.execute(text("SELECT 1"))
    
with engine.connect() as connection:
    rs = connection.execute(text("SELECT TOP (1000) * from testtable"))
    print(rs.fetchall())

[toollu]

I'm having the same problem. Other than @kafisatz I'm actually able to retrieve a valid token without Python via AzSessions.jl

using AzSessions
cid="foo"
cse="bar"
tenant="baz"
session = AzSession(;protocol=AzClientCredentials, client_id=cid, client_secret=cse)
t = token(session)

Concerning this issue:

There seems to be no obvious way to set the correct pre connection attribute. I gues this would need extension of src/API.jl or maybe we can use SQLSetEnvAttr directly somehwere?

Maybe @quinnj can chime in and point us towards the solution? I have a feeling it's not to big of a change but I'm honestly a little bit overwhelmed by the complexity of these whole DB driver things.

According to the MS docs, in C++ it can be done like this:

    SQLCHAR connString[] = "Driver={ODBC Driver 18 for SQL Server};Server={server};Encrypt=yes;"
    SQLCHAR accessToken[] = "eyJ0eXAiOi..."; // In the format extracted from an OAuth JSON response
    ...
    DWORD dataSize = 2 * strlen(accessToken);
    ACCESSTOKEN *pAccToken = malloc(sizeof(ACCESSTOKEN) + dataSize);
    pAccToken->dataSize = dataSize;
    // Expand access token with padding bytes
    for(int i = 0, j = 0; i < dataSize; i += 2, j++) {
        pAccToken->data[i] = accessToken[j];
        pAccToken->data[i+1] = 0;
    }
    ...
    SQLSetConnectAttr(hDbc, SQL_COPT_SS_ACCESS_TOKEN, (SQLPOINTER)pAccToken, SQL_IS_POINTER);
    SQLDriverConnect(hDbc, NULL, connString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    ...
    free(pAccToken);