Django connect SQL Server using active directory user

Question

I'm using Django and mssql-django backend to connect to SQL Server. No problems to connect to SQL Server when using sql login. But, when I try to connect using AD user, I get exception:

django.db.utils.InterfaceError: 
('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
Login failed for user 'DOMAIN\\myuser'. (18456) (SQLDriverConnect); 
[28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); 
[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'DOMAIN\\myuser'. (18456); 
[28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)")

My database settings in settings.py are:

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'NAME': os.environ.get('DB_NAME', 'djangodb'),
        'USER': os.environ.get('USER', 'DOMAIN\myuser'),
        'PASSWORD': os.environ.get('USER_PASS', 'mypass'),        
        'HOST': os.environ.get('HOST', 'server.blabla.net'),      
        'PORT': '',  

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

What I am doing wrong?

Answer 1

Active Directory (AD) users can't be specified as the USER in the connection string if attempting to authenticate using Windows Authentication (Trusted Connection/Integrated Security [SSPI])

It is possible to authenticate using this method but you would need to use Kerberos to authenticate against AD in order to receive the appropriate credentials that the driver can use to authenticate with.

In your Django DATABASES options specify, please note the Encrypt=yes option may not be necessary for your instance, but if using the 18 Driver, I find it is necessary, especially if using an unencrypted connection.

    "OPTIONS": {
        "driver": "ODBC Driver 18 for SQL Server",
        "extra_params": "Encrypt=yes;Trusted_Connection=yes",
    },

Deploying a Linux or macOS ODBC Driver Application Designed to Run as a Service

Deploying a Linux or macOS ODBC Driver Application Designed to Run as a Service A system administrator can deploy an application to run as a service that uses Kerberos Authentication to connect to SQL Server.

You first need to configure Kerberos on the client and then ensure that the application can use the Kerberos credential of the default principal.

Ensure that you use kinit or PAM (Pluggable Authentication Module) to obtain and cache the TGT for the principal that the connection uses, via one of the following methods:

Run kinit, passing in a principal name and password.

Run kinit, passing in a principal name and a location of a keytab file that contains the principal's key created by ktutil.

Ensure that the login to the system was done using the Kerberos PAM (Pluggable Authentication Module).

When an application runs as a service, because Kerberos credentials expire by design, renew the credentials to ensure continued service availability. The ODBC driver does not renew credentials itself; ensure that there is a cron job or script that periodically runs to renew the credentials before their expiration. To avoid requiring the password for each renewal, you can use a keytab file.

Also of interest would be looking at the source code in mssql-django, which will give more insight into what options are selected when connecting to a SQL Server instance. mssql-django: base.py

Check this related SO question: Connection string using Windows Authentication