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'
    }
}

score 8 · Accepted Answer · answered Sep 30 '15 at 10:38

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.

`'OPTIONS': { 'provider': 'SQLOLEDB', 'use_legacy_date_fields': 'True' }` really worked ! Thanks! — AmiNadimi, Jul 01 '17 at 05:35

score 4 · Answer 2 · answered Nov 02 '16 at 11:32

4

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

answered Nov 02 '16 at 11:32

Paolo Stefan

10,112
5
45
64

Also works with Python 2.7, but I still needed to add `'OPTIONS': { 'provider': 'SQLOLEDB' }` – Dinei Feb 16 '17 at 13:36

score 2 · Answer 3 · answered Apr 14 '15 at 15:48

Please ensure that you are using Django 1.6 and django-mssql is 1.6 version. I've noticed that django-mssql 1.6 does not work with the latest Django 1.8.

Here are my DB config settings which has worked for me -

  DATABASES = {
      'default': {
          'ENGINE': 'sqlserver_ado',
          'NAME': 'dbname',                      # Or path to database file       if using sqlite3.
          # The following settings are not used with sqlite3:
          'USER': 'db_user_id',
          'PASSWORD': 'db_password',
          'HOST': 'host_name_or_ip_addr',                      # Empty for       localhost through domain sockets or '127.0.0.1' for localhost through TCP.
          'PORT': '1433',                      # Set to empty string for       default.
          'OPTIONS': {
                'provider' : 'SQLOLEDB'             # or these other two dlls did not work for me SQLCLI10, SQLCLI11
          },
      },
  }

score 2 · Answer 4 · answered May 17 '17 at 04:45

Solved for me using 'SQLNCLI11' provider:

DATABASES = {
    'default': {
        'NAME': 'MyDatabase',
        'ENGINE': 'sqlserver_ado',
        'HOST': '.\\SQLEXPRESS',
        'USER': '',
        'PASSWORD': '',
        'OPTIONS': {
            'provider': 'SQLNCLI11',
            'use_legacy_date_fields': 'True'
        }
    }
}

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

4 Answers4

Linked