Establishing connection to MS SQL Server 2014 with django-mssql-1.6

Question

i am aware that the django-mssql-1.6/README states:

SQL Server Versions

Supported Versions:

• 2008
• 2008r2
• 2012

but, seeing as v. 1.6 is the latest version available, i was wondering if anyone was able to find a way to connect to an MS SQL Server 2014. I am trying, but getting the error message:

django.db.utils.OperationalError: (com_error(-2147352567, 'Exception occurred.', (0, u'ADODB.Connection', u'Provider cannot be found. It may not be properly installed.', u'C:\Windows\HELP\ADO270.CHM', 1240655, -2146824582), None), u'Error opening connection: DATA SOURCE=127.0.0.1;Initial Catalog=testdb;Integrated Security=SSPI;PROVIDER=sqlncli10;DataTypeCompatibility=80;MARS Connection=True')

using config:

DATABASES = {
    'default': {
        'ENGINE': 'sqlserver_ado',
        'NAME': 'testdb'
    }
}

Answer 1

As far as I can see, you are using the correct versions of django-mssql and Django. I recently moved from 1.6 to 1.7 and had to change the DB backend since sql_server.pyodbc is no longer supported by django 1.7. I came across this issue when changing to django-mssql (sqlserver_ado). The problem is you are using the wrong provider. Django-mssql uses SQLCLI10 as the default provider, which also did not work for me. Adding an option hash to your DB config, like the one in the above answer, will solve your problem as long as you use the SQLOLEDB provider. This is my config:

    DATABASES = {
    'default': {
        'NAME': 'CVH_Dev',
        'ENGINE': 'sqlserver_ado',
        'HOST': '192.***.212.2**',
        'USER': 'USER',
        'PASSWORD': 'PWD',
        'OPTIONS': {
            'provider': 'SQLOLEDB',
            'use_legacy_date_fields': 'True'
        }
    }
}

Use the SQLOLEDB provider option and it will work. Hope this helps.

Answer 2

Maybe too late, but... If you're using Django 1.10 and you don't need to stick to django-mssql, you could switch to the django-pyodbc-azure package, which supports MS SQL Server 2014 and 2016 out of the box.

Just tried out, and it works, in this environment:

• Windows Server 2012 R2
• Python 3.5.2
• SQL Server 2016
• Django 1.10