Django-pydobc SQL server connection problems on windows

django, django-pyodbc, sql-server, windows

Solution

Try using https://github.com/michiya/django-pyodbc-azure. This should work on both Linux and Windows.

Then define your database settings as such:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'dbname',
        'HOST': 'dsn_entry',
        'PORT': 'port',
        'USER': '',
        'PASSWORD': 'pass',
        'OPTIONS': {
            'driver': 'FreeTDS',
            'dsn': 'dsn_entry',
            'host_is_server': True
        }
    }
}

Under Windows the `'driver'` entry in `OPTIONS` should be:

'driver': 'SQL Native Client',

Edit: Oops, failed to see that the you had solved the problem. Leaving my answer here as reference.

Problem

Another developer and I are setting up a django (v1.4.2) project using a legacy SQL server database (SQLEXPRESS) on another server. So far, we have been able to connect to the database from linux and mac using django-pyodbc, and from a laptop running windows 7 using django-mssql. I would like to use django-pyodbc on the laptop to keep the environments in sync. On the laptop: - pyodbc (3.0.6) is installed and in a non-django .py script I can connect and run sql statements - Downloaded django-pyodbc 1.4 by downloading the zip; I'm not sure I installed it right: - I unzipped the file, and ran the setup.py file in the top directory; it puts a sql_server directory in the /lib/site-packages directory - Copied this sql_server directory to /django/db/backends - Created a PYTHONPATH environment variable pointing to /django/db/backends/sql_server - not sure if it's supposed to point to /site-packages/sql_server instead? - Created an ODBC Data Source (System DSN) - testing the connection option works - Editted the DATABASE entry in settings.py to be almost exactly like the linux version (details below) So, it doesn't work, and I get the following error message, and have no idea what to do next: ``` ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53); [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)') ``` I setup the django settings.py file as like so: ``` DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sql_server.pyodbc', 'NAME': 'test', 'USER': 'test', 'PASSWORD': 'something_else', 'HOST': 'mssqlx', 'PORT': '12345', 'OPTIONS': { 'driver': 'SQL Server', }, }, } ``` On linux, my settings file has a DATABASES entry like so: ``` DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sql_server.pyodbc', 'NAME': 'test', 'USER': 'test', 'PASSWORD': 'something_else', 'HOST': 'mssqlx', # ODBC DSN defined in /etc/freetds.conf 'PORT': '12345', # Probably unneeded. Set in mssqlx 'OPTIONS': { 'driver': 'SQL Server', # ODBC driver name in /etc/odbcinst.ini 'extra_params': "TDS_VERSION=7.0" # Probably unneeded. Set in mssqlx } }, } ``` don't know if it will help solve this, but using django-mssql (which only runs on windows), the (working) entry is: ``` DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlserver_ado', 'NAME': 'test', 'USER': 'test', 'PASSWORD': 'something_else', 'HOST': '199.555.0.10', # changed for this example 'PORT': '12345', 'OPTIONS': {'provider': 'SQLOLEDB'} }, } ``` Don't know what other info might help. Thank you for any help or insight you can offer. ----POST MORTEM ---- Here's what finally worked: partial entry in settings for DATABASES: ``` 'default': { 'ENGINE' : 'django.db.backends.sql_server.pyodbc', 'NAME' : 'test_db_name', 'USER' : 'test_db_user_name', 'PASSWORD' : 'password', # ODBC DSN defined in /etc/freetds.conf 'HOST' : 'mssql_test', # Ignored for Windows; Required for Linux 'OPTIONS' : { # ODBC driver name in /etc/odbcinst.ini 'driver': 'SQL Server', # NOTE: dsn option is added dynamically later, for Windows } }, # The ODBC DSN name specified above as DATABASES.default.HOST is ignored on # Windows, where it must be specified as DATABASES.default.OPTIONS.dsn instead. # However, we haven't found a way to make DATABASES.default.OPTIONS.dsn work in # Linux (and probably the same for Mac). It causes the error: # Data source name not found, and no default driver specified # Therefore we add it here, but only for Windows. # Note: The username and pwd in the windows dsn file is apparently NOT used # (b/c server hosts both test and prod database in same MSSQL # instance, both test and prod dsn files happen to work - they have the # same ip address and port number, but different username/password's) # # On 64-bit Windows, with our current 32-bit version of pyodbc, the DSN # must be created via: # C:\Windows\SysWOW64\odbcad32.exe # instead of the regular "ODBC Data Sources" app in Control Panel, which # invokes: # C:\Windows\system32\odbcad32.exe # # os.name is... # nt for Hans' laptop (Windows 7) # posix for the "Amazon Linux AMI" (CentOS) on AWS # posix for Fred's Mac if os.name == 'nt': # Windows DATABASES['cf']['OPTIONS']['dsn'] = 'mssql_test' ```

Original source