CODEWITHSUNDEEP
sql-serverdjango
Jingwei
Jingwei

Reputation: 23

Cannot connect django 1.11.6 to MS SQL Server using django-pyodbc-azure

I am working on a django app which needs to connect to MS SQL Server 2008. I use django-pyodbc-azure backend.

Environment:

I have also installed dependents: unixodbc unixodbc-dev tdsodbc freetds-dev

In /etc/freetds/freetds.conf: 

[sqlserver]  
    host = mysqlserverhost.com
    port = 6789
    tds version = 8.0

In /etc/odbc.ini: 

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Servername = sqlserver
Database = test
TDS_Version = 8.0

In /etc/odbcinst.ini: 

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[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 =
FileUsage = 1

Then I tested the connection with the following. 

import pyodbc
db = pyodbc.connect('DRIVER={FreeTDS};SERVER=mysqlserverhost.com,6789;DATABASE=test;UID=admin;PWD=password;TDS_Version=8.0')
cursor = db.cursor()
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
    print(row[0])
    row = cursor.fetchone()

I can see the version of SQL Server from the above codes. 

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

In my django project settings.py, I configured the database backend. 

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'test',
        'USER': 'admin',
        'PASSWORD': 'password',
        'HOST': 'mysqlserverhost.com',
        'PORT': '6789',
        'OPTIONS': {
            'driver': 'FreeTDS',
        },
    }
}

However, when I opened my app page and saw the below error. 

File "/usr/local/lib/python3.5/dist-packages/sql_server/pyodbc/base.py" in get_new_connection
309.                                         timeout=timeout)
Exception Type: OperationalError at /myapp/
Exception Value: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

Is there anything wrong in my settings?

Upvotes: 1

Views: 1316

Answers (2)

Melvyn Sopacua
Melvyn Sopacua

Reputation: 684

If you get here searching for the error message, let me stress that anything preventing a login will generate this messwage. Even if you forget to specify a user.

There's no hint whatsoever to the actual login problem and the error message is misleading to connection/host resolving problems.

In addition, when specifying an unresolvable host a timeout doesn't occur within a reasonable timeframe (I waited for > 5 minutes) and the operation cannot be interrupted by ctrl+c. I had to suspend the python shell and kill the backgrounded job instead.

Upvotes: 0

Jingwei
Jingwei

Reputation: 23

I think I have solved this question myself although I have encountered another issue.

The answer is actually in the django-pyodbc-azure package document.

  • host_is_server
    Boolean. Only relevant if using the FreeTDS ODBC driver under Unix/Linux.
    By default, when using the FreeTDS ODBC driver the value specified in the HOST setting is used in a SERVERNAME ODBC connection string component instead of being used in a SERVER component; this means that this value should be the name of a dataserver definition present in the freetds.conf FreeTDS configuration file instead of a hostname or an IP address.

By adding 'host_is_server': True into database backend settings, I no longer see this OperationalError.
Now the in my django project settings.py, the database backend is:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'test',
        'USER': 'admin',
        'PASSWORD': 'password',
        'HOST': 'mysqlserverhost.com',
        'PORT': '6789',
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True
        },
    }
}

Upvotes: 1

Related Questions