How do I install ODBC drivers on Amazon SageMaker so that I can connect to on-premises Microsoft SQL Server databases?

Last updated: 2020-03-18

I want to connect an Amazon SageMaker notebook instance to an on-premises Microsoft SQL Server database. How do I install the ODBC drivers that I need for the connection?

Short Description

Amazon SageMaker notebook instances come with Anaconda packages already installed. However, the drivers that are required to connect to on-premises databases aren't included. To connect to on-premises Microsoft SQL Server databases, install the driver manually.

Resolution

1.    Open a terminal window on your Amazon SageMaker notebook instance and then run the following commands to install the ODBC driver.

Note: This step requires root access. Root access is enabled by default when you create an Amazon SageMaker notebook instance. If you disabled root access, stop the notebook instance and enable root access before continuing. For more information, see Control Root Access to a Notebook Instance.

$ sudo su
$ curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
$ exit
$ sudo yum update
$ sudo yum remove unixODBC #to avoid conflicts
$ sudo ACCEPT_EULA=Y yum install msodbcsql-13.0.1.0-1 mssql-tools-14.0.2.0-1
$ sudo yum install unixODBC-utf16-devel

2.    When prompted, enter y to confirm the installation request.

3.    Run the following commands to confirm that the ODBC driver is installed:

$ odbcinst -q -d -n

[ODBC Driver 13 for SQL Server]

$ odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
UsageCount=1

4.    To test the connection to your on-premises Microsoft SQL Server database, open your Jupyter notebook and then run the following command:

import pyodbc
conn = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
'Server=name_of_server;'
'Database=db_name;'
)

Did this article help you?

Anything we could improve?


Need more help?