A while since my last post and so much that I nearly forgot that I had this site and what made it useful to me. In the world of AI it becomes nearly pointless to write down helpful hints and tips that I can refer back to myself but this site still gives me somewhere useful to store code snippets personal to me, and also might still help others.

On to the problem. I’ve recently started a new role as a Data Engineer and entering the world of Python development, which is new and shiny but also there’s a few concepts that I need to learn. This is such a concept: when working inside a notebook how do I log on to SQL Server as a different Windows user to the one that I’m currently logged on as. This is useful when databases use different Windows users for added layers of security.

The key is grab a token using the win32security package to impersonate the user you want. With a little bit of getpass usage to save storing credentials.

On to the code:

import pandas as pd
from sqlalchemy import create_engine
import getpass
import win32security
import win32con

# Get our credentials of the user we want to logon as
sql_server_username = getpass.getpass("Enter SQL Server username")
sql_server_password = getpass.getpass("Enter SQL Server password")

# Generate a windows token to use to impersonate our user
token = win32security.LogonUser(
    sql_server_username,
    None,
    sql_server_password,
    win32con.LOGON32_LOGON_INTERACTIVE,
    win32con.LOGON32_PROVIDER_DEFAULT
)

# Impersonate the logged on user
win32security.ImpersonateLoggedOnUser(token)

# Create our engine and execute our query
engine = create_engine(f"mssql+pyodbc://{sql_server_instance}/{sql_server_database}?driver=ODBC+Driver+17+for+SQL+Server")

df_result = pd.read_sql_query(your_query, engine)

# Revert back to self, so further queries will run under the default windows account
win32security.RevertToSelf()