Code Project

Link Unit

Thursday, December 20, 2007

Ad hoc access to OLE DB provider has been denied

Solution


In SQL server 2000 I was trying to import an xls file into a table.


SELECT * into TempData FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Data1.xls', [Sheet1$])


It worked with sa login, but with other users/login it gave error

"Ad hoc access to OLE DB provider [Microsoft.Jet.OLEDB.4.0] has been denied.
You must access this provider through a linked server"

This problem can be solved by
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers \Microsoft.Jet.OLEDB.4.0]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000000

The last line does the trick. If DisallowAdhocAccess is absent, it defaults to 1.

Post a Comment