Cannot connect to SQL Server on other device

Using Django Mystery Errors
1

I have an existing SQL Server database on a server, and I want to connect to it from my PC, using Django. I have found some systems that claim to work, but of them seem to do the trick.

I have an ODBC connection from my PC to the database, and test connection confirms that is good. It is a 64-bit connection, and I am using 64-bit Python; the connection is “System DSN”.

I have my database set up like this:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'Idhammar_64',
        'HOST':'f2-sql01\idhammar',
        'PORT':'1433',
        'OPTIONS':{
            'driver':'ODBC Driver 10 for SQL Server',
            'extra_params': 'MARS_Connection=Yes',
        },
    }
}

And I am getting this error:

django.db.utils.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

If I comment out the name there is no error - until I try to use the database anyway - so it seems to be that it cannot find the ODBC connection using that name, but it is definitely set right.

Any advice appreciated!

2

See the docs at mssql-django · PyPI for installation and configuration instructions. Ensure you have the right packages installed and that you’re using the right ENGINE. (It should be mssql, not sql_server.pyodbc.)

3

Thanks for the reply. That is different package, which is why the engine is not “right”, but if I can get this one to work, that would be great. I definitely have both installed.

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'NAME': 'Idhammar_64',
        'HOST':'f2-sql01\\idhammar',
        'PORT':'',
        'OPTIONS':{
        },
    }
}

Same error.

It is not clear if this is using ODBC. Should the name be the name of the ODBC connection, or the name of the database inside the SQL Server instance? I tried both, and got the same error either way.

So no further forward (but I have realised I need a second backslash in the host name!).

4

From the docs shown at mssql-django · PyPI :

  • NAME
    String. Database name. Required.

Also, which ODBC driver are you using? If you’re not using the default (13), you may need to specify it in the options.

5

Okay, so here is my latest, with the driver specified, and the name set to the name of the database inside my SQL Server instance:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'Idhammar_v12_live',
        'HOST':'f2-sql01\\idhammar',
        'PORT':'',
        'OPTIONS':{
            'driver':'ODBC Driver 10 for SQL Server',
        },
    }
}

Same result. It does seem odd that I am not specifying which ODBC connection to use.

6

You can try that by using dsn in the options instead of the HOST setting.

7

That is what I was missing! The docs give no hint that the DSN is the name of the ODBC connection. Am I supposed to just know that?

Seems to now connect. It failed to auto-generate the models, but maybe that has not been implemented? I can sort that out manually.

Thanks for the help.

8

Have you ever used the ODBC Data Source Administrator to configure connections? If so, you’ll see three tabs named as User DSN, System DSN, and File DSN. It really is the “proper name” for this.

Are you saying that the inspectdb command did not work? (I don’t see that identified in the Limitations section of the docs.) Is it possible that you may need to upgrade your ODBC driver? It looks like you’re using version 10, the most current version I see for download shows as version 18. In fact, I don’t even see version 10 as being available at Release Notes for ODBC Driver for SQL Server on Windows - ODBC Driver for SQL Server | Microsoft Learn. Having said this, I don’t know MS ODBC drivers well-enough to know whether they’re in any way tied to particular versions of SQL Server - I would hope that they aren’t, but I can’t attest to that either way.

9

I had to update ODBC to 17 to get the connect to work. Never got auto-generate to work, but I only need a subset of the models and fields, and could do it by hand.

I did encounter a bug in the SQL Server package when used with modern Django, as described here;

The solution that worked for me (so far?) was to edit …\Lib\site-packages\sql_server\pyodbc\compiler.py, line 294.

            if self.query.explain_query:

becomes

            if self.query.explain_info:

Also, trying to do objects.all() throws a FullResultSet exception, but that may be by design?