Menu

Subscribe

Django and Azure SQL Database on Ubuntu 14.04 LTS

on Python, Azure SQL Database, Linux, Django

By default, Django supports SQLite, MySQL, PostgreSQL and Oracle database engines. Thanks to ODBC, pydobc and django-pyodbc-azure we can use highly available Azure SQL Database with Django on Linux.
Using Azure SQL Database on Linux with ODBC is possible by Microsoft ODBC Driver for SQL Server for Linux - recently updated to version 13 (Preview):

With Microsoft ODBC Driver 13 (Preview) for SQL Server, SQL Server 2014 and SQL Server 2016 (Preview), are now also supported.

Always Encrypted

Support for the recently released Always Encrypted feature in SQL Server 2016 (Preview), a new security feature that ensures sensitive data is never seen in plaintext in a SQL Server instance. Always Encrypted works by transparently encrypting the data in the application, so that SQL Server will only handle the encrypted data and not plaintext values. Even if the SQL instance or the host machine is compromised, all an attacker can get is ciphertext of sensitive data.

Ubuntu Support

Ubuntu is now supported, along with Red Hat and SUSE.

unixODBC Driver Manager 2.3.1 Support

This guide is based on clean Ubuntu 14.04 LTS VM running on Azure and Django 1.9.

Upgrade gcc

New version of GCC is needed to run the Microsoft ODBC Driver so we need to make some updates for Ubuntu toolchain.

$ sudo add-apt-repository ppa:ubuntu-toolchain-r/test
$ sudo apt update
$ sudo apt upgrade

Install Driver Manager and SQL Driver

The next thing we need to install (or update if You have a version from Ubuntu repositories) is unixODBC Driver Manager. After that we are going to install the driver itself.

$ sudo su
# apt install build-essential libgss3
# cd /tmp 
# wget  https://download.microsoft.com/download/2/E/5/2E58F097-805C-4AB8-9FC6-71288AB4409D/msodbcsql-13.0.0.0.tar.gz
# tar xzvf msodbcsql-13.0.0.0.tar.gz
# cd cd msodbcsql-13.0.0.0
# ./build_dm.sh
# cd /tmp/unixODBC.24807.30156.17882/unixODBC-2.3.1; make install
# cd /tmp/msodbcsql-13.0.0.0
# ./install.sh install

Let's check if installation was successfull and what drivers do we have:

 # odbcinst -d -q

An ouptput should be as follows:

[ODBC Driver 13 for SQL Server]

Python venv

If You are familiar with Django, especially Django on Linux, there will be nothing new for You here. We need (or not, if we don't want to) Python virtual environment and new Django project.

$ sudo apt install python-pip python3-dev
$ sudo pip install virtualenv
$ mkdir myproject
$ cd myproject
$ virtualenv -p /usr/bin/python3 venv
$ source venv/bin/activate
$ pip install django pyodbc django-pyodbc-azure
$ django-admin startproject mydjango

Azure Database

Now create a new Azure SQL Database and get a connection string (You will find it in database's Properties blade in management portal). It will look like this:

Server=tcp:<ServerName>,<ServerPort>;Database=<DatabaseName>;Uid=<UserName>;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Remember to allow Azure Services to use this database.

Configure Django

Let's configure Django to use Azure SQL Database:

$ cd mydjango
$ vim mydjango/settings.py

Search and edit the definition of DATABASES to use the values from connection string. It should look like this:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': '<DatabaseName>',
        'USER': '<UserName>',
        'PASSWORD': '{your_password_here}',
        'HOST': '<ServerName>',
        'PORT': '<ServerPort>',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
            'MARS_Connection': 'True',
        }
    }
}

Save and proceed to DB migration.

Migrate Django DB

 $ python manage.py migrate

A proper output should look like this:

Operations to perform:
  Apply all migrations: contenttypes, admin, auth, sessions
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying Auth.0007_alter_validators_add_error_messages... OK
  Applying sessions.0001_initial... OK

But probably You will get some kind of error:

django.core.exceptions.ImproperlyConfigured: Error loading pyodbc module: libodbc.so.2: cannot open shared object file: No such file or directory

We need to make some symlinks for libs - newly installed odbc libs are not in libs PATH.

$ sudo ln -s /usr/lib64/libodbcinst.so.2 /lib/x86_64-linux-gnu/libodbcinst.so.2
$ sudo ln -s /usr/lib64/libodbc.so.2 /lib/x86_64-linux-gnu/libodbc.so.2

Then load new config and check if libs are loaded:

$ sudo ldconfig
$ ldconfig -p | grep libodbc

The output should look like this:

libodbcinst.so.2 (libc6,x86-64) => /lib/x86_64-linux-gnu/libodbcinst.so.2
libodbc.so.2 (libc6,x86-64) => /lib/x86_64-linux-gnu/libodbc.so.2

Create superuser

Let's create a Django superuser.

$ python manage.py createsuperuser

Run server

The last thing we need to do is to check if Django is up and running. Let's run it on IP 0.0.0.0 and port 8000 (it means that we are running Django server on all interfaces not on localhost like it is by default).

$ python manage.py runserver 0.0.0.0:8000

An output should look like this:

Performing system checks...

System check identified no issues (0 silenced).
July 14, 2016 - 21:25:55
Django version 1.9.7, using settings 'mydjango.settings'
Starting development server at http://0.0.0.0:8000/
Quit the server with CONTROL-C.

Remember to create an inbound rule for port 8000 in Azure Network Security Group.

Django should be available at http:// < vm public IP >

App servers and Web servers

Of course this is just development environment. Building it for production, You will probably use App Server - like gunicorn - and Web Servers - like NGINX.
You can also separate App from Web Frontend - just create App Servers inside vNET and without public IPs, create dedicated VMs for Web Servers behind Azure Load Balancer and configure Web Servers as reverse proxies for Your App Servers.
You can also make Django fully stateless with Azure Storage and automate deployments on any number of App Servers with Ansible.

comments powered by Disqus