Adaptive Server is unavailable or does not exist

Question

I'm trying to connect to mssql server via FreeTDS.

First I tried it via ODBC Driver 17 for SQL Server and it works. Here is my configuration in settings.py.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'mssql_database': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'blabla', 
        'USER': 'blabla', 
        'PASSWORD': 'blabla', 
        'HOST': '10.65.1.20', 
        'PORT': '',  
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server', 
        },
    },
   
}

According to this guide I installed FreeTDS on Ubuntu 18.04.

Here is my /etc/odbcinst.ini

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1
UsageCount=1


[FreeTDS]
    Description = TDS driver (Sybase/MS SQL)
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    CPTimeout =
    CPReuse =

And here is the new settings.py section

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'mssql_database': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'blabla', 
        'USER': 'blabla',  
        'PASSWORD': 'blabla', 
        'HOST': '10.65.1.20', 
        'PORT': '', 
        'OPTIONS': {
            'driver': 'FreeTDS', 
            'host_is_server': True,
            'extra_params': "TDS_VERSION=8.0"
        },
    },
}

And I have this error message pyodbc.OperationalError: ('08S01', '[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)')

How can I fix the error? The connection works with ODBC Driver 17 for SQL Server. So why doesn't it work with FreeTDS driver? Could it be because the file /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so isn't there? I can't find libtdsS.so.

$ pip list
Package       Version
------------- -------
Django        1.8
django-pyodbc 1.1.3
pip           21.3.1
pyodbc        4.0.32
setuptools    59.6.0
sqlany-django 1.13
sqlanydb      1.0.11
wheel         0.37.1

I set the port to `1433` and it works now. – xralf May 16 '22 at 07:06 — xralf, May 16 '22 at 07:06

score 2 · Accepted Answer · answered May 16 '22 at 11:37

2

As you noticed, set the PORT to 1433 - but that is only part of what you need to do.

TDS_Version=8.0 is invalid and will break on newer versions of FreeTDS greater than 1.3: https://www.freetds.org/userguide/ChoosingTdsProtocol.html

Since Ubuntu 18 ships with FreeTDS version 1.00.82, you should use version 7.4 of the TDS protocol, assuming you are using SQL Server 2012 or higher.

Change your options as follows:

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

You may have to re-run your migrations, if any, because the newer TDS Versions support more SQL Server fields, such as DATE and DATETIME2 - but it looks like you may just be using SQL Server for reads. Good luck!

answered May 16 '22 at 11:37

FlipperPA

13,607
4
39
71

1

Tommorrow I wil start anew. Which Ubuntu packages should I install to have the FreeTDS version 7.4? Can I check my version somehow? Or should I compile the FreeTDS from source rather? But now, I won't use it with MSSQL Server, but Sybase as [here](https://stackoverflow.com/questions/72255279/freetdssql-serversql-anywhere-error-265-procedure-serverproperty-not-fou) – xralf May 16 '22 at 16:16
1

Unfortunately, I've never heard of Django working with Sybase. I've helped out with the SQL Server backends for a few years, and recommend trying the Microsoft developed backend: https://github.com/microsoft/mssql-django For FreeTDS, you should be fine using whatever version modern Ubuntu supports. 18, 20, and 22 all support TDS Protocol 7.4, which AFAIK, is the most recent. – FlipperPA May 16 '22 at 21:17
1

I will use it with `pyodbc` and `FreeTDS` with raw queries as recommended [here](https://stackoverflow.com/a/72259266/653379), because django does not work with `sybase`. Only Django to version 1.8 works with sqlanydb and sqlanydjango, but it causes that `pyodbc` stops [working](https://stackoverflow.com/questions/72125696/driver-managercant-open-lib-opt-microsoft-msodbcsql17-lib64-libmsodbcsql-17). – xralf May 16 '22 at 21:33
I installed unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc and when I run `tsql -C` there is a line `Version: freetds v1.00.82` and a line `TDS version: 4.2` – xralf May 17 '22 at 07:14
That’s just the default TDS version. You can override it to 7.4 in version 1.0 and above. – FlipperPA May 17 '22 at 16:51
OK, I understood. – xralf May 17 '22 at 17:00

Adaptive Server is unavailable or does not exist

1 Answers1