?
Solved

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

Posted on 2003-03-18
16
Medium Priority
?
11,128 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 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