asked on

Ad hoc access to OLE DB provider 'msdasql' has been denied PLEASE HELP

I have an asp3 page that works fine locally but not on our webserver.

It attempts to import a table from an Access database into a SQL Server 2000 database. Here is the code:
SELECT * INTO myTable FROM(SELECT * FROM OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);UserCommitSync=Yes;Threads=3;SafeTransactions=0

;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=281;DefaultDir=F:\Inetpub\WWWROOT\emsystem\system\admin\dbFile;DBQ=d:

\Inetpub\WWWROOT\emsystem\system\admin\dbFile\event.mdb', 'SELECT * FROM Event')) AS newAlias"
Set Rs = Conn.execute(sSQL)

It returns the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Ad hoc access to OLE DB provider 'msdasql' has been denied. You must access this provider through a linked

server.

/emsystem/system/admin/new_event/2-import_to_sqlserver.asp, line 11

Any ideas?

arthuryeung

In SQL Server, you can only connect to an outside ole source when you have added it to the linked server list.

You can add your MS Access to a become linked server for your SQL server by the following steps:

Enterprise Manager -> Security -> Linked Servers
right click -> New Linked Server

you can choose between using OLE or ODBC to connect to the Access

jon100

ASKER

Great thanks.

How come it works locally?

jon100

ASKER

In linked server properties when I select Jet OLE DB Provider it asks for:
product name:, data source: and Provider string:

what do I need to enter into those?

thanks....

arthuryeung

your connection string seems complicated, you may try:

Product Name = Microsoft Access Driver (*.mdb)

Data Source = d:\Inetpub\WWWROOT\emsystem\system\admin\dbFile\event.mdb

Provider String = DRIVER=Microsoft Access Driver (*.mdb);UserCommitSync=Yes;Threads=3;SafeTransactions=0
;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=281;DefaultDir=F:\Inetpub\WWWROOT\emsystem\system\admin\dbFile;DBQ=d:\Inetpub\WWWROOT\emsystem\system\admin\dbFile\event.mdb

jon100

ASKER

Ive added the link in but it still comes up with the same error - I think its strange that it works locally but not on the web server.

Could it have anything to do with us using the MS IIS Lockdown & Security Analyser etc? (this is the first time ive ever tested it on the server)

ASKER CERTIFIED SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jon100

ASKER

Thanks for the info.

Im running SQL Server 2000 sp3 - have they let the bug slip in again i wonder?!

arbert

Yes, it also applies to SQL Server 2000.....

jon100

ASKER

Great thanks a lot!

ispaleny

Goodbye DTS. Be welcomed OPENROWSET.
Thank you.

arbert

I agree DTS definately has its quirks. Looks like DTS is going to be MASSIVELY improved in Yukon....

Brett

jon100

ASKER

I have looked at the DisallowAdhocAccess registry key and its set to 0. Do you have any idea how I could get this to work?

Could it have anything to do with the IIS Lockdown tool or the MS Baseline Security Analyser that have been run on our box? I ask this because it works fine locally.

Thanks - sorry it appears I do not have a solution to my problem (I thought the registry fix would do it but have only just found it).

ispaleny

You must be a wizard to solve it.

At first follow arbert's hint and run reg-file like this

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers]
"DisallowAdhocAccess"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0]
"DisallowAdhocAccess"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDAORA]
"DisallowAdhocAccess"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL]
"DisallowAdhocAccess"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB]
"DisallowAdhocAccess"=dword:00000000

But it was not working still.

At second you must practise witchcraft.
Go to EM->Security->linked servers
->AddNew->ODBC->Provider Properties->Check "Disallow adhoc access"->OK
Add a new server name->OK
Delete the new linked server.
AddNew->ODBC->Provider Properties->Uncheck "Disallow adhoc access"->OK
Add a new server name->OK
Delete the new linked server.

And you are in. Do not ask me how, this witchery is directly from BOL.
You must know the way of reading cryptograms.

jon100

ASKER

FANTASTIC

Not sure why but it works (like you say). Hopefully MS will get this sorted out.

Thanks again - saved my life!

talkingsolution

Works great.
Racking my brains how and why is the solution is working

arof

congratulations for this solution...