Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment

October 03, 2018
7 min read
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
I. The challenge
Recently, I received the task to run an application locally using the Django framework (the version used at that time was version 2.0.7) in combination with Python 3.6.5. The database I used for this runs on MSSQL server.
Opting for an MSSQL database was necessary for this project since an external app, also running on MSSQL, populates databases with data containing objects.
II. Problems and solutions to problems
In order to optimize the app in terms of speed and project size, I opted for django-mssql. After checking the documentation, one of the problems I noticed was that the package only supports Django 1.8 and I needed to use version 2.0.7 to my project.
Installing MSSQL server on the operating system was problematic due to setup versioning related to Ubuntu 18. Ubuntu 16.04 is the most recent version of Ubuntu that is currently supported.
In order to solve the first problem, we found django-pyodbc-azure, which is “a modern fork of django-pyodbc, a Django Microsoft SQL Server external DB backend that uses ODBC by employing the pyodbc library”. It supports Django 2.0.x and most importantly, it also supports Microsoft SQL Server.
To solve the second problem, we adopted the use of Docker technology so we could create a container based on the microsoft/mssql-server-linux, image provided by Microsoft.
II.1 Docker settings

If you want to install Docker on your Linux system, please check out this link and if you want to find out more about Docker, please go to the official page. In addition, you need to install Docker Compose and this can be done by following this tutorial. After you have installed Docker and Docker Compose on your system, you have to download the image you want and create a new container based on that image.

Inside your Django app, create a new folder called docker_config. In this folder, create a new file called docker-compose.yml with the content below. I prefer keeping all of the Docker settings together in the docker_config folder, but this is not compulsory.

version: '3'

services:
  db:
    image: microsoft/mssql-server-linux
    environment:
      SA_PASSWORD: StrongPass123
      ACCEPT_EULA: Y
      MSSQL_PID: Developer
    ports:
      - "1433:1433"
In the docker-compose.yml file, you must specify what image to use inside the container and add some variables (such as password and port) in order to set MSSQL inside the container The image I used is microsoft/mssql-server-linux. For more information about what can be configured for this image, please check this article.
In order to see all of the Docker images, run docker images. A list with all of the images will be shown. In the example below, the list is empty.

After creating the docker-compose.yml file, run the command below in order to create the container.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

 docker-compose up -d

You will see that the image will be downloaded.


 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

Now, if you run docker images, you will see that microsoft/mssql-server-linux has been downloaded.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

Furthermore, if you type docker ps, you will see a list of containers. In order to check if your container is running, type docker ps -a.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

If the container is not running, docker start <container_id> will start the container.
In the example below, <container_id> is 9f2f18715afe.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

If you want to stop running a specific container, you can use:


docker stop <container_id>.

In order to run some commands inside a container, run the following command. If everything is ok, you will be inside the container bash.


docker exec -i -t <container_id> /bin/bash

You can access MSSQL server by running:


 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa

The default username for MSSQL server is sa. After you run this command, type the password written in the docker-compose.yml file. If everything is ok, you will be able to make interrogations inside mssql.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

Now that you have configured the Docker container, you can check the connection from the outside of the container. This can be done by running the following script written in Python.

import pyodbc
    server = '127.0.0.1'
    username = 'sa'
    password = 'StrongPass123'
    PORT = '1433'

    cnxn = pyodbc.connect(
        'DRIVER={FreeTDS};SERVER=' +
        server + ';PORT=' + PORT + ';UID=' + username + ';PWD=' + password)
    cursor = cnxn.cursor()

    print ('Using the following SQL Server version:')
    tsql = "SELECT @@version;"
    with cursor.execute(tsql):
        row = cursor.fetchone()
        print (str(row[0]))
The results:
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
This error will occur if you don’t have FreeTDS installed on your system.
II.2 FreeTDS settings
“FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.” 
I had some trouble understanding why it was necessary to install this package and how to install and configure it. The first time I installed a version of FreeTDS, older than version 7.3 (version 4.2, to be more specific) and  I received the following error:
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
This occurred because django-pyodbc-azure 2.0 requires at least TDS protocol 7.3 (or later versions) because it doesn't support the legacy datetime type anymore. In order to remove FreeTDS version 4.2, I ran the following command:

sudo apt-get autoremove freetds-dev freetds-bi

In order to install the latest version of FreeTDS, run the following commands:


sudo apt-get install wget
sudo apt-get install build-essential
sudo apt-get install libc6-dev

To find the latest version of FreeTDS, check this link. Then run the following command by changing the version of FreeTDS (at the time of writing this article, the latest version was freetds-1.00.92).

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.00.92.tar.gz
tar -xzf freetds-1.00.92.tar.gz
cd freetds-1.00.92
./configure --prefix=/usr/local --with-tdsver=7.3
sudo make
sudo make install

After FreeTDS is installed, it must be configured. In the /etc/ folder, add or update the following files with the content shown below.


odbcinst.ini

[FreeTDS]
Description = v0.91 with protocol v7.3
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

odbci.ini

[dbserverdsn]
Driver = FreeTDS
Server = 127.0.0.1
Port = 1433
TDS_Version = 7.3
freetds/freetds.conf

[global]
    # TDS protocol version, use:
    # 7.3 for SQL Server 2008 or greater
    # 7.2 for SQL Server 2005
    # 7.1 for SQL Server 2000
    # 7.0 for SQL Server 7
    tds version = 7.3
    port = 1433

;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    # If you get out-of-memory errors, it may mean that your client
    # is trying to allocate a huge buffer for a TEXT field.
    # Try setting 'text size' to a more reasonable limit
    text size = 64512

    # A typical Microsoft server
    [dbserverdsn]
        host = 127.0.0.1
        port = 1433
        tds version = 7.3

If you want to check the FreeTDS version, type tsql -C and you will receive an output like the one shown below.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

If you try to run the script again, you will receive the following output.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

You can access the MSSQL from your container by typing:


tsql -S 127.0.0.1 -U sa
II.3 Configure your Django app with MSSQL

In order to configure your Django app to run on MSSQL server, you must have the following packages installed:


django-pyodbc-azure==2.0.4.1
pyodbc==4.0.23

and you must update the settings or local_settings file as shown below.

DB_ENGINE = 'sql_server.pyodbc'
DB_NAME = 'django_mssql_test'
DB_USER = 'sa'
DB_PASSWD = 'StrongPass123'
HOST = '127.0.0.1'
PORT = '1433'
DB_OPTIONS = {
    'driver': 'FreeTDS',
    'unicode_results': True,
    'host_is_server': True,
    'extra_params': 'tds_version=7.3;',
}

DATABASES = {
    'default': {
        'ENGINE': DB_ENGINE,
        'NAME': DB_NAME,
        'USER': DB_USER,
        'PASSWORD': DB_PASSWD,
        'HOST': HOST,
        'PORT': PORT,
        'OPTIONS': DB_OPTIONS
    }
}

After running the migrations, you will see that all of the tables will be created in the MSSQL database from the container. 

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
III. Conclusion

Most of the time, updating packages or operating systems to the latest version can create compatibility issues. This becomes a problem when you work on several projects. With the help of Docker technology and the package django-pyodbc-azure, you will be able to connect your Django application to MSSQL database locally using the latest version of Ubuntu (Ubuntu 18), even though MSSQL is not officially supported yet.

Share on:

Want to stay on top of everything?

Get updates on industry developments and the software solutions we can now create for a smooth digital transformation.

* I read and understood the ASSIST Software website's terms of use and privacy policy.

Frequently Asked Questions

1. What is ASSIST Software's development process?  

The Software Development Life Cycle (SDLC) we employ defines the following stages for a software project. Our SDLC phases include planning, requirement gathering, product design, development, testing, deployment, and maintenance.

2. What software development methodology does ASSIST Software use?  

ASSIST Software primarily leverages Agile principles for flexibility and adaptability. This means we break down projects into smaller, manageable sprints, allowing continuous feedback and iteration throughout the development cycle. We also incorporate elements from other methodologies to increase efficiency as needed. For example, we use Scrum for project roles and collaboration, and Kanban boards to see workflow and manage tasks. As per the Waterfall approach, we emphasize precise planning and documentation during the initial stages.

3. I'm considering a custom application. Should I focus on a desktop, mobile or web app?  

We can offer software consultancy services to determine the type of software you need based on your specific requirements. Please explore what type of app development would suit your custom build product.   

  • A web application runs on a web browser and is accessible from any device with an internet connection. (e.g., online store, social media platform)   
  • Mobile app developers design applications mainly for smartphones and tablets, such as games and productivity tools. However, they can be extended to other devices, such as smartwatches.    
  • Desktop applications are installed directly on a computer (e.g., photo editing software, word processors).   
  • Enterprise software manages complex business functions within an organization (e.g., Customer Relationship Management (CRM), Enterprise Resource Planning (ERP)).

4. My software product is complex. Are you familiar with the Scaled Agile methodology?

We have been in the software engineering industry for 30 years. During this time, we have worked on bespoke software that needed creative thinking, innovation, and customized solutions. 

Scaled Agile refers to frameworks and practices that help large organizations adopt Agile methodologies. Traditional Agile is designed for small, self-organizing teams. Scaled Agile addresses the challenges of implementing Agile across multiple teams working on complex projects.  

SAFe provides a structured approach for aligning teams, coordinating work, and delivering value at scale. It focuses on collaboration, communication, and continuous delivery for optimal custom software development services. 

5. How do I choose the best collaboration model with ASSIST Software?  

We offer flexible models. Think about your project and see which models would be right for you.   

  • Dedicated Team: Ideal for complex, long-term projects requiring high continuity and collaboration.   
  • Team Augmentation: Perfect for short-term projects or existing teams needing additional expertise.   
  • Project-Based Model: Best for well-defined projects with clear deliverables and a fixed budget.   

Contact us to discuss the advantages and disadvantages of each model. 

ASSIST Software Team Members

See the past, present and future of tech through the eyes of an experienced Romanian custom software company. The ASSIST Insider newsletter highlights your path to digital transformation.

* I read and understood the ASSIST Software website's terms of use and privacy policy.

Follow us

© 2025 ASSIST Software. All rights reserved. Designed with love.