domino logo
Latest (5.6)
  • Tech Ecosystem
  • Get Started
  • Domino Cloud
  • Code Assist
  • Collaborate
  • Projects
  • Work with Data
  • Workspaces
  • Environments
  • Executions
  • Deploy Models and Apps
  • Model Monitoring
  • Organizations
  • Security and Credentials
  • Notifications
  • Download the Audit Log
  • Data Planes
  • Search Your Domino Deployment
  • Domino CLI
  • Troubleshooting
  • Get Help
  • Send Feedback
domino logo
About Domino
Domino Data LabKnowledge BaseData Science BlogTraining
User Guide
>
Work with Data
>
Data Sources
>
Connect to Data Sources
>
Connect to MSSQL

Connect to MSSQL

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

Domino recommends that you use a Domino Data Source to connect to a MSSQL instance from Domino.

Create a MSSQL data source

  1. From the navigation pane, click Data.

  2. Click Create a Data Source.

  3. In the New Data Source window, from Select Data Store, select SQL Server.

    mssql newds

  4. Enter the Host. Valid values are <host string>:3306 or <host string>.

  5. Enter the Port.

  6. Enter the name of the Database.

  7. Enter the Data Source Name.

  8. Optional: Enter a Description to explain the purpose of the data source to others.

  9. Click Next.

  10. Enter the Username and Password to connect to SQL Server. The Domino secret store backed by HashiCorp Vault securely stores the credentials.

  11. Click Test Credentials.

  12. If the data source authenticates, click Next.

  13. Select who can view and use the data source in projects.

  14. Click Finish Setup.

Alternate way to connect to a MSSQL data source

Warning
  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.

RUN pip install pymssql
  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()

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.

    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
  3. See Microsoft’s documentation for the Dockerfile instructions to add the MSSQL drivers to your Domino-supported Ubuntu environment.

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

Domino Data LabKnowledge BaseData Science BlogTraining
Copyright © 2022 Domino Data Lab. All rights reserved.