How to Incorporate/Structure Interactive Database Authentication into a Deployable Multipage App

Dash Python
1

Hello, I’m relatively new to Python (~1 year) and this is my first post, so apologies for any terminology/content/formatting issues. Preemptively, I’ll note that I’ve seen other threads demonstrating dash-auth and flask login, but I’m not sure if/how those authentication flows can be adapted to database authentication.

I’m trying to incorporate user interactive database authentication into a multipage app. I generally structure my apps consistent with the best practices outlined here, and also include a module (e.g., utils.conn.py) with SQLAlchemy engines pointing to my org’s “on prem” servers. Each page module for my dashboard imports the relevant engine (from utils.conn) and then loads the data prior to definition of the page layout, which often times depends in part on the underlying data (e.g., the options for a dropdown component would depend on certain values in the data).

However, for reasons that are out of my control, in order to deploy my dash app, I need to point the database engines to my org’s Azure SQL managed instance, which requires user interactive authentication (i.e., getting device credentials , getting an access token, then establishing the database connection with token-based authentication). Although I’ve written scripts that can, in isolation, acquire the access token, store it in browser, and establish the database connection, I’m having issues tying everything together within the app.

The critical issue is that the app can’t run until the database connection is established (because the page layouts depend in part on the imported data), but the database connection can’t be established until the app runs and presents the user with the authentication instructions and code. (Note that when I run this locally I can print the authentication instructions to the console log, but that won’t be possible when the app is deployed.) What is the optimal way to resolve this apparent catch-22? Can the file structure outlined in the best practices post still be used? Would I need to modify each page layout (wrap page layouts in functions or something), or is there some sort of way to delay reading the page layouts until database authentication has been established? I’m currently researching flask blueprints, but not sure whether I’m on the right track.

Although this is more of a conceptual question, below is some sample code that works when running locally but fails when I try to restructure the authentication process into the app components (using dcc.Store to check for/acquire token and dbc.Modal to relay messages), resulting in circular imports (ImportError: cannot import name ‘engine’ from partially initialized module ‘src.app’ (most likely due to a circular import)).

Thank you for any advice/assistance.

file utils.conn.py

from msal import PublicClientApplication
from sqlalchemy import URL, create_engine

# public client app
public_client_app = PublicClientApplication(
    client_id=CLIENT,
    authority="https://login.microsoftonline.com/{}".format(TENANT)
)
# get device creds - this prompts the user to go to a website and enter a code
device_flow = public_client_app.initiate_device_flow(scopes=SCOPES)

# this returns, among other things, the access token
auth_result = public_client_app.acquire_token_by_device_flow(flow=device_flow)

# once the token is converted into bytes, it can be used to establish the database connection
conn_string = "DRIVER={};SERVER={};ENCRYPT=yes;".format(DRIVER, SERVER)
conn_url = URL.create("mssql+pyodbc", query={"odbc_connect": conn_string})
engine = create_engine(
    conn_url,
    connect_args={"attrs_before": {SQL_COPT_SS_ACCESS_TOKEN: token}},
    fast_executemany=True
)

file pages.somepage.py

from utils.conn import engine

# data imports
with engine.connect() as connection:
    query_stuff = text(r"""
    SELECT *
    FROM [DATABASE].[DBO].[TABLE_NAME]
    """)
    df_stuff = pd.read_sql_query(query_stuff, connection)

register_page(__name__, name="somepage")
layout = dbc.Container([...dcc/dbc Components based on df_stuff], fluid=True, className="dbc")

app.py

# imports
...

app = Dash(__name__, external_stylesheets=[dbc.themes.CYBORG], use_pages=True)
app.layout = dbc.Container(
    [
        navbar,
        page_container
    ],
    fluid=True,
    style={"padding": 0},
    className="dbc"
)

if __name__ == "__main__":
    app.run(debug=True)
2

I came up with a functional, but perhaps suboptimal, solution similar to what I posited in the original post. In the main app.py script, I created an initial callback that checks dcc.Store for the existence/validity of the azure access token. Depending on that condition, a modal is displayed either with authentication instructions or information regarding the validity and duration of the token in local storage. In addition, each page layout is wrapped in a callback function that returns either a default blank layout or the desired layout depending on existence of the access token. These functions also import the data for the page layout.

Keeping the structure of the project consistent with the best practices, the solution would look something like this.

app.py

# imports
# ...
app = Dash(__name__, external_stylesheets=[dbc.themes.CYBORG], use_pages=True)
app.layout = dbc.Container(
    [
        navbar,
        modal_01,
        modal_02,
        page_container,
        dcc.Store(
            id="authentication_store_token",
            storage_type="local"
        )
    ],
    fluid=True,
    style={"padding": 0},
    className="dbc"
)

# modal callbacks to relay authentication instructions to user...
@callback(
    Output("authentication_modal_01", "is_open"),
    Output("authentication_modal_01_body", "children"),
    State("authentication_store_token", "data"),
    State("authentication_modal_01", "is_open"),
    Input("authentication_modal_01_button", "n_clicks")
)
def toggle_authentication_modal_01(stored_token_data, opened, n_clicks):
    # desired logic/workflow, update number of modals and Inputs/Outputs as needed 
    # e.g.
    if ((token is None) | expired) & (not opened):
        #... get device creds (see authentication script in previous post) ...
        # open modal, return authentication instructions
        return True, return_message
    
    elif: #  etc...



if __name__ == "__main__":
    app.run(debug=True)

somepage.py

# imports
# ...

# initialize variable
df_stuff = None


# page layout with no children/content
register_page(__name__)
layout = dbc.Container(
    id="stuff_layout",
    fluid=True,
    className="dbc"
)


# callback to render layout depending on existence/validity of token
@callback(
    Output("stuff_layout", "children"),
    Input("authentication_store_token", "data")
)
def update_page_layout(stored_token_data):
    global df_stuff

    # The function here is a custom function to parse the data and determine whether token is valid
    token, token_expiration, token_expiration_datetime, expired = parse_token(stored_token_data)

    # if no token or token is expired, return default layout
    if (token is None) or expired:
        children = [html.P("Need to Authenticate")]
        return children

    # otherwise, display page and import data (if not already done)
    else:
        if df_stuff is None:
            # will need to zero pad token first, but then can use token to establish database
            # connection
            engine = create_engine(
                conn_url,
                connect_args={"attrs_before": {SQL_COPT_SS_ACCESS_TOKEN: token}}
            )

            # import data
            with engine.connect() as connection:
                query_stuff = text(r"""
                SELECT *
                FROM [DATABASE].[dbo].[TABLE_NAME]
                """)
                df_stuff = pd.read_sql_query(query_stuff, connection)
                
        # return page layout
        return [
            # enter desired page layout with components that depend on DataFrame values....
        ]
    
    
# regular component callbacks...
# ...

If anyone has come up with something different, I’d be interested to see the approach.

3

Could you build a Dash component with your solution? This would be major contribution, Dash (free version) is really lacking authentication features…