Error related to OPENROWSET

• SUBRAHMANYA HEDGE

  SSCommitted

  Points: 1514

  More actions

  January 18, 2010 at 6:25 am

  #209236

  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

• f_ernestog

  Ten Centuries

  Points: 1217

  More actions

  January 18, 2010 at 7:20 am

  #1104923

  Maybe this will be useful for you to fix the issue:

  http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.oledbprovidersettings.disallowadhocaccess.aspx

  http://bytes.com/topic/sql-server/answers/142321-solution-ad-hoc-access-ole-db-provider-has-been-denied-you-must-access-provider-through-linked-server

  I Hope this helps.

  Regards,

  Frank

  Frank.

• SUBRAHMANYA HEDGE

  SSCommitted

  Points: 1514

  More actions

  January 18, 2010 at 8:02 am

  #1104950

  Thanks for the reply frank. I tried the 2nd option already, but it wasn't useful. This seems to be reported as a bug in SQL 2005. Not sure if it was resolved or not:

  _https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=257494

  I will look into the first link and try the things...

  Again thanks for reply.

  Subrahmanya Hegde