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",
}
}
}
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).