Using FreeTDS with Django

Question

I'm banging my head against a wall and am hoping to at least find some direction here... I've built an app in Django that depends on a MSSQL database housed on Azure. I've been able to deploy my app successfully on Heroku, but am unable to access the SQL database because Heroku doesn't support MSSQL database drivers. I've read it's possible to access the database by using a combination of FreeTDS and UnixODBC, but can't find any documentation explaining how to do so. Does anybody have any familiarity with either FreeTDS or UnixODBC that could at least point me in the right direction?

Thanks in advance.

Answer 1

Connecting Django to SQL Server is always a bit of a moving target with regards to best practices. I was a long time user of the FreeTDS driver, which along with pyodbc and django-pyodbc-azure provided the stack necessary to run Django with SQL Server. However, Microsoft's drivers have come a long way for Linux, and I have switched to using them. Here are instructions on getting connected:

Step 1: Install the Microsoft Linux ODBC Driver

You can also use FreeTDS, but at the time of this writing, I recommend using the Microsoft driver; instructions to install are here: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Step 2: Verify the Installation

If everything worked, you should be able to verify the installation by viewing /etc/odbcinst.ini; it should have a section that looks something like this.

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

Take note of the section header of the file: ODBC Driver 17 for SQL Server. We will be using this later to reference the driver.

Step 3: Setting Up a Service User in SQL Server

For best security practices, I recommend setting up an individual user for each Django project you have. The following script will create a DATABASE, LOGIN, USER and PASSWORD in SQL Server with the minimum permissions needed for Django to run.

/*
This Script Creates a SQL Server Database, Login and User
With Appropriate Permissions for a Production Django Project
with migrations. Simply fill out the variables below (@db_name and @db_password)
Username will be set to database name + '_user' by default.
*/
DECLARE @db_name VARCHAR(MAX) = 'project'
DECLARE @db_password VARCHAR(MAX) = 'project_password'
DECLARE @db_user VARCHAR(MAX) = @db_name + '_user'
--
--
USE master
DECLARE @cmd VARCHAR(MAX)
-- Server scope: create SQL Server login and permissions
SET @cmd = 'CREATE LOGIN ' + @db_user + ' WITH PASSWORD = ''' + @db_password + ''''
EXEC(@cmd)
SET @cmd = 'GRANT VIEW SERVER STATE TO ' + @db_user
EXEC(@cmd)
SET @cmd = 'CREATE DATABASE [' + @db_name + ']'
EXEC(@cmd)
-- DB scope: create user for server login and permissions
SET @cmd = 'USE [' + @db_name + '];'
SET @cmd = @cmd + 'CREATE USER ' + @db_user + ' FOR LOGIN ' + @db_user + ';'
SET @cmd = @cmd + 'GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE TABLE, REFERENCES, EXEC TO ' + @db_user
EXEC(@cmd)

Step 4: Setting up Django

Fire up your virtualenv for your Django project. We're going to install django-pyodbc-azure (which will install pyodbc as well).

pip install django-pyodbc-azure

Then, we modify our Django settings.

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': 'dbserver.your-domain.com',
        'PORT': '1433',
        'NAME': 'project',
        'USER': 'project_user',
        'PASSWORD': 'project_password',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'unicode_results': True,
        },
    },
}

Step 5: Run Initial Migrations

If everything has worked, you should now be able to run your initial migrations, and see Django's initial tables in your SQL Server database. You may also want to see the post here, which has some Heroku specific instructions: sql.h not found when installing PyODBC on Heroku

Good luck!