feat(mssql): Native support for interactive Active Directory Authentication

[inigohidalgo]

Is your feature request related to a problem?

Following on from the discussion here #7317 (comment) I would like to raise the discussion to see what y'all's thoughts are.

Describe the solution you'd like

What reminded me of this discussion is that looking through the snowflake backend parameters I saw the authenticator="oauth" and I thought it was a possible way to add that capability natively.

What version of ibis are you running?

8.X:X

What backend(s) are you using, if any?

MSSQL
(Snowflake)

Code of Conduct

• I agree to follow this project's Code of Conduct

[inigohidalgo]

For reference this is how I am doing things atm

import struct
from azure import identity
import sqlalchemy as sa


SQL_COPT_SS_ACCESS_TOKEN = 1256  # Connection option for access tokens, as defined in msodbcsql.h
TOKEN_URL = "https://database.windows.net/"  # The token URL for any Azure SQL database


azure_credentials = identity.DefaultAzureCredential()

def provide_token(dialect, conn_rec, cargs, cparams):
    """sqlalchemy.event.listens_for(ibis_conn.con, "do_connect")(provide_token)
    """
    # remove the "Trusted_Connection" parameter that SQLAlchemy adds
    cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")

    # create token credential
    raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le")
    token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token)

    # apply it to keyword arguments
    cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}

def make_ibis_connection_login_ad_interactive(conn):
    sa.event.listens_for(conn.con, "do_connect")(provide_token)

raw_conn = ibis.connect(
    f"mssql://{os.environ['SERVER_NAME_ENV']}/{os.environ['DB_NAME_ENV']}",
    query = {
        "driver": "ODBC Driver 17 for SQL Server",
    }
)
make_ibis_connection_login_ad_interactive(raw_conn)

[cpcloud]

cc @lostmygithubaccount who knows a thing or two about Azure 😉

[cpcloud]

@inigohidalgo Still interested in this? I'll close later today if I don't hear from you :)

[inigohidalgo]

Hi @cpcloud it's been a while since I picked up ibis as we've been focusing on other areas of our stack, but it is still relevant for us, and MS authentication is still relevant to us. I don't mind closing this if nobody else has expressed interest, as this is handleable by us subclassing the object introduced here kedro-org/kedro-plugins#560 @deepyaman

[inigohidalgo]

Actually I remember @gforsyth asked me if I could update the documentation to explain my connection method. I was happy to do that but have not had time to address it. I would still like to update the documentation page if you think it would be useful, I don't think I'll have time to do it any time soon but I can self-assign a separate documentation issue, what do you think?

[cpcloud]

That sounds good to me!