This topic describes how to connect to a Microsoft SQL Server (MSSQL) from Domino.
The easiest way to connect to a MSSQL instance from Domino is to use a Domino Data Source.
-
From the navigation pane, click Data.
-
Click Create a Data Source.
-
In the New Data Source window, from Select Data Store, select SQL Server.
-
Enter the Host. Valid values are
<host string>:3306
or<host string>
. -
Enter the Port.
-
Enter the name of the Database.
-
Enter the Data Source Name.
-
Optional: Enter a Description to explain the purpose of the data source to others.
-
Click Next.
-
Enter the Username and Password to connect to SQL Server. The Domino secret store backed by HashiCorp Vault securely stores the credentials.
-
Click Test Credentials.
-
If the data source authenticates, click Next.
-
Select who can view and use the data source in projects.
-
Click Finish Setup.
Domino recommends the pymssql package for interacting with MSSQL databases from Python.
Environment setup
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
Credential setup
There are several environment variables you must 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
Read Environment variables for secure credential storage to learn more about Domino environment variables.
Usage
Read the pymssql documentation for detailed information about how to use the package. Below is a simple example for connecting to MSSQL with Python where:
-
You have set up environment variables noted above
-
The server hosts a database named
myData
with a table namedaddresses
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()
Domino recommends the RODBC library for interacting with MSSQL databases from R; however, you can use an alternative package if you’d like.
Environment setup
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
See Microsoft’s documentation for the Dockerfile instructions to add the MSSQL drivers to your Domino-supported Ubuntu environment.
Usage
Read the RStudio RODBC documentation for detailed information about how to use the package.