About Domino
Domino Data LabKnowledge BaseData Science BlogTraining

Connect to MSSQL

This topic describes how to connect to a Microsoft SQL Server (MSSQL) from Domino.

Warning
Connect to MSSQL

  1. To interact with MSSQL databases from Python, Domino recommends the pymssql package.

  2. Use the following Dockerfile instruction to install pymssql in your environment.

This instruction assumes you already have pip installed.

USER root
RUN pip install pymssql
USER ubuntu

  1. You must set up the following Domino environment variables to store secure information about your MSSQL connection.

    • DB_SERVER

    • DB_USERNAME

    • DB_PASSWORD

      See Secure Credential Storage to learn more about Domino environment variables.

      See the pymssql documentation for detailed information about how to use the package. The following is an example for connecting to MSSQL with Python where:

    • You have set up environment variables noted previously.

    • The server hosts a database named myData with a table named addresses.

      from os import getenv
import pymssql

server = getenv("DB_SERVER")
user = getenv("DB_USERNAME")
password = getenv("DB_PASSWORD")

conn = pymssql.connect(server, user, password, "myData")
cursor = conn.cursor()

cursor.execute('SELECT * FROM addresses')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

conn.close()
Connect R and RODBC to MSSQL

  1. To interact with MSSQL databases from R, Domino recommends the RODBC library. You can use an alternative package if you’d like.

  2. Use the following Dockerfile instruction to add the MSSQL drivers to your Ubuntu 16.04 environment.

USER root

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
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install msodbcsql17

USER ubuntu

  1. See Microsoft’s documentation for the Dockerfile instructions to add the MSSQL drivers to your Domino-supported Ubuntu environment.

  2. See the RStudio RODBC documentation for information about how to use the package.