Cross-platform development with ODBC and Microsoft ODBC Driver for SQL Server

In my last post I described how to configure Django to use Azure SQL Database via ODBC if You are deploying Django on Ubuntu 14.04 LTS. Working with Django deployment with Azure SQL Database there is a chance that You will encounter one annoying issue. If You are developing Your app on Windows and deploying it to Ubuntu or developing on Linux and deploying it to Windows (and Azure Web Apps), You need to use two versions of ODBC Driver in Django configuration.

If You are running Django (locally or remotely) on Linux, You need to use ODBC Driver 13 for SQL Server:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': '<DatabaseName>',
        'USER': '<UserName>',
        'PASSWORD': '{your_password_here}',
        'HOST': '<ServerName>',
        'PORT': '<ServerPort>',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
            'MARS_Connection': 'True',
        }
    }
}

But if You are running Django (locally or remotely) on Windows, You need to use SQL Server Native Client 11.0:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': '<DatabaseName>',
        'USER': '<UserName>',
        'PASSWORD': '{your_password_here}',
        'HOST': '<ServerName>',
        'PORT': '<ServerPort>',
        'OPTIONS': {
            'driver': 'SQL Server Native Client 11.0',
            'MARS_Connection': 'True',
        }
    }
}

Of course there is no problem when You are developing on Windows and deploying on Windows (and Azure Web Apps) or developing on Linux and deploying to Linux.

But in my case... it's annoying. I'm developing on Linux Mint (Ubuntu) and want to deploy my code to Azure Web Apps (Windows). So during development, running local Django instance and during database migration I need to have ODBC Driver 13 for SQL Server in Django config and before every git push I need to change one line in Django config to use SQL Server Native Client 11.0 or my deployment will fail.

Dirty hack Solution

By default Linux ODBC Driver is configured as "ODBC Driver 13 for SQL Server" - and fortunately it's only a naming issue. The driver name is stored in one of the ODBC's configuration files: /etc/odbcinst.ini. It looks like this:

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

And yes... only thing we need to do is to rename the driver's definition name:

[SQL Server Native Client 11.0]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0
Threading=1
UsageCount=1

And yes... it works. The same code is working on Linux with ODBC Driver 13 for SQL Server and on Windows with SQL Server Native Client 11.0. The bad thing is that information in our Linux configuration is not true.

Of course this solution will work with any other framework or module that use ODBC driver, like SQLAlchemy:

db_odbc = 'DRIVER={{SQL Server Native Client 11.0}};SERVER=tcp:{}.database.windows.net;PORT=1433;DATABASE={};UID={};PWD={};MARS_Connection=yes;'.format(db_server, db_name, db_user, db_pass)


engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(
urllib.parse.quote_plus(db_odbc), pool_recycle=3600)

)

Remember that if You are developing on Linux and deploying to Windows (Azure Web Apps) You need to have driver's name changed locally. If You are developing on Windows and deploying to Linux, You must have driver's name changed on your server.