Discussion:
Ad Hoc access to OLE DB provider 'MSDASQL' has been denied error
(too old to reply)
Tim Zych
2007-02-22 01:24:33 UTC
Permalink
I can run a query that accesses a server and database no problem, as a
developer. The user can run the same query no problem on another database
and server.

When a user tries to run a query (via a VB.Net project), they get an error:

"Ad Hoc access to OLE DB provider 'MSDASQL' has been denied
You must access this provider through a linked server."

The query that it's stopping on, I believe, is using OPENROWSET.
However, I can run this no problem. And, the user can run the query no
problem on another database and server

What must be modified to allow them to run this? The user seems to have the
same rights to the query from what I can see in Enterprise Manager.

SQL Server 2000.

Any help is appreciated. Thanks.
lucky
2007-02-22 03:02:13 UTC
Permalink
On Feb 21, 5:24 pm, "Tim Zych" <FirstInitial
Post by Tim Zych
I can run a query that accesses a server and database no problem, as a
developer. The user can run the same query no problem on another database
and server.
"Ad Hoc access to OLE DB provider 'MSDASQL' has been denied
You must access this provider through a linked server."
The query that it's stopping on, I believe, is using OPENROWSET.
However, I can run this no problem. And, the user can run the query no
problem on another database and server
What must be modified to allow them to run this? The user seems to have the
same rights to the query from what I can see in Enterprise Manager.
SQL Server 2000.
Any help is appreciated. Thanks.
Have you tried changing the connection string with SQL native client
provider (SQLNCLI)

I think in your case DisallowAdhocAccess is not set to 0 for OLEDB
provider MSDASQL.

You can check the value at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
\Microsoft SQL Server\MSSQL.1\Providers

By default i believe it is set to 0 for SQLNCLI. For rest, it is not
set to 0.
Tim Zych
2007-02-22 03:15:51 UTC
Permalink
Thanks...but, wouldn't the registry have to already be set if they can run
the query from one server / db but not another?
Post by lucky
On Feb 21, 5:24 pm, "Tim Zych" <FirstInitial
Post by Tim Zych
I can run a query that accesses a server and database no problem, as a
developer. The user can run the same query no problem on another database
and server.
"Ad Hoc access to OLE DB provider 'MSDASQL' has been denied
You must access this provider through a linked server."
The query that it's stopping on, I believe, is using OPENROWSET.
However, I can run this no problem. And, the user can run the query no
problem on another database and server
What must be modified to allow them to run this? The user seems to have the
same rights to the query from what I can see in Enterprise Manager.
SQL Server 2000.
Any help is appreciated. Thanks.
Have you tried changing the connection string with SQL native client
provider (SQLNCLI)
I think in your case DisallowAdhocAccess is not set to 0 for OLEDB
provider MSDASQL.
You can check the value at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
\Microsoft SQL Server\MSSQL.1\Providers
By default i believe it is set to 0 for SQLNCLI. For rest, it is not
set to 0.
Loading...