February 6, 2009 at 3:14 pm
I'm trying to help a developer with a failing OPENDATASOURCE problem. His query works from a SQL 2000 server to another 2000 box, but encounters this issue when the calling machine is SQL 2005. I did the sp_configue 'allow updates', 1 then RECONFIGURE to no avail.
The 'Adhoc Distributed Queries' is apparetly only for 2005. Here is the error:
Msg 15281, Level 16, State 1, Line 1
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.
Any input is appreciated.
February 6, 2009 at 4:34 pm
The OPENDATASOURCE and other features are disabled by default in SQL Server 2005 you need to go into SAC(surface area configuration) to enable it before your developer can use it. The same xp_cmdshell you will see all the disabled features in SAC enable what you need.
Kind regards,
Gift Peddie
February 9, 2009 at 8:44 am
Gift,
Thanks a lot for pointing that out. I kept worrying about the SQL 2000 configurations and left out the 2005 configuration. My bad.
All is well.
Thanks again and best regards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply