January 18, 2010 at 3:22 am
Hi Friends,
I am running an OPENROWSET query to access data from other server:
SELECT a.* , 2 AS KpiId
FROM OPENROWSET
( 'MSOLAP','DATASOURCE=SQLServer1;Initial Catalog=ReportingDB;Integrated Security=SSPI',
' SELECT * FROM [Reporting Table1] ') as a
If I run this query with my own account(Sys Admin), i'm able to get the desired output. If I run this with a service account, which has read access, i'm getting below error:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'MSOLAP' has been denied. You must access this provider through a linked server.
I have also enabled 'ADHOCRemoteQueries' and enabled 'Allow onprocess' for MSOLAP linked server provider.
Can anyone help on this?
Thanks and regards,
Subrahmanya Hegde
January 20, 2010 at 10:17 am
Accounts like "LOCAL Service", "Network Service" only have access to local resources on that box, those accounts will not have access to remote network resources.
To resolve your issue, you can setup your SQL Server that you ran the query to be under a Domain Account, and grant permission to that Domain Account in SQLServer1, you can also try to setup SQL Agent Proxy with a Domain Account,
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply