SQL Server 2005
The OPENROWSET and OPENDATASOUCE features need to be enabled by Surface Area Configuration. It is called “Ad Hoc Remote Queries”. Otherwise, you may see the follwoing error.
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ has been denied. You must access this provider through a linked server.
In case the error keep coming up, it may be some of the value are still missing after the Surface Area Configuration. Following the following link, make sure the DisallowAdhocAccess is set up correctly to 0.
Well, the OPENROWSET and OPENDATASOUCE features use the temporary folders. The SQL users may not have rights to the folder SQL Server startup user’s temporary folder.
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Following the link to set the SQL Server startup user’s temporary folder to a place most people have access.