Sql-server – Server 2012 Linked Server From An Instance

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

"

A number of comments asked if Kerberos was configured correctly, but I do not see a direct response to that issue. Based on the comments that you make I suspect Kerberos is not set up correctly.

Greatly simplified description:

1. If UserAccount is directly logged onto Server A then a linked server to Server B is one hop away and the UserAccount's authentication will be recognized on Server B. This works with or without Kerberos delegation.

2. Is UserAccount is directly logged onto his desktop and accesses Server A then Server A is one hop away from the desktop and the UserAccount's authentication will be recognized on Server A. However, if from the desktop he runs a query against Server A that uses a linked server to access Server B that is two hops away from his desktop. Without Kerberos (or some similar service) the UserAccount's delegation cannot make the second hop and, despite the rights granted on Server B, will not authenticate on Server B.

This is all about how NTLM and Kerberos work. There is a blog from a few years ago that offers some details on Linked Servers and NTLM or Kerberos authentication:

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

One edited quote is: "In a single-hop setting, windows NTLM authentication, which is available if all machines are windows, is sufficient for delegation; while in a double-hop setting, Kerberos authentication is necessary for the user’s credential to cross machine boundaries from the client to the linked server."

Now, suppose that you do indeed have Kerberos configured properly and still are mystified by the behavior. In that case you might need to check into whether there is a duplicate SPN. If so that will need to be corrected.

The following blog discusses duplicate SPNs and other Kerberos issues: http://blogs.technet.com/b/askds/archive/2013/07/01/interesting-findings-on-setspn-x-f.aspx

Note: Some people just fall back to using Named Pipes instead of TCP/IP, but this is not a recommended solution any more.

Bottom line: Resolve the authentication delegation problem and everything should work.