Connecting to SQL Server via SQL magics in Jupyter Lab

Question

I am trying to connect to our remote sql server db via Jupyter Labs using the SQL magics. I can connect with a traditional connection string, but the SQL magics doesnt seem to work (which means I am doing something wrong). Below is the working pyodbc connection:

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=Server;'
                      'Database=DB;'
                      'Trusted_Connection=yes;')

But when I try to connect with the magics I get an error that says no connection string supplied

%load_ext sql
%sql engine = create_engine("mssql+pyodbc://user:password@server/db")

Ideally, I would like to connect with Magics using a DSN (I tried this also and didnt work):

%load_ext sql
%sql engine = create_engine("mssql+pyodbc://DSN;Trusted_Connection = Yes")

Any help on getting a connection working?

Answer 1

Try this from a Jupyter cell:

import urllib
params = urllib.parse.quote_plus("DRIVER={SQL SERVER};SERVER=Server;DATABASE=DB;TRUSTED_CONNECTION=YES")
"mssql+pyodbc:///?odbc_connect=%s" % params

Then run the cell and copy/paste the encoded string that is returned from its evaluation into the magic.

%%sql mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BSQL+SERVER%7D%3BSERVER%3DServer%3BDATABASE%3DDB%3BTRUSTED_CONNECTION%3DYES
   [your SQL code here]