In Jupyter notebooks, how to connect to MS SQL with a different Windows user

I have Select access to a MS SQL database that I would like to extract data into a Pandas dataframe running inside a Jupyter notebook. For reasons out of my control, I have access to the database from a different user. How can I query the database from Jupyter while connected to my current user account?

Answer

This is how you can do this:

1. Python needs to be installed for all users in computer.
2. Install Visual C++ Build tools Microsoft C++ Build Tools – Visual Studio
3. Create a directory for the virtualenv accesible by all users in the computer, for example: mkdir C:virtualenv
4. Make a copy of the Command Prompt and paste in the virtualenv directory. From: C:UsersYOURUSERNAMEAppDataRoamingMicrosoftWindowsStart MenuProgramsSystem ToolsCommand Prompt To: C:virtualenvCommand Prompt
5. Run Command Prompt as different user (press shift + right click) and login with YOURDOMAIN. username: YOURUSERNAME@YOURDOMAIN or YOURDOMAINYOURUSERNAME password: your password
6. cd C:virtualenv
7. Create the virtualenv: python -m venv .
8. Connect to the virtualenv. scriptsactivate
9. Install dependencies pip install --upgrade jupyter pyodbc sqlalchemy pandas pip
10. Create a Jupyter configuration file for the user. jupyter notebook --generate-config
11. Close the Command Prompt.
12. Right click on the Command Prompt shortcut, then select Properties.
13. Clear and leave empty the values from Start in and replace the Target values with: Target: %windir%system32runas.exe /user:YOURUSERNAME@YOURDOMAIN “CMD /k "cd C:virtualenv && Scriptsactivate && jupyter notebook”" **Optional: You may add /savecred (after/user) and it will remember the password. For security reasons you should not do this.
14. Double click the command prompt to run Jupyter.

Create a notebook to connect to your SQL database and download a table into a Pandas dataframe.

import os
import pyodbc
import sqlalchemy as db
import pandas as pd

windomain = os.environ["userdomain"]

if windomain == "YOURDOMAIN":
  server = "YOURSERVER"
  database = "YOUDATABASE"
  driver = "{SQL Server}"
  connect = "DRIVER="+driver+";SERVER="+
    server+";DATABASE="+database+
    ";Trusted_Connection=yes"
  engine = db.create_engine(
    "mssql+pyodbc:///?odbc_connect={}".format(connect))
else:
  print("Open as different user.")

df = pd.read_sql(
  """
  SELECT TOP(100) *
  FROM [YOURDATABASE]
  """,
  con=engine,
)