I have a perplexing problem which I suspect is permission related.

I have created a Linked Server in SSMS (SQL Express) that connects to an Excel Document on a Network Share. A stored procedure (spUpdateProducts) uses this Linked Server to Merge data into an existing table.
When I execute the stored procedure in SSMS, it works correctly (domain admin).
Similarly, when I execute a script that holds a SQLCMD from the server, that also executes correctly.

SQLCMD -S serverSQLEXPRESS -E -d OutEnd24 -Q “EXEC [dbo].[spUploadProducts]”

However, if I try and execute that as a scheduled task the sproc does not appear to run (or runs and fails to initialise the Linked Server – see below).

Similarly, if I try running the SQL CMD from my client laptop, I get the error:

Msg 7303, Level 16, State 1, Server *****SQLEXPRESS, Procedure spUploadProducts, Line 22
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “OUTEND DATA”.
OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “OUTEND DATA” returned message “Unspecified error”.

My User account on the laptop has permission to execute the sproc, but it appears the Linked Server is unable to access the file in this case (which I suspect is also what is happening in the scheduled task).

I have set the advanced option in SQL Server to allow Ad Hoc Distributed Queries.
The SQL Server instance is running under NT ServiceMSSQL$SQLEXPRESS
I have granted Full Control on the network share and file path to DomainServerName$ 

I have tried experimenting with various combinations of login mapping with the Linked Server, but either I get a user not recognized error, or the same failure as above.

Any thoughts appreciated.

​I have a perplexing problem which I suspect is permission related.I have created a Linked Server in SSMS (SQL Express) that connects to an Excel Document on a Network Share. A stored procedure (spUpdateProducts) uses this Linked Server to Merge data into an existing table.When I execute the stored procedure in SSMS, it works correctly (domain admin).Similarly, when I execute a script that holds a SQLCMD from the server, that also executes correctly.SQLCMD -S serverSQLEXPRESS -E -d OutEnd24 -Q “EXEC [dbo].[spUploadProducts]”However, if I try and execute that as a scheduled task the sproc does not appear to run (or runs and fails to initialise the Linked Server – see below).Similarly, if I try running the SQL CMD from my client laptop, I get the error:Msg 7303, Level 16, State 1, Server *****SQLEXPRESS, Procedure spUploadProducts, Line 22Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “OUTEND DATA”.OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “OUTEND DATA” returned message “Unspecified error”.My User account on the laptop has permission to execute the sproc, but it appears the Linked Server is unable to access the file in this case (which I suspect is also what is happening in the scheduled task).I have set the advanced option in SQL Server to allow Ad Hoc Distributed Queries.The SQL Server instance is running under NT ServiceMSSQL$SQLEXPRESSI have granted Full Control on the network share and file path to DomainServerName$ I have tried experimenting with various combinations of login mapping with the Linked Server, but either I get a user not recognized error, or the same failure as above.Any thoughts appreciated.  Read More

​