Azure SQL Server and FreeTDS ODBC with linux and windows dev environments

Question

I host a web app on Windows Azure (Flask), which runs on Windows Server.

I have a linux development environment (some other contributors may use windows in the future), so we use pyodbc to communicate with the SQL Server. Unfortunately, I have to change my connection string every time I pull or push from the Azure deployment due to linux and windows ODBC connection differences.

Consider my connection string:

pyodbc.connect('Driver=SQL Server;Server=tcp:mydbname.database.windows.net,1433;Database=mydbname;Uid=dbuser@mydbname;Pwd=topsecret;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')

This works fine once it's deployed on the server (Windows Server), or on the windows box, but doesn't work on the linux dev box. I found some over SO questions and a guide on how to setup FreeTDS (e.g. Connecting to Microsoft SQL Server through pyODBC on Ubuntu and http://www.gazoakley.com/content/connecting-sql-azure-python-ubuntu-using-freetds-and-unixodbc, http://blog.tryolabs.com/2012/06/25/connecting-sql-server-database-python-under-ubuntu/).

However, after following those guides through you have to remove the server name and replace it with a the DSN (data source name), which is a locally configured variable. While that will work on a local linux box, that doesn't work once it's deployed to the Azure web server (windows).

pyodbc.connect('DSN=SQL Server;Driver=SQL Server;Database=mydbname;Uid=dbuser@mydbname;Pwd=topsecret;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')

You have to remove the servername otherwise it conflicts with the odbc/FreeTDS config.

With no DNS specified (first connection string in this post):

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)

With DNS and servername:

pyodbc.Error: ('HY000', '[HY000] [unixODBC][FreeTDS][SQL Server]Only one between SERVER, SERVERNAME and DSN can be specified (0) (SQLDriverConnect)')

With DNS and no server name

When deployed to the server it wont work.

We use git for version control, and I don't want to have continuous merge conflicts as developers change their connection string to match their dev enviroments and/or deploy to the Azure site.

Is there a clean fix for this. Gut tells me I'm doing something wrong, and this is very trivial.

In addition to the answer below, have you tried including "Driver=FreeTDS;TDS_Version=7.2" in your pyodbc.connect? Also, if you want to use the same DB driver everywhere, FreeTDS is available for Windows as well, if you're okay running over ODBC, which it seems you would be with pyodbc instead of pymssql. — FlipperPA
@FlipperPA why would I want to try setting the driver to FreeTDS. What does that buy me? Good to know I can install FreeTDS on the windows dev boxes, but wouldn't I also have to install FreeTDS on the azure web app aswell? EDIT: seems there is an MS approved way to install FreeTDS on the web app: azure.microsoft.com/en-us/documentation/articles/… — Prof
The ultimate "holy grail" here would be to have your development environment match production as closely as possible. While that isn't possible if you're already on Windows for development and Linux for production, you can get closer by using FreeTDS everywhere (same driver) and not having to use different connection strings in your code (avoiding the "if" statement during connection based on which OS). — FlipperPA

Gary Liu Gary Liu · Accepted Answer · 2015-08-10T03:46:53

First of all, if you have successfully connected to SQL Server both in Windows and Linux platforms, congratulations. In python, there is a package installed by python default named ‘platform’, we can use it to check the what platform python script run in. And we can set 2 connection string in advance, and select the specific string in different platform. Here is my python code snippet:

import platform

def getOBCDString():
    stsos = platform.system()
    bol = False
    odbcstring=''
    if(stsos == "Windows"):
       bol = True
       odbcstring = 'windows_odbc_string'
    elif(stsos == "Linux"):
        bol = True
        odbcstring = 'Linux_odbc_string'
    #else:
        # custom error handle
    return (bol,odbcstring)

bol,string = getOBCDString()
if(bol):
    print(string)

Azure SQL Server and FreeTDS ODBC with linux and windows dev environments

1 Answers