linked server failure but agent jobs keep running ok

Question

Had an issue today where a linked server pair (SQL 2005/SQL2008R2) started failing for linked queries generated by users in their Management studio, but the sql agent jobs (which also used this link) all still kept working fine?

Also within SSMS, the link definition connection test failed, and we couldnt get it to succeed with the standard domain account we had been using for the link without issue for a few weeks.

We couldnt find the root cause, so we had to switch the target server to mixed-mode and create a sql account to get around the issue.

We changed the target SQL server service account from local to a domain account the night before, but dont believe this to be related - the link failure errors (trying to revert to anonymous authentication) began around the middle of the next day.

We switched the target server service account back to its original, but this still didnt fix the link-fail issue. Only switching the SQL server to mixed-mode and using a SQL authenticated account did the job.

We did get an SPN fail message in the log for the new service account on the target server. This disappeared when we switched back to the local service account.

The AD service account was checked by IT and it looked like its SPN was registered ok.

Anyone had something like this happen?

Answer 1

Not much help, but some thoughts:

When the Database Engine starts, if attempts to register its SPN with Kerberos. See Register a Service Principal Name for Kerberos Connections http://msdn.microsoft.com/en-us/library/ms191153.aspx Changing the account used by SQL Server to a domain account, probably caused that to fail because the domain account didn't have sufficient permissions to register the SPN. When connecting to SQL Server, Kerberos authentication failed. You can confirm that with: SELECT net_transport, auth_scheme FROM sys.dm_exec_connections;

At that point the logins should have attempted NTLM authentication, which presumably would succeed. And so you wouldn't have a problem.

So I don't know what happened. Maybe it timed out because the process took too long. (??) Any other interesting errors in the Windows Event log? Of course, now you have resolved the SPN issue so ...

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Thanks Rick, i will check the event logs and see what more clues i can uncover - also sounds to me to be an SPN/kerberos authentication issue.