Cannot create an instance of OLE DB Provider "MSDAC"

[shaggydog]

Hey Guys,

I'm trying to query a couple of Excel 2007 spreadsheets for some reports
using OpenDataSource in my query to pull in the data.

Everything is find with the preview inside the IDE, but when I deploy my
report I recieve the following error:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'Regression'.
(rsErrorExecutingCommand)
Cannot create an instance of OLE DB provider "MSDASC" for linked
server "(null)".

I've installed 2007 Data Connectivity Components on the report server and
this allowed me to query the data while developing. I'm at a loss why this is
not working after I deploy.

The SQL I'm using to query the data is:
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="\\server\document.xlsm";Extended Properties="Excel 12.0
Macro;HDR=YES"')...Sheet1$

Any ideas/suggestions?

[Bruce L-C [MVP]]

My guess if this works in development and not in deployment that it has to
do with file security. The report is executed under a different user account
and that account doesn't have rights to the folder where the Excel data
resides. Just a guess because I have never done this.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"shaggydog" <shag...@discussions.microsoft.com> wrote in message
news:55853C99-FB58-422B...@microsoft.com...

[shaggydog]

Thanks for your response, while not directly related to file security, it was
a security problem. Here's what I did to solve my problem in case anyone
comes across this thread in the future....

I was using a shared data source which connected using windows
authentication. No matter how I tweaked and fiddled around I could not get
windows authentication to work.

So I setup a SQL account with limited rights. Then provided me with a new
error message stating:

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.
You must access this provider through a linked server.

While I have Ad hoc access enabled via Surface Configuration, it would not
allow me to perform an ad hoc query if the user account wasn't a sysadmin to
the SQL Server. Ugh!

I played around with tweaking the DisallowAdhocAccess registry setting for
the provider, but I didn't find a way to allow non-sysadmin accounts the
ability to perform ad hoc queries.

In the end using a SQL authentication with a sysadmin account allowed
OpenDataSource to pull in the external data.

For what it's worth.....