For one reason or another, a particular development SQL Server is unable to openrowset or 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 [5]. 

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?

  • Try to telnet to the machine on port 1433, like telnet targetserver.target.domain.com 1433. If that fails (especially when it's a timeout), that points at a firewall issue.
    – Andomar
    Feb 7, 2012 at 21:34
  • I'll give this a shot from the dev box tomorrow. Feb 8, 2012 at 1:51
  • @Andomar Looks like I'm timing out. I'm going to talk to our firewall experts and see what's going on. Thanks! Feb 15, 2012 at 15:16

2 Answers 2


It's a simple setting for sp_configure (that should work for all newer versions of SQL Server - 2005, 2008, 2008 R2):

sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;

This will enable the use of OPENROWSET and OPENDATASOURCE.


In SQL Server 2005, you have to explicitly allow ad-hoc remote queries in the Surface Ara Configuration wizard. When enabled, the instance of the Database Engine supports the use of the OPENROWSET and OPENDATASOURCE functions to run remote queries without first defining a linked server.

See this link for how to set this setting on your development server.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.