SQL Server returns error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." in Windows application

67

An application that has been working without problem (and has not had any active development done on it in about 6 months or so) recently began failing to connect to database. Operations admins cant say what might have changed that would cause the problem.

The client application uses a hardcoded connection string with Integrated Security=True, but when the applications attempts to create a connection to the database, it throws an SQLException saying "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON".

I can log on to the database through Management Studio on this account without problem. All of the things that I have seen for this issue are for ASP.NET projects and it is apparently the "Double Hop Problem" which being a client application darned well better not be a problem. Any help would be greatly appreciated.

Edit

The client machine and server machine as well as user accounts are on the same domain. This occurs when Windows Firewall is off.

Leading theory is: Server was restarted about a week or so ago, and failed to register Service Principal Name (SPN). Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos.

This question is tagged with sql-server security sqlconnection

~ Asked on 2012-09-17 15:41:04

The Best Answer is


26

If your issue is with linked servers, you need to look at a few things.

First, your users need to have delegation enabled and if the only thing that's changed, it'l likely they do. Otherwise you can uncheck the "Account is sensitive and cannot be delegated" checkbox is the user properties in AD.

Second, your service account(s) must be trusted for delegation. Since you recently changed your service account I suspect this is the culprit. (http://technet.microsoft.com/en-us/library/cc739474(v=ws.10).aspx)

You mentioned that you might have some SPN issues, so be sure to set the SPN for both endpoints, otherwise you will not be able to see the delegation tab in AD. Also make sure you're in advanced view in "Active Directory Users and Computers."

If you still do not see the delegation tab, even after correcting your SPN, make sure your domain not in 2000 mode. If it is, you can "raise domain function level."

At this point, you can now mark the account as trusted for delegation:

In the details pane, right-click the user you want to be trusted for delegation, and click Properties.

Click the Delegation tab, select the Account is trusted for delegation check box, and then click OK.

Finally you will also need to set all the machines as trusted for delegation.

Once you've done this, reconnect to your sql server and test your liked servers. They should work.

~ Answered on 2012-09-18 03:00:30


7

First off: My problem isn't the exact same as yours, but this post is the first thing that comes up in google for the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error at the time I wrote this. The solution may be useful to people searching for this error as I did not find this specific solution anywhere online.

In my case, I used Xampp/Apache and PHP sqlsrv to try to connect to an MSSQL database using Windows Authentication and received the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error you described. I finally found the problem to be the Apache service itself running under the user "LOCAL SERVICE" instead of the user account I was logged in as. In other words, it literally was using an anonymous account. The solution was to go into services.msc, right click the Apache service, go to Properties, go to the Log On tab, and enter the credentials for the user. This falls in line with your problem related to SPN's as your SPN's are set up to run from a specific user on the domain. So if the correct SPN is not running, windows authentication will default to the wrong user (likely the "LOCAL SERVICE" user) and give you the Anonymous error.

Here's where it's different from your problem. None of the computers on the local network are on a Domain, they are only on a Workgroup. To use Windows Authentication with a Workgroup, both the computer with the server (in my case MSSQL Server) and the computer with the service requesting data (in my case Apache) needed to have a user with an identical name and identical password.

To summarize, The Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error in both our cases seems to be caused by a service not running and/or not on the right user. Ensuring the right SPN or other Service is running and under the correct user should solve the anonymous part of the problem.

~ Answered on 2015-07-10 15:01:02


Most Viewed Questions: