Web App getting Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

The problem here is you are using

<authentication mode="Windows">    </authentication>

This needs your browser to send NTLM credentials. Firefox does not send this by default.

When you on the the server and use localhost, your browser is sending your windows login credentials to the server. It is authenticating and giving access to the user, MyDomain\MyID.

ASP.NET impersonates the token passed to it by IIS, which is either an authenticated user or the anonymous Internet user account (IUSR_machinename).

All your web requests, that occur from machines that are not on that domain, will run under the anonymous account. In your case, NT AUTHORITY\ANONYMOUS LOGON

Your connection string is using, Integrated Security=True. That means the windows account under which the asp.net thread is processing must have access to the database too. If you want to pass the the windows credentials used to login to IIS you have to set, Trusted_Connection=Yes.

Refer: How to: Access SQL Server Using Windows Integrated Security

I suggest that you take a look at forms authentication, if you plan to expose this webservice on the web, or if you want to make it available to users who are not the same domain as your server.

I found that the issue for me was that in IIS I had Windows Authentication instead of Basic Authentication enabled. As soon as I switched to Basic Authentication, I was able to access the SQL Server under the logged in account.

In IIS, only Basic Authentication logs users on with a security token that flows across the network to a remote SQL server. By default, other IIS security modes used in conjunction with the identity configuration element settings will not result in a token that can authenticate to a remote SQL Server.

From: http://msdn.microsoft.com/en-us/library/bsz5788z.aspx

It sounds like you're running into what's called a "double-hop" issue, which is where the server is not being trusted to pass the client's credentials on to another box (hop 1 is the credentials to the IIS box, hop 2 is from the IIS box to the SQL Server).

When you're logged directly into the server, the second hop doesn't need to take place since it's just passing credentials directly from the client machine (the IIS server in this scenario) directly to the SQL Server. Likewise, if the SQL Server lived on the IIS box, you wouldn't have this error either, since the client would only be making the one request to a box that could share the credentials with both IIS and SQL Server.

There are quite a few steps required to get the delegation to work, such as trusting the servers for delegation, creating SPNs and making sure that other proper permissions are given to the account that IIS is using to run the web site. There is a technet article that can help take you through a lot of the required steps here: https://docs.microsoft.com/en-us/archive/blogs/taraj/checklist-for-double-hop-issues-iis-and-sql-server

Note: if you're using NTLM and not Kerberos (or another delegatable protocol), it will not work, as the middle server (the IIS server) needs to have a token that it can pass along. Since NTLM is based on negotiation, it won't work.

2020 Update: if you're starting to see this issue popup again, and it's only affecting Windows 10 users, or Windows 2016+ users, it's likely that "Credential Guard" is being enforced on your users' machines (see: https://docs.microsoft.com/en-us/windows/security/identity-protection/credential-guard/credential-guard-requirements). One of the things that breaks is Kerberos unconstrained delegation - so if this has happened to you, you'll likely need to reconfigure the middle box (the IIS server in the example above) to use constrained delegation instead of unconstrained delegation.