Connect to SQL Server from Linux with ODBC

In a perfect database administrator world, everything would be the same. All servers would be identical and run the same workloads. They’re easier to manage. But, that’s not the case. Nowadays, System administrators have to manage different environments. This couldn’t be more evident in need to perform SQL queries from a Linux machine. In this post, you’ll learn how to connect to SQL Server from Linux!

One way to connect to SQL Server from Linux is to use a Python module. But before you get that far, let’s first cover what environment I’m working with.

In this article, I’ll be demonstrating this task using Ubuntu 16.04 and I’ll be connecting to SQL Server 2012 R2. But, the same technique should apply to other Linux flavors and SQL Server versions as well. Database administrators rejoice!

Prerequisites

To get started, you’re going to need to install a few prerequisites. First, since you’ll be connecting to a SQL Server instance from Python, you’ll need a Python module. A common Python module to connect to SQL is called PyODBC. This module allows you to query SQL databases via ODBC via a SQL Server ODBC driver for Linux. To get the latest version installed, use pip (the Python package manager).

> sudo pip install pyodbc

If this does not work, you might not have pip installed. To install pip:

> sudo easy_install pip

Next, you need to create a Python script. I’m going to call this one sql_server.py. To create a Python script, first create a blank file.

> touch sql_server.py

Then, using your editor of choice, add the below lines. The shebang followed by the path to the Python binary tells the interpreter this is a Python script. The import statement then allows you to call the library methods inside of the pyodbc module. Save this script.

!/usr/bin/python
import pyodbc

Once you have the Python script created, run the script:

> python sql_server.py

If this runs without error, the pyodbc module has been installed successfully.

Next, add the code to execute a test query. To do this, create an ODBC string.

To learn more about crafting ODBC strings, here‘s a good resource.

The ODBC string is picky about what’s included. It took some time to figure out how to make this work but here’s what mine looks like. Below I’m passing the ODBC string as an argument to the connect() method that’s included with the pyodbc module.

conn = pyodbc.connect('DRIVER={FreeTDS};SERVER=server.domain.local;PORT=1433;UID=DOMAIN\user;PWD=mypassword;DATABASE=mydatabasename;UseNTLMv2=yes;TDS_Version=8.0;Trusted_Domain=domain.local;')

Most of the ODBC string is evident, but one important fact is the double backslash for the UID. Always ensure that you’ve escaped any backslashes in the ODBC string. Also, some of the options I’m using are optional.

Also, you can either use the hostname for SERVER as I’ve done above or you can use the SQL Server IP address.

Feel free to add or remove them as you see fit to match your SQL Server.

Next, you need to create a cursor object that will allow you to pass a T-SQL statement to. This is done with the cursor() method.

cursor = conn.cursor()

Now you have an object with an execute() method that can be used to pass any T-SQL statement we’d like into as shown below. This creates a rows variable containing the resulting dataset.

cursor.execute("SELECT * FROM <tablename>")
rows = cursor.fetchall()

You’re now to the point where you’ll need to decide what to do with the dataset. You can send the results to a CSV file, put the results into another database or write the contents to the console.

Below, I’m printing the results to the console if the dataset is populated.

if rows:
    print(rows)

You can see that if the dataset is printed out to the console, the output isn’t too pretty. At this point, it’s up to you to decide how to format or parse the data from the database. The hard part is over!

You’ll now end up with a script that looks like this:

!/usr/bin/python
import pyodbc

conn = pyodbc.connect('DRIVER={FreeTDS};SERVER=server.domain.local;PORT=1433;UID=DOMAIN\user;PWD=mypassword;DATABASE=mydatabasename;UseNTLMv2=yes;TDS_Version=8.0;Trusted_Domain=domain.local;')

cursor.execute("SELECT * FROM <tablename>")
rows = cursor.fetchall()

if rows:
    print(rows)

Summary

In this blog post, you learned how to use the pyodbc Python module on Linux to connect to a SQL Server data source. The hardest part for me was figuring out the ODBC string but once you get that covered, you should be smooth sailing.

Once you’re connecting from Linux, why not see if you can up the performance using performance counters?