August 10, 2011 at 11:39 am
I am trying to use a linked server between two SQL Server 2008 R2 server but am receiving the error:
OLE DB provider "SQLNCLI10" for linked server "<linkedservername>" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "<linkedservername>" " returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].
I have multiple windows logins mapped and for logins not defined in the mappings I use a SQL account. the windows logins are defined like this:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<linkedservername>',@useself=N'True',@locallogin=N'DOMAIN\username',@rmtuser=NULL,@rmtpassword=NULL
When I attempt to use the linked server it works fine(I am an administrator of the windows sevrer and in the sysadmin group in sql server). When a user not defined the the mapping list uses the linked server it works fine. But if a user defined in the mapped logins list other than myself tries to use the linked server they get the error mentioned above.
I have created another linked server in testing without any mapped logins and any user is able to use it to query remote server.
I have also tried the same setup on SQL server 2005. Still does not work but I receive a different error.
OLE DB provider "SQLNCLI" for linked server "SV53786" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "SV53786" 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 1450, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [1450].
I have checked and named pipes is enabled on both servers for the sql server service as well as the Native client protocol.
Any help getting mapped logins to work would be much appreciated.
August 10, 2011 at 2:35 pm
Basically, you're using impersonation, the equivalent of clicking the check box if you were using the GUI to map the login. To use impersonation, the servers must be set up to use delegation, which I *believe* means you need Kerberos configured.
See here for more information on Configuring Linked Servers for Delegation
Hope this helps.
August 10, 2011 at 3:11 pm
Thanks Colleen. I had thought of this and double checked my setup for kerberos which looks ok. If kerberos was not setup properly the linked server would not work with my windows account. I have verified that my account is being used on the remote server.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply