“Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.” to SQL Server 2005

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.

From serverfault Liam

• Hi

  I think you are using the wrong database driver for your odbc connection. MS SQL 2005 uses the SQL Native Client.

  Driver=SQL Native Client; UID=username; PWD=password; Server=SERVERNAME; Initial Catalog=DBNAME;

  You can download the setup here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb

  Liam : Right, that sorted that error out. However, now I get a new error: [Microsoft][SQL Native Client][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

  Liam : That sorted it. Not ideal as we try to avoid having usernames and passwords in our files, but it does the job for now. Cheers.

  grub : Hi check the answer from brian. maybe you can remove the username and pw from the file if youre using the trusted connection.
  From grub

• I believe you need to add an attribute to your connection string that will allow the application pool account in IIS7 to authenticate on the SQL Server. See below:

  Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
  

  The Trusted_Connection piece will allow IIS to connect using the credentials of the app pool account. If that is running under the machine account, as you said, then the login you created at the SQL Server will work.

  From Brian Knight