OPENROWSET() fails 'Ad Hoc Access..denied' ADO

[K. Lobe]

I'm tring the the following where strDBtoOpen is the UNC path to an Access
Database:

..ASP page ADO Connection object Source property.
cn.Source = "SELECT DD.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','" &
strDBtoOpen & "';'admin';'', DueDiligence) as DD WHERE ReviewedBy IS NOT
NULL"



The error I get is:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
/PreBoarding/DueDiligence/ddUploadFileSelProc2.asp, line 71


The issue is: it works on our development server and there is no linked
server. Anyone know what could be causing this? I'm stumped as I can't find
a difference between the two SQL servers. SQL 2000.

Thanks,

Kevin

[Steve Kass]

Kevin,

See if the linke below helps. This is more or less the opposite
situation from yours, but it explains what governs whether ad hoc access
is allowed..

[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;328569[/url]

Steve Kass
Drew University

K. Lobe wrote:

>I'm tring the the following where strDBtoOpen is the UNC path to an Access
>Database:
>
>.ASP page ADO Connection object Source property.
>cn.Source = "SELECT DD.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','" &
>strDBtoOpen & "';'admin';'', DueDiligence) as DD WHERE ReviewedBy IS NOT
>NULL"
>
>
>
>The error I get is:
>
>Error Type:
>Microsoft OLE DB Provider for SQL Server (0x80040E14)
>Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
>You must access this provider through a linked server.
>/PreBoarding/DueDiligence/ddUploadFileSelProc2.asp, line 71
>
>
>The issue is: it works on our development server and there is no linked
>server. Anyone know what could be causing this? I'm stumped as I can't find
>a difference between the two SQL servers. SQL 2000.
>
>Thanks,
>
>Kevin
>
>
>
>

[Andy Ball]

might be that the registry has been set with the DisAllowAdHocAccess key in
the registry for the OLEDB provider for Jet as per link below (look towards
bottom for ad hoc access explanation )

look at key key below to see if its there. I have a nagging feeling that
some patch / MDAC release changed this behaviour but can't find any info on
this. Might be worth comparing MDAC versions between boxes using the
Component Checker
([url]http://www.microsoft.com/downloads/details.aspx?familyid=8f0a8df6-4a21-4b43[/url]
-bf53-14332ef092c9&displaylang=en)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\Microsoft.Jet.OL
EDB.4.0


[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;327489[/url]

Andy.


"K. Lobe" <nospamKLoeb@snsc.com> wrote in message
news:eFjmc6hXEHA.2408@tk2msftngp13.phx.gbl...

> I'm tring the the following where strDBtoOpen is the UNC path to an Access
> Database:
>
> .ASP page ADO Connection object Source property.
> cn.Source = "SELECT DD.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','" &
> strDBtoOpen & "';'admin';'', DueDiligence) as DD WHERE ReviewedBy IS NOT
> NULL"
>
>
>
> The error I get is:
>
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been

denied.

> You must access this provider through a linked server.
> /PreBoarding/DueDiligence/ddUploadFileSelProc2.asp, line 71
>
>
> The issue is: it works on our development server and there is no linked
> server. Anyone know what could be causing this? I'm stumped as I can't

find

> a difference between the two SQL servers. SQL 2000.
>
> Thanks,
>
> Kevin
>
>