Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 7 '12 at 21:46

This question came from our site for professional and enthusiast programmers.

    
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 '12 at 21:34
    
I'll give this a shot from the dev box tomorrow. –  IronicMuffin Feb 8 '12 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! –  IronicMuffin Feb 15 '12 at 15:16
add comment

2 Answers 2

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.

share|improve this answer
add comment

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;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

This will enable the use of OPENROWSET and OPENDATASOURCE.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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