locked
Error: 18456 Severity: 14, State: 11 New Mystery RRS feed

  • Question

  • I've seen a lot of posts with this error already but none of them seem to address my scenario.

    I have multiple SQL 2005 clusters using mixed mode (Windows and SQL authentication) running in a Windows 2003 domain.  I get the Error: 18456 Severity: 14, State: 1 (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.) when I login to node 2 on a SQL 2005 cluster as a Windows Domain Admin and run a query against a remote SQL server in the same domain.

    When I login to node 1 on that same cluster and run the same query against the same remote SQL server it works without the error.  Here's where it really gets weird.  Then when I go back to node 2 and re-run the query it works fine.  Me and two other users have been able to reproduce this problem on this particular node against multiple remote SQL servers in the same domain.

    Windows sees the connection as Anonymous and successful but SQL throws the error.  I understand State 11 is a "server access failure" but what does that mean.  This is a new problem and the it just occurs from node 2 on that cluster.  We recently updated the clusters to SP3 but problem hasn't gone away.

    I would very much appreciate any help with this mysterious problem.

    Thanks

    Friday, February 4, 2011 6:11 PM

All replies

  • what do you mean by remote SQL server? are you trying to query another sql server (not in your current cluster) via linkserver?

    Sevengiants.com
    Friday, February 4, 2011 6:54 PM
  • Can you try connecting using the Named Pipe? I mean go to the error log of remote SQL Server -> Note down the pipe which should be like \\.\pipe.... -> Use this in the ServerName field in SSMS

    This attempts to use NamedPipe protocol so that it will authenticate using NTLM instead of Kereberos. This will isolate whether the issue is because of KERBEROS vs Named Pipe.

    For further reading:

    http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx


    Cheeeeerrzzzz!
    S@kthi
    http://blogs.msdn.com/sqlsakthi | http://twitter.com/PowerSakthi

    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.
    • Marked as answer by Alex Feng (SQL) Monday, February 14, 2011 10:13 AM
    • Unmarked as answer by Cameron123 Monday, February 14, 2011 5:14 PM
    Sunday, February 6, 2011 2:14 PM
  • Thanks for the replies.  We did some further troubleshooting and have some more information.  Also, we were told that this was a new problem but we're not so sure that's true - maybe newly noticed instead.

    We tried running our query from each node on multiple clusters all via linkserver.  In addition, we ran the query to different SQL servers and on different databases.  We discovered that every time we ran the query from a (active) node that was hosting the SQL resources of that particular cluster it worked.  When we ran the query from the (passive) node that was not hosting the SQL cluster resources the query would fail with the error.  So the problem isn't isolated to just that one node we originally ran the query from.

    Our attempt to run the query using Named Pipes failed with could not find server.

    What is happening in the background for it to work from an active node and to fail from the passive one?  And why does it start working on the passive node once the query is first ran from the active node?

    Thanks

    Thursday, February 10, 2011 11:08 PM
  • Cameron,

    It is confusing that:

    1. You ran query via linkserver from active node, no problem. How would you run query via linkserver from passive node?

    2. Youw will have to give us your query and your infrastructure setup, servers,clusters, cluster resource groups, etc..


    Sevengiants.com
    Friday, February 11, 2011 11:49 AM
  • Hi,

    I am working with Cameron on this.

    Here is what we have

    2 W2K3 R2 Enterprise x64 2005 MSSQL servers in an active/passive MS cluster. Server names: NODE1, NODE2. SQL SERVER NAME: NODEVSQL01

    NODE1 currently hosts the SQL cluster resources. I login to the primary server in the cluster (NODE1) open management studio and connect to NODEVSQL01 with Windows Authentication. We have several other linked SQL servers (hosted on seperate hardware and clusters) defined in management studio. If I attempt to run a query from management studio against a database on one of those linked SQL servers. The query executes successfully when run from NODE1.

    NODE2 is the passive node in the cluster. We have some DBA's that will login to NODE2 and open management studio and connect to SQL server NODEVSQL01 using Windows Authentication. The DBA will usually login to the passive node and open management studio when all 3 RDP sessions on NODE1 are used up. Now, running the same query on NODE2 that was run in management studio on NODE1 will return the error 18456 Severity: 14, State: 1 (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.) The SQL error logs show Severity: 14, State: 11 which indicates a server access failure.

    A couple of additional things.

    1. If you run the query from management studio on the passive node first it will fail with the error indicated. If you run the query from the active node, which will always work successfully and then run the same query on the passive node it will work on the passive node.

    2. The query will always run successfully on the active node in the cluster. So if I failover the cluster resources to NODE2 the problem will reverse. NODE1 will return the error if I attempt to run the query there before running it on NODE2.

    3. All Windows authentication accounts are local admins on the NODEVSQL01 cluster and all the linked SQL servers we attempt to run the query against.

    4. When the query fails, the remote linked server will log an entry in the application log with error 18456 Severity: 14, State: 1 (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.) This is confusing to me since we are using a domain account to open managemnt studio and run the query on NODEVSQL01.

    I hope this information helps. Let me know if you need more.

    • Marked as answer by Alex Feng (SQL) Monday, February 14, 2011 10:13 AM
    • Unmarked as answer by Cameron123 Monday, February 14, 2011 5:08 PM
    Monday, February 14, 2011 6:13 AM
  • Please do not mark this post as answered yet.  MichaelW456 and I work at the same organization.  I was unable to reply to the post so he replied for me.  He was just providing the additional information that SevenKnights was asking for.

    This is still an outstanding issue for us.

    Thank you.

    Monday, February 14, 2011 5:13 PM
  • We also encountered a problem like that in an SQL 2008 Cluster, two instances on two Nodes, Kerberos configured on both of them identically. But it was impossible to create a linked Server from one to the other as one of them would just make connections via NTLM while the other used TCP/Kerberos.

    We applied a Hotfix on all our Cluster nodes http://support.microsoft.com/kb/982549/en-us which was probably part of the solution, but the linked Server was still not working then.

    What finally solved our problem was simply deleting the SPNs and recreating them... the same way they were before but then it worked.

    Thursday, May 26, 2011 2:12 PM
  • I'm having the same problem and losing my mind over it!~
    Monitoring this post..
    Monday, October 17, 2011 3:18 PM