"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

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 3

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