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!
