How to connect Django to MSSQL with Windows Authentication

Question

Similar to this question:

How to use windows authentication to connect to MS SQL server from windows workstation in another domain with Python

My current database configurations looks like this:

   'default': {
        'ENGINE': "sql_server.pyodbc",
        'HOST': "myServer\server1",
        'USER': "myUserName",
        'PASSWORD': "myPassWord",
        'NAME': "myDB"

However, I would like to pass through Windows Authentication so that I can keep track of who is making changes to the DB. I have Windows Authentication enabled through IIS, so users are prompted to login when they visit my app. I have tried this, but it did not work:

   'default': {
        'ENGINE': "sql_server.pyodbc",
        'HOST': "myServer\server1",
        'trusted_connection': 'yes'
        'NAME': "myDB"

Is there a way to pass through windows authentication to the Django database settings?

Ovoid

Have you seen this? #26881844 – Arrears

Answer 1

I am not sure that you can use your client's window username as the authenticator to a trusted connection to SQL Server.

If you set your Web App to using AD authentication(trusted connection) like Windows Authentication, your Web App will use the windows account that is hosting your Web App on your server to connect to your SQL Server.

To accomplish tracking what user made what edits to a table in SQL Server... If you are tracking who is making edits to tableA:

• Create an additional column to tableA, and call it UpdatedBy that stores nvarchar(255) datatype
• Whenever your Web App make changes to a record in tableA, make sure to update [UpdatedBy] for the same record, with your client's window username

Digitalis

Answer 2

Check out this module https://pypi.org/project/django-windowsauth/

You can use this module to integrate Windows Authentication into your Django Project through IIS.

Then, you can add a create_by / updated_by field to any model you desire.

from django.contrib.auth.models import get_user_model()
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=200)
    created_by = models.ForeignKey(get_user_model(), on_delete=models.CASCADE)

    # ...

Using the user from the request context, you can set the field like this:

from django.contrib.auth.mixins import LoginRequiredMixin
from django.views.generic.edit import CreateView
from myapp.models import Author

class AuthorCreate(LoginRequiredMixin, CreateView):
    model = Author
    fields = ['name']

    def form_valid(self, form):
        form.instance.created_by = self.request.user
        return super().form_valid(form)

https://docs.djangoproject.com/en/3.1/topics/class-based-views/generic-editing/#models-and-request-user

or using DRF like this:

class YourView(generics.ListCreateAPIView):
    ...
    def perform_create(self, serializer):
        serializer.save(user=self.request.user)

How to set current user to user field in Django Rest Framework?

Same applies to update_by field, though I suggest you take a look at this great module https://pypi.org/project/django-simple-history/

Selfimprovement

Answer 3

We can connect Django to MSSQL(SQL Server) with Windows Authentication using mssql-django. *I use SQL Server 2019 Express.

First, set the code below to "settings.py" to connect Django to MSSQL with Windows Authentication. This code below is the example of Django and MSSQL in the same Windows Computer(localhost) and "ENGINE" must be "mssql" and "NAME" is for Database name "test" and "DESKTOP-QVRCPTA" for "HOST" is Windows Computer Name(Device name). *Keep it blank for "PORT" because there will be error if setting any port number e.g. "2244", "9877" or even "1433" which is the default port number of MSSQL:

# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'mssql',                    # Must be "mssql"
        'NAME':'test',                       # DB name "test"
        'HOST':'DESKTOP-QVRCPTA\SQLEXPRESS', # <server>\<instance>
        'PORT':'',                           
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}

In addition, "DESKTOP-QVRCPTA" can be replaced with "localhost" and "USER" and "PASSWORD" can be put with empty string and "PORT" can be removed as shown below to connect Django to MSSQL with Windows Authentication:

# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'mssql',
        'NAME':'test',
        'USER':'',                     # Here
        'PASSWORD':'',                 # Here
        'HOST':'localhost\SQLEXPRESS', # Here
        # 'PORT':'',                   # Here      
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}

Next, install the latest package mssql-django:

pip install mssql-django

Then, make migrations and migrate:

python manage.py makemigrations && python manage.py migrate

Then, create superuser:

python manage.py createsuperuser

Now, we could connect Django to MSSQL with Windows Authentication, then create the tables for "test" database. *the default schema "dbo" is put before the table names as shown below:

And, this is Django Admin:

Plassey