?
Solved

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

Posted on 2003-03-18
16
Medium Priority
?
11,277 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:jon100
  • 7
  • 3
  • 2
  • +3
16 Comments
 
LVL 4

Expert Comment

by:arthuryeung
ID: 8158875
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
0
 

Author Comment

by:jon100
ID: 8158968
Great thanks.

How come it works locally?
0
 

Author Comment

by:jon100
ID: 8159014
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....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:arthuryeung
ID: 8159061
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
0
 

Author Comment

by:jon100
ID: 8159152
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)
0
 
LVL 34

Accepted Solution

by:
arbert earned 600 total points
ID: 8159393
This doesn't have anything to do with linked servers.  This was a "fix" that microsoft made to disallow "adhoc" queries through the OLEDB provider--check this out:

http://support.microsoft.com/default.aspx?kbid=266008
0
 

Author Comment

by:jon100
ID: 8160006
Thanks for the info.

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

Expert Comment

by:arbert
ID: 8161548
Yes, it also applies to SQL Server 2000.....
0
 

Author Comment

by:jon100
ID: 8182544
Great thanks a lot!
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8203643
Goodbye DTS. Be welcomed OPENROWSET.
Thank you.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8203814
I agree DTS definately has its quirks.  Looks like DTS is going to be MASSIVELY improved in Yukon....

Brett
0
 

Author Comment

by:jon100
ID: 8213714
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).
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8216877
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.
0
 

Author Comment

by:jon100
ID: 8218198
FANTASTIC

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

Thanks again - saved my life!
0
 

Expert Comment

by:talkingsolution
ID: 21479864
Works great.
Racking my brains how and why is the solution is working
0
 

Expert Comment

by:arof
ID: 26445280
congratulations for this solution...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question