For one reason or another, a particular development SQL Server is unable to
opendatasource to production servers, but can hit other dev servers. I've tried sqloledb and sqlncli providers.
I'm not sure if this is a database or network configuration.
If it is a database configuration, is it set on the dev server, or ALL of the prod servers?
Is it a firewall setting?
It's weird. I have full access from the Sql Server instance running from my PC, but not the one on the dev server. I'm using SSMS 2008 in both cases. The dev server is 2005, and the remote access is to other 2005 and 2000 servers.
I ask the question as "how to block queries" because I'm looking to know what settings/configuration I need to disable to allow it again. Thanks!
The error message returned is as follows:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". Msg 5, Level 16, State 1, Line 0 Named Pipes Provider: Could not open a connection to SQL Server .
Edit: Not sure if folks who answered are clear on what I am looking for. Ad hoc Queries are enabled for all boxes, but from this particular development box, querying production boxes results in the error message above. How did they block things so specifically?