Openrowset statement does not work in 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Openrowset statement does not work in 2005

Author

Topic

Rekonn
Starting Member

7 Posts

Posted - 2007-01-12 : 12:22:32

We're beginning to upgrade some of our servers to 2005, and I've come across a problem with an openrowset statement that used to work in 2000.

select * from openrowset('SQLOLEDB'
, 'SERVER=TargetServer,3180;uid=dim_usr;pwd=dim_usr@1'
, 'set fmtonly off exec msdb..sp_help_job ')

The TargetServer is running 2000. When the code is run on another 2000 machine, it works as expected. But when the exact same code is run on a 2005 machine, it gives this error:

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 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.

On the 2005 machine that ran the openrowset statement, I was able to ping the TargetServer, so I don't know why it's not being seen. Please help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-12 : 12:51:07

I'll throw out the obvious check first. By default sql 2005 installs not allowing any type of connection besides the local machine.

Check under
Start/SQL Server 2005/ConfigurationTools/SQL Server Configuration Manager

Make sure under the
SQL Server 2005 Network Configuration that you have enabled the proper protocals to enable connection. Ny default all these will be disabled.

Also do the same under

SQL Native Client Configuration/ClientProtocals

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-12 : 13:41:43

this may help:

http://msdn2.microsoft.com/en-us/library/ms188313.aspx

did you create a linked server to TargetServer? if you didn't I think you have to change the settings for "DisallowAdhocAccess" and "Ad Hoc Distributed Queries"

www.elsasoft.org

Rekonn
Starting Member

7 Posts

Posted - 2007-01-12 : 15:22:17

Thanks for the replies, I'm new to 2005 and still don't know where to find things, so if I'm off let me know. In the Configuration Manager, I checked on the Network Configuration and Client Protocols, and they showed the same thing:
Shared Memory - Enabled
TCP/IP - Enabled
Named Pipes - Enabled
VIA - Disabled

There is no linked server, so I started the Surface Area Configuration tool, clicked Surface Area for Features, and found the Ad Hoc Remote Queries option. It is enabled.

Where can I find the DisallowAdhocAccess option?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-12 : 20:02:17

It's better to use a linked server as switching off DisallowAdhocAccess is considered a security hole. read this for more info: http://support.microsoft.com/default.aspx?scid=kb;en-us;327489

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources