0

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?

4
  • 3
    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. Commented Jan 23, 2015 at 16:41
  • 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? Commented Jan 23, 2015 at 16:45
  • 4
    Have you tried the SQL Server Kerberos Configuration Manager? microsoft.com/en-us/download/details.aspx?id=39046 Commented Jan 23, 2015 at 16:47
  • @spaghettidba beat me to it! Kerberos Config Manager is amazing, and I highly recommend it to customers when dealing with Kerberos. Commented Jan 24, 2015 at 3:44

1 Answer 1

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.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.