36 Chambers – The Legendary Journeys: Execution to the max!

March 24, 2010

SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]

Filed under: Computinating, Programming & Work — Kevin Feasel @ 6:46 pm

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.

I checked out various pages and ideas from lots of different sources regarding similar symptoms.  The advice proffered from these sources was as follows:

  • 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.

About these ads

2 Comments »

  1. Its like you read my thoughts! You seem to grasp so much
    about this, such as you wrote the ebook in it or something.

    I think that you just could do with some percent to force the message house a bit, but
    other than that, that is wonderful blog. A great read. I’ll certainly be back.

    Comment by http://cheezmeh.com — February 7, 2013 @ 12:53 am

    • I own cheezmeh.com and I did not post this comment so whats up here.

      Comment by chris — May 16, 2013 @ 4:06 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: