Configuring linked server for a service account - error authenticating

Question

I am trying to run a report through SSRS 2008 web interface.  The credentials that are being used to execute the report are through a service account.  The point of using a service account was so that the subscriptions feature could be used.  However, two particular reports connect directly though a linked server to an excel workbook.  I get the following error when I try to execute the report:

 

• An error has occurred during report processing. (rsProcessingAborted)
  • Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
    • The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "YTWCHECK" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "YTWCHECK".

How can I correctly add my service account to the linked server?

 

 

---

ddd

Answer 1

Hello,

Please see the following resource:

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

 

Answer 2

I actually called micrsoft and it happened to be an authentication issue related to the folder where the file was.  My service account did not have permissions to that folder.

 

See below:

•    Symptom - Running a Report in SSRS and gets this error An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "HYACTRADES" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "HYACTRADES".

    •    Cause - Execution Account did not have proper permissions to folder where Excel file was located
    •    Resolution - Gave the Execution account READ/EXECUTE and MODIFY permissions and then the Linked server and Report displayed the data as expected.

 

---

ddd