ODBC problems in SQL 2000 --> 2005 Upgrade

Question

This wiki post outlines both a problem and a solution. I wanted to post this for others that may be having similar problems, as I couldn't find anything specifically to solve this problem elsewhere.

We recently upgraded our SQL Server 2000 database to SQL Server 2005. One of the databases on the server is a back-end to a MS Access database. The MS Access database uses pass-through queries, via DSN-less ODBC to connect to SQL Server.

An example of the DSN-less connection string is shown below:

ODBC; DRIVER=SQL Server;SERVER=servername;APP=Microsoft® Access (Pass Through
    Query);DATABASE=databasename;Network=DBMSSOCN;ConnectionTimeout=20;
    Trusted_Connection=Yes

After the upgrade, we found that users were unable to run the pass-through queries, and were getting the following error displayed :

ODBC -- connection to 'SQL Server ' failed

This initially appeared to be a permission issue, as elevating the priveledges of the SQL server logins to sysadmin server role alleviated the problem (but obviously this isn't a great solution).

After taking the logins back out of the sysadmin role we found that when connecting to SQL Server via Management Studio, the login could execute the stored procedures. The very same login could not from within MS Access. This pointed to something MS Access was doing while trying to execute the stored procedures - rather than a permission issue.

We ran a trace on the server using Profiler, and this showed up MS Access trying to execute the following command prior to stored proc execution:

DBCC TRACEON(208)

It appeared to fail at this command, prior to stored procedure execution. Research on the web showed that DBCC TRACEON(208) is equivalent to using 'SET QUOTED IDENTIFIERS ON' command, and that in SQL 2005 priveledges to run this DBCC command had been revoked.

After further research, we found references to MS Query having a similar problem, and that the APP component of the connection string should be changed from 'MS Query' to something else.

On a hunch, we changed our APP component of the ODBC connection string, and MS Access no longer tried executing DBCC TRACEON(208) prior to stored procedure execution.

After further testing, we tracked the problem down to the 'copyright' symbol included in the APP component :

APP=Microsoft® Access (Pass Through Query)

By removing the copyright symbol, all was well with the connection and the application worked as it had previously done on SQL 2000.

Hope this helps out anyone else having a similar problem.

Answer 1

Isn't that the registered trademark symbol?

I believe you hit on one of sql server 2005's defences against odbc based attacks. Since there isn't anything on the internet about it, it is likely something MS handled internally.