Connecting Django to Microsoft SQL Database

Question

I want to connect my django application to MS-SQL server 2014 database. I wrote this code for making connections.

 DATABASES = {
'default': {
    'ENGINE': 'sql_server.pyodbc',
    'HOST':'DESKTOP-6UNRAN0',
    'PORT':'1433',
    'NAME': 'MOVIE',
    'COLLATION' : '',

  }
}

I have installed sql_server.pyodbc

 pip install django-pyodbc-azure

as mentioned in the documentation https://pypi.org/project/django-pyodbc-azure/. I am still getting error

django.db.utils.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Answer 1

I no longer recommend using django-pyodbc-azure, as it is no longer maintained by the author. The active PyPI project for SQL Server in Django is currently django-mssql-backend. However, it only supports Django 2.2 and above. I would highly recommend upgrading to Django 2.2 (a long term support release), if not Django 3.0. 2.1 is no longer supported, and this will save you headaches down the road for a little bit of work now. I'm going to assume you're on Linux.

Step One: Install Microsoft's Driver for Linux (You May Also Use FreeTDS)

If you want to use Microsoft's driver, you can install it like this:

sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
sudo ACCEPT_EULA=Y yum install msodbcsql17

Step Two: Create a Database and Service User in SQL Server

In SQL Server, set up a service user to your Django database. This script will create a user with the minimum permissions needed to the underlying database.

/*
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 Three: Configure Django

Finally, let's set up Django itself to point to SQL Server. In your Django project with your venv activated:

pip install django-mssql-backend

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

If you're using FreeTDS or another driver, change the OPTIONS line, 'driver': 'ODBC Driver 17 for SQL Server'. That should do it.

Good luck!

Answer 2

It is expected that you know if you want to connect to SQL Server you'll have to use/install ODBC as it is native data access API

Regarding documentation lets look into following lines

a Django Microsoft SQL Server external DB backend that uses ODBC by employing the pyodbc library

---

Compatible with Micosoft ODBC Driver for SQL Server, SQL Server Native Client, and FreeTDS ODBC drivers

---

OPTIONS

Dictionary. Current available keys are:

driver

String.

Server Native Client 11.0", "FreeTDS" etc). Default is "ODBC Driver 13 for SQL Server".