How can I connect to remote MSSQL server from jupyterHub notebook using windows authentication instead of MSSQL users?

I want to connect to Microsft SQL server database from jupyternotebook which is based on kubernetes z2jH installation, I made a cutome notebook image which has pyodbc & odbc inside, and could connect to MSSQL DB with sql user; however, it would be much easier to connect via Windows user through kerberos and Trusted_connection without giving explicitly username and password in connection string…

has anyone done that before? I found that if wanting to get connected from linux machine to MSSQL DB via windows authentication; it needs to install and config some packages for user integration between linux and microsoft kerberos…sth like FreeTDS package…

anyone has done it before?

this is my dockerFile for custome Notebook image with capability of connecting to MSSQL via pyodbc and ODBC driver 17:


ARG OWNER=jupyter
ARG BASE_CONTAINER=$OWNER/scipy-notebook:akhari
FROM $BASE_CONTAINER

#FROM alpine

Install Python 3 pyodbc packages

USER root

RUN apt-get update --yes &&
apt-get install --yes --no-install-recommends
curl
gnupg2
gnupg
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version

#Ubuntu 20.04
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN exit
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17

optional: for bcp and sqlcmd

RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN echo ‘export PATH="$PATH:/opt/mssql-tools/bin"’ >> ~/.bashrc
RUN source ~/.bashrc

optional: for unixODBC development headers

RUN apt-get install -y python3-pip python3-dev unixodbc-dev
RUN pip3 install pyodbc

USER ${NB_UID}

WORKDIR “${HOME}”


And this is sample of connection string and as i said it is working with SQL user but need to use windows authentication users:

import pyodbc

Some other example server values are

server = ‘localhost\sqlexpress’ # for a named instance

server = ‘myserver,port’ # to specify an alternate port

server = ‘tcp:myserver.database.windows.net’ < ——192.168.63.8

database = ‘mydb’

username = ‘myusername’

password = ‘mypassword’

cnxn = pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=’+server+’;DATABASE=’+database+’;UID=’+username+’;PWD=’+ password)

cursor = cnxn.cursor()

tnx;

The only recent discussion I can think of:

Though there may be others hidden under more searching. Unfortunately, you’re not going to find a deep well of highly-windows-specific use cases for jupyter integration: sorry about that!

1 Like