SQL Server driver issue on notebook instance running on AWS SageMaker

Question

I am using aws sagemaker notebook instance to run jupyter notebook. I have a` MS SQL Server DB 2019` db that I am trying to connect to from the notebook. Notebook instance is running on Amazon Linux 2, Jupyter Lab 1 platform.

import pandas as pd
    import numpy as np
    from datetime import datetime
    import json
    import os

# sql database
    import pyodbc
    connection = pyodbc.connect(
                                  'Driver={SQL Server};'
                                  'Server=sname;'
                                  'Database=dbname;'
                                  'Trusted_Connection=True;'
                               )
    
    cursor = connection.cursor()

I get an error, likely because the driver is not installed on the instance.

Error                                     Traceback (most recent call last)
    /tmp/ipykernel_20407/3026941781.py in ()
         10 # sql database
         11 import pyodbc
    ---> 12 connection = pyodbc.connect(
         13                               'Driver={SQL Server};'
         14                               'Server=sname;'
    
    Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

how do I install the driver on sagemaker instance and resolve this issue?

Answer

You're correct in assuming that the problem is due to the driver not being installed.
Microsoft have recently published a useful guide to installing various versions of the SQL Server drivers on different editions of Linux which should help you to get the driver installed: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16

Once you've done that, you also need to check that you have connectivity to your SQL Server instance from your EC2 instance. You can check basic network connectivity with a ping or traceroute, but you also need to check that you can establish a connection between the two. Personally, I'd start by installing the command line tools - again, Microsoft publish a good tutorial for installing them at this link: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver16

If you can't connect to your SQL Server by name, try instead to use the IP address as the server name. This will work if it's a "base" instance of SQL Server, I think you can use it with a secondary instance too, by using something like 192.168.0.1/instancename (subsitute the IP address of the SQL Server).

Once you've done that, you just need to sort out authentication. You don't say if you've got SQL Server installed on Linux or Windows, but these two articles should help you to fix any LDAP/Kerberos configuration issues that you might have in getting the two to talk to each other:

AD Authentication with SQL Server & Linux:
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver15

This may also be of use, which describes how to join a SQL Server that's running on a Linux box to AD. Worth a read to understand the mechanism, but I think the previous link is what you need:
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-join-domain?view=sql-server-ver15

Alternatively, you could (temporarily) bypass that step, which can be tricky to the uninitiated, and simply use SQL Server authentication instead, by creating a SQL Server login/password and corresponding user in the database you're trying to access. This isn't as secure as using Kerberos authentication, but it may suffice for your needs, or be useful to get you to connect your notebook to SageMaker temporarily to prove basic connectivity.

Please vote up my answer if it's useful to you, hope that helps.
Jon.

SQL Server driver issue on notebook instance running on AWS SageMaker

Relevant content