Connect to Microsoft SQL Database using official tools

petter.olssonpetter.olsson Member, Moderator, Domino Posts: 70 mod
edited November 26 in How-To Guides

Hi,

As we have seen pymssql not being developed for a while due to Microsoft open sourcing these tools we can now use this Dockerfile to have native MS SQL Client support in Linux. This describes Ubuntu (Specifically 16.04) but can very easily be changed.

#Gives you Sudo access in container
RUN echo "ubuntu    ALL=NOPASSWD: ALL" >> /etc/sudoers

# SQL Server Command Line Tools
LABEL maintainer="Domino Data Lab"

# apt-get and system utilities
RUN apt-get update && apt-get install -y update-manager-core apt dpkg curl apt-transport-https debconf-utils
RUN rm -rf /var/lib/apt/lists/*

# adding custom MS repository - change Ubuntu version here as needed
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# install SQL Server drivers and tools
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN /bin/bash -c "source ~/.bashrc"

USER ubuntu 
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN /bin/bash -c "source ~/.bashrc"

USER root
RUN apt-get -y install locales
RUN locale-gen en_US.UTF-8
RUN update-locale LANG=en_US.UTF-8

CMD /bin/bash

There are several environment variables you optionally can set up to store secure information about your MSSQL connection. Set the following as Domino environment variables on your user account:

DB_SERVER
DB_USERNAME
DB_PASSWORD

For more information on how-to use the sqlcmd itself we suggest the following read: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

Have a powerful day!

Petter

Sign In or Register to comment.