django-mssql-backend - Invalid connection string attribute (0)

Question

OK, I'm slowly going crazy. I want to connect Django 3 to MSSQLv15 Server. Connection works if I use Trustedconnection=yes (using win credentials), it also works on Ubuntu if I'm using FreeTDS v7.4 but it won't work in Windows if I manually insert service account or personal credentials and use sql login. Is it some kind of dependency issue? I have tried various library version combos, but to no avail.

Error message is:

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

Requiremetns.txt

asgiref              3.3.4
Django               3.2
django-mssql-backend 2.8.1
djangorestframework  3.12.4
pip                  20.2.3
pyodbc               4.0.30
pytz                 2021.1
setuptools           49.2.1
sqlparse             0.4.1

Not working (Win)


DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'db_name',
        'HOST': 'hostname',
        'USER': 'DOMAIN\\user',
        'PASSWORD': 'pass',
        'PORT': '1433',

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

Working (Win)

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'db_name',
        'HOST': 'hostname',
        'USER': '',
        'PASSWORD': '',
        'PORT': '1433',

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

Working (Ubuntu)

django==2.1.0
django-pyodbc-azure-2019

    DATABASES = {
        'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'db_name',
        'HOST': 'hostname',
        'USER': 'DOMAIN\\user',
        'PASSWORD': 'pass',
        'PORT': '1433',

            'OPTIONS': {
                "driver": "FreeTDS",
                "host_is_server": True,
                "extra_params": "tds_version=7.4",

               }
            }
        }

use service account or personal credentials without Trustedconnection What does that mean? Either you use a windows account and set the trusted connection option or you use a sql login and don't set that option (or set it to No). If you don't set that option i believe the default is "not set (No)" - meaning you are using a sql login. — SMor
– SMor, Commented Apr 8, 2021 at 12:55
Yep "without Trustedconnection" is unnessacery here. I meant that trusted connection works and sql login with manual credentials doesn't. — Jennosian
– Jennosian, Commented Apr 8, 2021 at 12:59

Tim Nyborg · Accepted Answer · 2021-04-08 13:18:48Z

1

ODBC Driver 17 for SQL Server doesn't seem to support authenticating domain users via username and password, where FreeTDS does (see https://stackoverflow.com/a/37702329/9461432). On a non-Windows machine, Microsoft directs you to use Kerberos.

Authenticating via username & password works fine if you're connecting to a user that uses SQL Server authentication (ie. non-domain).

answered Apr 8, 2021 at 13:18

Tim Nyborg

1,6491 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jennosian Over a year ago

Ah then the mystery is solved. Well, at least I can use domain user in Docker with FreeTDS. Thanks for helping out!

Collectives™ on Stack Overflow

django-mssql-backend - Invalid connection string attribute (0)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related