SQL 2000 中的 ODBC 问题 --> 2005年升级
这篇 wiki 文章概述了问题和解决方案。我想将这篇文章发布给可能遇到类似问题的其他人，因为我在其他地方找不到任何专门解决此问题的内容。
我们最近将 SQL Server 2000 数据库升级到 SQL Server 2005。服务器上的数据库之一是 MS Access 数据库的后端。 MS Access 数据库使用传递查询，通过 DSN-less ODBC 连接到 SQL Server。
无 DSN 连接字符串的示例如下所示：
ODBC; DRIVER=SQL Server;SERVER=servername;APP=Microsoft® Access (Pass Through
Query);DATABASE=databasename;Network=DBMSSOCN;ConnectionTimeout=20;
Trusted_Connection=Yes
升级后，我们发现用户无法运行直通查询，并且显示以下错误：
ODBC——连接到“SQL Server” '失败
这最初似乎是一个权限问题，因为将 SQL Server 登录权限提升到 sysadmin 服务器角色可以缓解该问题（但显然这不是一个很好的解决方案）。
将登录名从 sysadmin 角色中撤回后，我们发现当通过 Management Studio 连接到 SQL Server 时，登录名可以执行存储过程。无法从 MS Access 中进行相同的登录。这表明 MS Access 在尝试执行存储过程时正在执行某些操作，而不是权限问题。
我们使用 Profiler 在服务器上运行跟踪，这显示 MS Access 尝试在存储过程执行之前执行以下命令：
DBCC TRACEON(208)
在存储过程执行之前，此命令似乎失败。网上的研究表明，DBCC TRACEON(208) 相当于使用“SET QUOTED IDENTIFIERS ON”命令，并且在 SQL 2005 中运行此 DBCC 命令的权限已被撤销。
经过进一步研究，我们发现对 MS Query 的引用也存在类似问题，并且连接字符串的 APP 组件应从“MS Query”更改为其他内容。
凭直觉，我们更改了 ODBC 连接字符串的 APP 组件，并且 MS Access 不再尝试在执行存储过程之前执行 DBCC TRACEON(208)。
经过进一步测试，我们将问题追溯到 APP 组件中包含的“版权”符号：
APP=Microsoft® Access (Pass Through Query)
通过删除版权符号，连接一切正常，应用程序可以像以前在 SQL 2000 上一样运行。
希望这会有所帮助其他人有类似的问题。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这不是注册商标符号吗？
我相信您已经发现了 sql server 2005 针对基于 odbc 的攻击的防御措施之一。由于互联网上没有任何相关信息，因此这很可能是微软内部处理的事情。
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.