July 21, 2010 at 1:33 pm
I still have a couple of SQL 2000 systems. On one of these systems I have a few linked servers using Windows Authentication.
All of the sudden I am getting "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." on just one of the Linked SQL Servers. This server has been linked and running fine for years. Nothing has changed on either of the systems. I can connect fine from the other server (also as a linked server) to this one, just not the other way around.
I rebooted the server and it worked fine for about an hour and then I started getting the error again.
Any ideas why this would pop up out of the blue?
Microsoft SQL Server 2000 - 8.00.2055. Both SQL Servers are in a cluster.
July 21, 2010 at 1:53 pm
This should affect both, unless one has been setup for Kerberos authentication / delegation properly and the other hasn't. One instance isn't failing over to another node on the cluster is it? Any errors in the application event log? Are you seeing proper audit login events in the security event log?
K. Brian Kelley
@kbriankelley
July 21, 2010 at 2:06 pm
Not failing over to another node. I am not seeing any errors in the event viewer or sql error logs of either system.
All of the SQL Servers here (about 25 production) all use kerberos.
All of the other linked servers are fine. Also the fact that I can run a query from the other system to the one that is having a problem is odd. You think it would break in both directions.
This just started after 5 years of no problems.
July 21, 2010 at 2:11 pm
When you check the Security event log on the physical node where the SQL Server instance is failing, can you correlate a successful Kerberos login in the OS' security event log?
K. Brian Kelley
@kbriankelley
July 21, 2010 at 2:36 pm
I do see things like this:
Authentication Package:Kerberos
I tried to link the server from another one and got the same error. I think I am going to reboot the server tonight and see if that fixes this.
I can't think of any reason why this would happen unless something just crashed on the server.
July 22, 2010 at 8:41 am
Here is the situation. I rebooted the server and I still have the problem.
To make this a little more clear:
I have one SQL Server, Server A.
I have another SQL Server, Server B.
Server A is a linked server from Server B.
When I log on to Server B I cannot run queries against Server A without getting the error. It is not even making it the one hop from server to server.
I also tried linking Server A from another server, same result.
After rebooting Server A, no change.
I rebooted Server B and the error went away. However, that only lasted a short time and with in one hour I had the error again.
I can also get the error by opening EM from Server B and expanding the linked server and clicking on Tables.
This one has me a bit baffled. I am going to talk to our server and network guys to see if they have any ideas.
July 22, 2010 at 9:06 am
With Kerberos in play, you've actually got a two hop situation, even if you're local. Kerberos keys on the name. So...
if you are talking about a cluster, here's how things actually resolve out.
SSMS runs on PhysicalNodeA
SSMS connects to VirtualNodeA SQL Server Instance
This is technically one hop in Kerberos terms. It is not one hop in NTLM terms.
So when you go linked server from VirtualNodeA to VirtualNodeB, you have:
Kerberos: two hops now
NTLM: only one hop
How are the SPNs being registered? Were than manually registered or are they automatically registered by SQL Server?
K. Brian Kelley
@kbriankelley
July 22, 2010 at 9:23 am
This sounds like an Active Directory/domain replication problem, but I can't back that up with any real facts. I say it because I experienced something similar: one day we could authenticate to a server thru LDAP, then minutes/hours/days later we could not. Our fix was to change the port we were calling from our LDAP app to the Global Catalog port, instead of the LDAP protocol port. We could fix it temporarily by running 'newsid' from the problematic machine, too.
My limited understanding of AD is that the computer "signs on" to the domain controller with it's authentication "key" (which is made up of the 'sid', the hostname, IP etc)...for some reason that gets mucked up. Again, this is all conjecture and may not be factual.
Dunno if I've added anything useful.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
July 22, 2010 at 9:50 am
We are looking at that now. The SPNs should be generated by the service account that runs SQL Server when the system is restarted.
July 22, 2010 at 9:59 am
A side note. This started happening one day after the server was patched.
July 22, 2010 at 10:14 am
Do you have an option of rolling back the patches? If you can and do and everything is fixed, sounds like a call on Microsoft's dime.
K. Brian Kelley
@kbriankelley
July 22, 2010 at 10:33 am
I will look into that next.
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply