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.

I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this SPROC there is a query that selects data from ServerB.DatabaseB.SchemaB.TableB. There is a linked server set up from ServerA to ServerB with the security context set to: "Be made using the login's current security context". When the job executes the SPROC and the SPROC tries to select from the remote table the following error is thrown:

Executed as user: domain\user. 
Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)  
OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  
OLE DB provider "SQLNCLI" for linked server "ServerB" 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.". [SQLSTATE 01000] (Error 7412).  The step failed.

I have made sure that the remote server allows remote connections and that the [domain\user] login has appropriate access to the remote server and database.

Does anybody have a resolution to this? It started out as a problem with trying to perform SQL authentication across the wire so we created the domain user and now I'm having this timeout issue.

edit

We now have named pipes enabled on the production servers and I am still getting an error.

Executed as user: <domain>\<user>. Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)  OLE DB provider
"SQLNCLI" for linked server "<remote server>" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI" for linked server "<remote server>" 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.". [SQLSTATE 01000] (Error 7412).  The step failed.

any ideas?

share|improve this question

2 Answers 2

SQL Agent will use Shared Memory to connect to the local server. The Instances will attempt to use named pipes to talk to each other. You can adjust this by creating an alias in the SQL Server Client Network Utility (cliconfg.exe) and setting the alias to use TCP.

share|improve this answer

The error "Named Pipes Provider: Could not open a connection to SQL Server [1450]." leads me to the idea that the Server B doesn't have Named Pipes protocol enabled (you can check in SQL Server Configuration Tool).

Can you run the query in Management Studio without errors (connected to server A take data from B)?

share|improve this answer
    
Yes, I am able to execute the query just fine from SSMS. –  b0fh Feb 21 '11 at 21:30
    
From my knowledge the Management Studio uses TCP-IP protocol. Change the protocol of the new connection to Named Pipes and see if it's still working. –  Marian Feb 21 '11 at 21:44
    
I have determined that named pipes is not active on the servers (these are production servers so adding named pipes and then restarting the SQL Server service is a bit out of the question), is there a way to force the job to use tcp/ip rather than named pipes? –  b0fh Feb 22 '11 at 17:03

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.