Connect to MSSQL database in Django using Tokens

One great way to connect to a MSSQL database for development or production in Django is to use your Microsoft Entra / Azure Active Directory identity. This is supported by the MSSQL database backend for Django.

This means, you can add users or groups from Microsoft Entra to your database users and assign them permissions. They can then login using this identity instead of a regular database user.

Create the user or group in the database

First, you will have to create the user or group in the database (and optionally assign a default schema) via:

CREATE USER [your-entra-group] FROM EXTERNAL PROVIDER;
ALTER USER [your-entra-group] WITH DEFAULT_SCHEMA=[dbo]

This makes your database recognize the user or group and ensures they can log in.

Make sure you can acquire a token

Now, make sure you are either logged in through Azure CLI or in Visual Studio Code (if you are using this editor) with your Microsoft identity that you also want to use for the authentication with the database.

Change your Django config

Then, you have to update your database config so that it uses the token method to authenticate.

First, you have to install the azure-identity package (add it to your requirements.txt / pyproject.toml / your package management). Then, add this to your settings.py before the database configuration:

import time

from azure.core.credentials import AccessToken
from azure.core.exceptions import ClientAuthenticationError
from azure.identity import DefaultAzureCredential

class DatabaseToken(str):
    cached_token: AccessToken | None = None

    def __new__(cls) -> None:
        instance = super().__new__(cls, "")
        instance.get_access_token()
        return instance

    def token_is_valid(self) -> bool:
        if self.cached_token is None:
            return False

        return self.cached_token.expires_on > time.time()

    def get_new_token(self) -> AccessToken:
        try:
            return DefaultAzureCredential().get_token("https://database.windows.net/.default")
        except ClientAuthenticationError:
            pass

        return DefaultAzureCredential(exclude_shared_token_cache_credential=True).get_token(
            "https://database.windows.net/.default"
        )

    def get_access_token(self) -> AccessToken:
        if self.token_is_valid():
            return self.cached_token

        self.cached_token = self.get_new_token()
        return self.cached_token

    def encode(self, *args, **kwargs) -> bytes:
        return self.get_access_token().token.encode(*args, **kwargs)

Finally, you have to update your database configuration in settings.py:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "YOUR_DATABASE",
        "TOKEN": DatabaseToken(),
        "HOST": "YOUR_URL",
        "CONN_MAX_AGE": 30 * 60,  # max of 30 min, since AT is valid for 60 min
        "OPTIONS": {"driver": "ODBC Driver 18 for SQL Server"},
        "TEST": {"NAME": "YOUR_TEST_DATABASE"},
        "TIME_ZONE": "UTC",
    }
}

Here, you have to update the values for the HOST and NAME keys. We are using ODBC Driver 18 for SQL Server which you might want to update depending on the specific driver you are using.

The above code using the DatabaseToken() instance might seem overly complex. As is described in this GitHub issue, we could also just use one line: DefaultAzureCredential().get_token("https://database.windows.net/.default").token.

I have found that the simple way sometimes leads to problems depending on the specifics of the development setup. Sometimes it will try to use a token from the shared token cache which might fail without an easy way to fix this. I have found that falling back to acquiring a token through another way (e.g. Azure CLI) works great.

Summary

Setting up token authentication for MSSQL databases with Django is quite easy thanks to the support for the specification of the token as part of the login credentials. You will have to give your Entra user or a group that your user is part of access to the database, login using that user, and then adjust your settings.py.