error with SP Execution

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  April 16, 2014 at 1:27 am

  #285873

  Hi,

  I am having SP where I am pulling data from linked server. Previously its working fine but suddenly started to give below error.

  Msg 15281, Level 16, State 1, Procedure Procedure_Name Line 184

  SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

  What could be the issue? Any Idea?

  Thanks

  Abhas.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  April 16, 2014 at 2:45 am

  #1706353

  Looks like something has changed, what does this query bring back?

  😎

  SELECT

  sc.configuration_id

  ,sc.name

  ,sc.value

  ,sc.minimum

  ,sc.maximum

  ,sc.value_in_use

  ,sc.description

  ,sc.is_dynamic

  ,sc.is_advanced

  FROM sys.configurations sc

  where sc.name = 'Ad Hoc Distributed Queries';

  GO

• free_mascot

  One Orange Chip

  Points: 27169

  More actions

  April 16, 2014 at 2:49 am

  #1706354

  Do you have sysadmin privilages on the server?

  If yes enable option 'Ad Hoc Distributed Queries' in configuration by executing 'sp_configure' (with enabling advance option on).

  ---------------------------------------------------
  "Thare are only 10 types of people in the world:
  Those who understand binary, and those who don't."

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 16, 2014 at 2:56 am

  #1706357

  Go have a chat with your DBA and ask why the option was changed. It could be a security requirement that it be disabled.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  April 16, 2014 at 5:55 am

  #1706412

  Hi All,

  Its Security issue.

  Thanks for your help.

  Thanks

  Abhas