Server 2012 Linked Server From An Instance

Question

I have 3 SQL Server 2012 Instances, 2 on SiteA on the same server SiteAServer. I have 1 on SiteB on server SiteBServer.

SiteADefaultInstance, SiteASecondInstance
SiteBDefaultInstance

• SiteBDefaultInstance can create a linked server to SiteADefaultInstance and SiteASecondInstance.
• SiteADefaultInstance can create a linked server to SiteBDefaultInstance.
• SiteASecondInstance CANNOT create a linked server to SiteBDefaultInstance.

I get the error

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON"

I am setting the security to be made using the current context.

I have ran setSPN for SiteAServer and SiteBServer. You don't have to change the commands if you have more than one instance do you? I am in the role sysadmin.

I have never experienced where it only worked one way before. Any suggestions where to look?

• Sean Gallardy: Definitely an issue with Kerberos. "You don't have to change the commands if you have more than one instance do you?" it's a PER SERVICE setup, so yes you need to change the values per service.
• John Wesley Gordon: setspn -U -A MSSQLSvc/SiteAServer:1433 user and setspn -U -A MSSQLSvc/SiteBServer:1433 user are the commands I ran. What is the correct way to specify the instance? setspn -U -A MSSQLSvc/SiteAServer\SiteASecondInstance:1433 user?
• spaghettidba: Have you tried the SQL Server Kerberos Configuration Manager? microsoft.com/en-us/download/details.aspx?id=39046
• Sean Gallardy: @spaghettidba beat me to it! Kerberos Config Manager is amazing, and I highly recommend it to customers when dealing with Kerberos.

Answer 1

Comments were very useful in leading to the solution. After running SQL Server Kerberos Configuration Manager I saw that while the first was on 1433 for SiteADefaultInstance and the second instance of the database was listening on a dynamic port for SiteASecondInstance.

Went into SQL Server Configuration Manager> SQL Server Network Configuration> Protocols for SiteASecondInstance> TCP/IP and change IPALL TcpPort to a different port, say 9999.

SetSPN -s "MSSQLSvc/SiteAServer:9999" "user"

After this could create a linked server.

• Jeff Sacksteder: The 'most correct' way to fix this is to grant the service account the ability to register its own SPNs. That way you never have any hard-coded ports.
• Jeff Sacksteder: blogs.technet.com/b/askds/archive/2008/06/13/…