Adaptive Server is unavailable or does not exist-CodePudding

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

CodePudding user response：

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!