HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0

NOTE: With the DisallowAdHocAccess property set to 1, SQL Server does not allow ad hoc access through the OPENROWSET and the OPENDATASOURCE functions against the specified OLE DB provider. If you try to call these functions in ad hoc queries, you will receive an error message similar to:

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
已拒绝对 OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 的特殊访问。必须通过链接服务器来访问此提供程序。

In other words, with the DisallowAdHocAccess property set to 1 for a specific OLE DB provider, you must use a pre-defined linked server setup for the specific OLE DB provider; you can no longer pass in an ad hoc connection string that references that provider to the OPENROWSET or the OPENDATASOURCE function.

仅当 DisallowAdhocAccess 注册表选项针对指定的提供程序显式设置为 0，并且启用 Ad Hoc Distributed Queries 高级配置选项时，OPENDATASOURCE 才可用于访问 OLE DB 数据源中的远程数据。如果未设置这些选项，则默认行为不允许即席访问。

配制 Ad Hoc Distributed Queries 的方法：
SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
-- 1为开启0为关闭
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE
GO
SP_CONFIGURE 'show advanced options',0
GO
RECONFIGURE

DisallowAdHocAccess设置方法:

EXEC master..xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0',
N'DisallowAdhocAccess',
N'REG_DWORD',
0

Comments Feed: http://www.ccopus.com/blog/feed.asp?q=comment&id=26