"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." to SQL Server 2005

Question

I'm trying to migrate a legacy application we have to Windows Server 2008 x64 and IIS7. It's written in Classic ASP and connects to a SQL Server 2005 database.

However, when the page runs, I receive the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The connection string I'm using is: Driver=SQL Server; Server=SERVERNAME; Initial Catalog=DBNAME; I can't see any reason for it to be using the anonymous logon as when it was running on my 32-bit Win2k3 server, it accessed the SQL Server using DOMAINNAME\SERVERNAME$.

I have the following settings.

SQL Server 2005 - running in mixed mode. IIS7 Application Pool - Allow 32-bit applications set to True.

I've also added the server as a user on the SQL Server.

I've tried a few things now and I'm starting to run out of ideas.

Answer 1

There are some solutions.

use SQL authentication, instead of SSPI. It will work since your database is in mixed mode.

If for any reasons you won't change the authentication.

The NT AUTHORITY\ANONYMOUS LOGON is strange, by the way, just as if the database server and the app servers are not on the same domain. Please check this until proeding further.

1) Create an user in Active Directory
2) Create a application pool on IIS7 and as "Identity" , the user you've created
3) Grant this user the rights and roles you want on the DB

Answer 2

I think I've fixed it. Moving to Win2k8 means that I needed to change the connection string driver to SQL Native Client;.

Answer 3

Set the Anonymous Authentication credentials to be your Application Pool Identity.
(default is Specific User --> IIS User). Then you can use a Trusted Connection in your connection string as it will now use the AppPoolIdentity account.

Answer 4

Well, that failed login attempt is a classic example of code running under the "Local Service" account, rather than (what you had on Win2K3), where it was running under the "Network Service" account. Now you just need to find where that setting is.

Answer 5

For either the OP or future readers, you can:

1) Either use a dedicated service account, as suggested by Johan above, or

2) Use the AppPoolIdentity account and go to "Authentication", then select "Windows Authentication" and enable "ASP .NET Impersonation".

If you still get the same result after #2, you likely have the "double hop" issue cited by Damien - this is where the web server can authenticate you, but it does not allow you to pass those credentials on to the database for security reasons without using Kerberos. You would then need to set the server as "Trusted for Delegation" in AD and set up Kerberos as the authentication provider in IIS. This site helps with that: http://technet.microsoft.com/en-us/library/ee675779.aspx