I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this stored procedure, 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 stored procedure and the stored procedure 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?
Best Answer
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.