How to configure the Database setting Django-MSSQL using django-pyodbc (ubuntu 16.04)?

Question

I'm new to Django and currently trying to use another database to save my model (i.e. MS SQL). My database is deployed in a docker container:

903876e64b67        microsoft/mssql-server-linux   "/bin/sh -c /opt/mssq"   5 hours ago         Up 5 hours          0.0.0.0:8888->1433/tcp             nauseous_williams

I also create a new user for my login to the SQL Server.

Username='kucing', password='xxxxx'

With my user, I can use sqlcmd to access my DB as below:

sqlcmd -S localhost,8888 -U kucing -P 'xxxxx'

Therefore, I change my Django setting for my DB as shown here:

DATABASES = {
    'default': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'videogame', #The DB name
    'USER': 'kucing',
    'PASSWORD': 'xxxxx',
    'HOST': 'localhost',
    'PORT': '8888',

    'OPTIONS': {
        'driver': 'ODBC Driver 13 for SQL Server',
    },
},

However when I run python manage.py migrate, I get an error related to authentication:

Traceback (most recent call last):
File "/home/luca/git/learnPython/DjangoTicTacToe/lib/python3.5/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection
  self.connect()
File "/home/luca/git/learnPython/DjangoTicTacToe/lib/python3.5/site-packages/django/db/backends/base/base.py", line 171, in connect
  self.connection = self.get_new_connection(conn_params)
File "/home/luca/git/learnPython/DjangoTicTacToe/lib/python3.5/site-packages/sql_server/pyodbc/base.py", line 302, in get_new_connection
  timeout=timeout)
  pyodbc.Error: ('28000', "[28000] [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'kucing'. (18456) (SQLDriverConnect)")

Did I wrongly set up my configuration? Should I update my setting?

Answer 1

I've manage to figure out the issue. Because, I'm running both the Django application and the MS SQL server in linux, I need to change my driver to FreeTDS. This link is useful: How to install freetds in Linux?

After I finish installing the FreeTDS driver on my host (Ubuntu), I updated the Databases setting as follow:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'videogame',
        'USER': 'sa',
        'PASSWORD': 'xxxxx',
        'HOST': 'localhost',
        'PORT': '8888',
        'OPTIONS' : {
            'driver': 'FreeTDS',
            'unicode_results': True,
            'host_is_server': True,
            'extra_params': 'tds_version=7.0;',
            }
    }
}

Then I create a superuser using this command:

python manage.py createsuperuser

And Lastly, I do the Database migration:

python manage.py makemigrations; python manage.py migrate

Answer 2

I assume you're hosting on Windows, since you're attempting to connect to localhost. I'd highly recommend using the django-pyodbc-azure engine (https://github.com/michiya/django-pyodbc-azure), as it can be used for both local SQL Server or on Azure, and is the best maintained Django SQL Server package I've seen over several years of use. To install:

pip install django-pyodbc-azure

This will also install the pyodbc dependency. Since you're running Django on Windows (otherwise, you wouldn't be connecting to localhost), you can use the native client. Here's what I'd start with for your settings:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'videogame',
        'USER': 'kucing',
        'PASSWORD': 'xxxxx',
        'HOST': 'localhost',
        'PORT': '8888',

        'OPTIONS': {
            'driver': 'SQL Server Native Client 13.0',
        },
    },
}

Also, you should never post your password on Stack Overflow! I would highly recommend changing it. Typically, if you post your password as xxxxx or something like that, people will understand why. Good luck!