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

The linked answer is completely wrong for the subject asked here. This is asking how to connect to SQL Server, a database, not how to run IIS on Windows connecting to PostgreSQL. This should be reopened. — FlipperPA
I have installed odbc driver @iklinac. I dont understand why this question is duplicate, the link shown definetly not answers my question. — shaswat kumar
what version of driver you have installed and what platform do you have, x64 or x86 for python and driver — iklinac
@iklinac version = 2.1.0.0 (pyodbc-azure) , my django version = 2.1.15, my python version=3.6.4 — shaswat kumar

FlipperPA FlipperPA · Accepted Answer · 2020-05-22T21:47:31

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!

Connecting Django to Microsoft SQL Database

2 Answers