Named Pipes Provider

Question

Post reply

Named Pipes Provider

Chris Lee-152270

SSCommitted

Points: 1809
More actions
January 24, 2008 at 3:57 pm

#183251

Hi,
I have SQL 2005 Ent SP2 on Windows 2003 server. When I tried to compile a proc from local box and the error:
OLE DB provider "SQLNCLI" for linked server "PROD" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "PROD" 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].
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'pr_proc', because it does not exist or you do not have permission.
"PROD" is alias of a SQL 2000 server. Inside the proc has something like:
select * from PROD.database.dbo.table.
If I go to SQL 2005 server (there is linked server setup. I could see all databases and table...when expand the folder), I could run the select statement from SQL Server Management Studio without any problem. Do I have to setup on local box? Or something missing from my local box? Thanks.
Chris

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Anam Verma SSCertifiable Points: 5365 More actions · Answer 1

can you please give a try as it happened with me once.

try

select * from [PROD].[database].dbo.

instead of

select * from PROD.database.dbo.table.

i know there is no difference in this but it worked for me.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Have you actually gone in to the Surface area configuration tool and enabled remote connections? from the server console on the box you can't talk to?

By default the install comes with that turned off.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Chris Lee-152270 SSCommitted Points: 1809 More actions · Answer 3

Hi SCC and Matt,

Thanks for the help. I will try:

select * from [PROD].[database].dbo.

tomorrow when I get back to office.

And yes, I signed onto SQL server itself and made sure it allows all local, remote, named pipe and TCP/IP connection. I also ran cliconfg and added alias/server on server box. From server box, I could expand server object to view all database, table and views on SQL 2000 box. I could run:

select * from PROD.database.dbo.table

on server box with no problem. But I could not compile procedure nor execute the SQL statement on my local client box. Not sure what prevent me from access SQL 2000 box.

Chris

Atul_SafeMode SSC Veteran Points: 291 More actions · Answer 4

Hi,

Please try this. It should help:

Go to SQL Server 2005 Surface Area Configuration

Select Surface Are Configureation for Services and Connections

Under Database Engine

-> Select Remote Connections

-> Select Local and Remote Connections

-> Using both TCP/IP and Named Pipes

Rgds,

peterhe SSChampion Points: 11469 More actions · Answer 5

Are you using integrated security? It looks like a double hop issue, i.e. NTLM credentials cannot be hopped to the second machine. So the linked server works fine if you run the query on the server but does not work when you run it from a client. Check this link:http://support.microsoft.com/kb/238477

named pipe is a workaround. Make sure both servers enable the named pipe protcol.