I recently spent 4 days trying to get the setup of a Linked Server in SQL Server 2008 R2 to work.  I discovered that the UI misrepresents a key security feature for Linked Servers.

Before I explain the Linked Server UI problem, I'll run through my experience of some of the intermediate problems I had trying to get this to succeed.  Hopefully it's helpful by showing the factors and reasoning needed to navigate the world of suggested solutions.  And I believe it's an illustration, among many, of the torturous troubleshooting process, which I find is due to the convoluted design of Microsoft architectures and the poor placement or absence of concise but detailed, helpful information.

Mysterious Connection Errors From SPN Omission

Even though I tried various security parameter combinations in the Linked Server setup dialog, I kept getting errors, such as "Invalid authentication specification" (error 7399).  A hunt on the Web for solutions yielded suggestions such as:

1. "Use a specified, SQL Login security context for the Linked Server."  --> This limits our options for Windows-based logins and is unusable for me.

2. "Turn on trace flag 7300 to get more error information."  --> We shouldn't have to do this sort of thing just to get software to work.  It also didn't help me.

3. "Use the system stored procedures to set up Linked Servers."  --> Why should we be forced to bypass the GUI?  It's supposed to work and save time.

At some point, I tried connecting to SQL Server using a domain admin account (which is also in the local machine's Administrators group), but couldn't.  I then tried to connect using sqlcmd and impersonating the local machine admin. This was a trial-and-error test, which also failed.  I looked at the SQL Server logs and found this error concerning the domain admin account I was using:

Error 18456, "Token-based server access validation failed with an infrastructure error".

Well, "infrastructure" encompasses a lot of stuff.  Where in it is the validation failing?  Why can't we get detailed, useful information in an error?  So I had to search again and found more suggestions...

4. "Disable UAC (User Account Control)."  --> I already have it mostly disabled for Admins.  Changing what I could didn't help.

5. "It's possible there's an SPN missing for the SQL Server service account."  --> A HA!  (Thank you, StackOverflow.com, for reminding me.)

The SQL Server documentation states that the linking server's service account must be set to "Trusted for delegation".  I didn't see this setting for the account and thought it inapplicable, until I read this in the help file "Understanding User Accounts" in Windows Server 2008:

"The Trusted for delegation setting is on the Delegation tab of Properties for user accounts, which is only available if the account has an SPN assigned." (emphasis mine)

I had tried to register an SPN before but didn't succeed due to lack of information, gave up, and must've forgotten about it.  After getting more information, I registered the SPNs for both servers using setspn.exe, and the Trusted for Delegation property was then visible for the SQL service accounts, which I enabled.

Bottom Line: You need to register SPNs for a named instance if it uses a domain account and either:

1. Clients need to identify SQL Server using Kerberos, or

2. Impersonation of a domain account will be used between Linked Servers

Connection Failure From SID Mismatch

After all of the above, however, I still couldn't connect to SQL Server with a domain admin account.  I searched the Web again and read that somebody was running into similar problems due to SID mismatches.  I saw no hint that this was the problem but decided to test it.

Author: I added the following short paragraph--it was missing.

So I logged in to SQL Server with a SQL login, deleted the (Windows Authentication) login for the domain admin, recreated it, and finally I could log in as a domain admin!

I now ask: "Why are we running into SID problems, anyway?"  If I submit a username/password combo to any software, or am already logged in to an environment that uses trust, that should be good enough.  It works that way on xNIX, in my experience.  This SID-mismatch business is ridiculous!

TCP/IP Caveats

While sifting through online Microsoft documentation about SPNs, it sounded like a port must be supplied when registering SPNs for named instances of SQL Server.  This, plus the inconvenience of constantly updating firewall rules, means that we should use static ports, not dynamic ports.  I decided to "finalize" my TCP/IP protocol settings in Configuration Manager, and discovered some caveats:

1. If you unwittingly specify a port that's in use by another program, the server either won't be reachable or may use a random port, and should note a conflict in the log.  Use netstat -ab to find out what programs are listening on what ports.

2. If you look in the SQL Server log, you'll see a line stating that it's listening at the port on localhost that you specify in Configuration Manager.  But another, later, line will say it's listening at a different port on localhost, probably above 49152.  Don't be confused.  It's still listening on the port you specified.  The other port is used for the loopback connection, apparently.

3. If you "enable" listening on localhost, you must either specify a static port or use dynamic ports for it, or do one or the other for "IPAll".  Only somewhat obviously, if you don't you'll have trouble connecting on the local machine.

4. The BOL topic "Registering a Service Principal Name" which I found later, says "the new SPN format [in SQL 2008] does not require a port number" [1].  This is also mentioned in the online documentation I was using, but it's ambiguous about its applicability to TCP/IP [2].  So maybe static ports aren't required for named-instance SPNs!

The Linked Server UX Failure

After overcoming the difficulties with domain credentials, network configurations, etc., I was ready again to tackle the Linked Server headache.  I found that the problem with the Linked Server setup GUI is that the security page fails to indicate that it controls the access security for the person setting up the linked server as well as for those who will use it!

There are 2 areas in this page of the dialog box:

• The top one is labeled "Local server login to remote server login mappings:"
• The bottom one is labeled "For a login not defined in the list above, connections will: ..."

And the Help says, for the top area:

"Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login."

This all sounds like this page is for declaring how access will be handled when people use the Linked Server object.  And that matches what we expect, namely that security for the administrator of an object is handled separately (and usually before we're doing the administration).

However, the GUI, the Help, and our expectation don't match reality.  If you don't include here a way for SQL Server to authenticate you, the admin, with the remote server, you'll get a 7416 error, "no login mapping exists".  I kept running into this until I read the following sentence in the Help (which is ambiguous because we don't know to whom it applies):

"When connecting to the local server using Windows Authentication (recommended), select Be made using the login's current security context to connect to the remote server using the same Windows Authentication credentials." [3]

I tried this just to test it, and sure enough, I was finally able to create the Linked Server!  But if you do this, then anybody without a non-impersonation mapping in the top area of the page will be able to try to use the Linked Server.  It makes the list in the top area, which the Help says can be used to restrict access, rather moot.

So, if you want to create a Linked Server and restrict access to a specific user list, as the UI and Help portrays, then you must include yourself in the restriction list.  This is not at all obvious in the GUI nor the documentation, and constitutes a UX failure that cost me many hours!

A Final TCP/IP Complication

In order to create and use a Linked Server, your client software must be connected to the local SQL Server using either TCP/IP or named pipes.  And if you're using named instances, it's almost required (and recommended) to use static TCP/IP ports if you'll be linking through a firewall.  (Using dynamic ports means you'd be constantly updating firewall rules, depending on what firewall you use.)

But I've had difficulty connecting to the local server via TCP/IP in SQL Server Management Studio.  I've found that the following is required, whether logged in with a local machine account or a domain account:

1. Connect with SSMS using the host name via Shared Memory, click around a bit, then disconnect.

2. Immediately try to reconnect using the host name via TCP/IP.  It should now connect.

3. It may be necessary to try to connect to ".\instance" using Shared Memory before trying with TCP/IP.  One may also first have to try "IPaddress\instance".

4. Keep trying--it will eventually work!  (SSMS apparently ends up realizing the host name of the local machine & caches it.)

For some reason, SSMS has trouble knowing what the name of it's host computer is.  Why, I ask?  It's pathetic--even the user can type set computername to get the host name!

(I couldn't test if this is a network problem for SSMS because the network analyzer I use, Wireshark, can't view localhost packets on Windows machines.)

A lot of people report having trouble setting up Linked Servers.  I believe understanding the process of a solution is often better than blindly applying it.  Hopefully I've helped with that here.

References

1. "Registering a Service Principal Name", Books Online, SQL Server 2008 R2. Microsoft, 2010.

2. "Register a Service Principal Name for Kerberos Connections", SQL Server 2012, Microsoft. Web, technet.microsoft.com/en-us/library/ms191153.aspx, 2014.4.

3. "Linked Server Properties (Security Page)", Books Online, SQL Server 2008 R2. Microsoft, 2010.

© 2014 Thomas Knight(421)