I set up a new SQL Server 2008 server a while back, configured it to my liking, set up the correct permissions, etc. etc. I also set up a number of linked servers. Testing it from my account and from SQL authenticated accounts worked just fine, so I brought it live. Unfortunately, one of our users started getting a very weird message. Whenever this user tried to run a query that involved using a linked server, the following message popped up:
OLE DB provider “SQLNCLI10″ for linked server “SERVER” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI10″ for linked server “SERVER” 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 changed the bolded name to protect the innocent. This error appeared for all of the linked servers, be they SQL 2005 or 2008. The weird thing is that with the exact same permissions, it worked just fine on another SQL 2008 server. And it worked just fine for me and another person in the Database Administrator group in Active Directory. But it failed for our Architect, who is also a sysadmin on the server.
- Make sure that your protocols are set up correctly in SQL Server. Check. I had Shared Memory, TCP/IP, and Named Pipes enabled as services, though only TCP/IP was enabled as an access point for the server.
- Make sure that the SQL Server Browser is on. Check. I actually had it disabled because I had just one instance on the server, but even after turning it on, that didn’t solve the problem.
- Make sure that the registry is OK. Check. There weren’t any registry errors, and the keys in the registry that people had me look at were just fine.
I checked this against SQL Server with Service Pack 1, and a re-installed SQL Server without any service packs, and it showed the same error. The interesting thing was that the Database Administrators group and SQL authenticated accounts could access other linked servers, but not other AD users. And those same AD users could access the troubled 2008 server from other servers, so the error was just one-way.
The resolution in this case was as follows: we have a Domain Users account in Active Directory which includes all users who would need access to our servers (including the user in question who had the problem). This account needed to be added to the Users group on the server.
My speculation here is that because Database Administrators were already in the Administrators group, we could access the registry. But all other users were locked out from the registry because they were not in any server access groups. Because they could not get to the registry to access the available protocols for connection to linked servers, that error would pop up, and the linked server connection would eventually time out because the connection was not cleanly closed after receiving an error. But if these users went in through SQL authentication, their queries would use the service account we set up to access the registry. I made sure that this service account had access to the server, and so it worked just fine.
This is a solution that I did not see at all in my searches, so hopefully it helps someone.