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;