When working with IIS 7.5 and SQL Server 2008 R2 in a domain environment, many developers encounter the frustrating "Login failed for NT AUTHORITY\ANONYMOUS LOGON" error. This typically occurs when:

• Using ApplicationPoolIdentity as the IIS app pool identity
• Connecting to a remote SQL Server instance
• Having Anonymous Authentication enabled in IIS

The core issue stems from Windows authentication and credential delegation. When using ApplicationPoolIdentity:

IIS (AppPoolIdentity) → SQL Server → Gets translated to ANONYMOUS LOGON

This occurs because the credentials can't be delegated across machines by default (the "double hop" limitation). Network Service works because it uses the machine account, while domain accounts have explicit delegation rights.

Option 1: Use SQL Authentication

The simplest workaround is to use SQL Server authentication in your connection string:

<connectionStrings>
    <add name="MyDB" 
         connectionString="Data Source=sqlserver;Initial Catalog=mydb;User ID=sqluser;Password=password;" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

Option 2: Configure Kerberos Constrained Delegation

For Windows authentication purists, configure constrained delegation:

1. Open Active Directory Users and Computers
2. Find the IIS server computer account
3. Go to Delegation tab and select "Trust this computer for delegation to specified services only"
4. Add the SQL Server service (MSSQLSvc)

Option 3: Use a Domain Service Account

Create a dedicated domain account for the app pool:

# PowerShell to set app pool identity
Import-Module WebAdministration
Set-ItemProperty "IIS:\AppPools\MyAppPool" -Name processModel -Value @{userName="domain\svc_account";password="P@ssw0rd";identityType=3}

Here are some working configurations:

// Windows auth with explicit credentials
Server=sqlserver;Database=mydb;Integrated Security=SSPI;User ID=domain\user;Password=secret;

// Trusted connection (when delegation works)
Server=sqlserver;Database=mydb;Integrated Security=SSPI;Trusted_Connection=True;

// Encrypted SQL auth
Server=sqlserver;Database=mydb;User ID=sqluser;Password=password;Encrypt=True;

Use these tools to diagnose authentication issues:

• SQL Server Profiler: Monitor login attempts
• Event Viewer: Check security logs on both IIS and SQL Server
• Setspn: Verify SPN registration

Remember to test connectivity after each configuration change using a simple ASP.NET page:

<%@ Page Language="C#" %>
<script runat="server">
    void Page_Load() {
        try {
            using (var conn = new System.Data.SqlClient.SqlConnection(
                ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString)) {
                conn.Open();
                Response.Write("Connection successful!");
            }
        }
        catch (Exception ex) {
            Response.Write("Error: " + ex.Message);
        }
    }
</script>

---

When using ApplicationPoolIdentity in IIS 7.5 to connect to SQL Server 2008 R2, you might encounter the frustrating "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" error. This occurs despite proper domain configuration and SQL Server permissions being set up correctly for the machine account (IIS APPPOOL\AppPoolName).

The fundamental issue stems from how Windows handles authentication between different machines when using ApplicationPoolIdentity. Unlike traditional service accounts, ApplicationPoolIdentity has special security characteristics:

• It's a virtual account that exists only on the local machine
• By default, it can't authenticate to remote resources using Windows authentication
• The authentication attempt gets "stripped" to anonymous when crossing machine boundaries

Here are three proven approaches to resolve this authentication issue:

1. Use a Domain Account Instead

The simplest solution is to configure your application pool to run under a domain account that has SQL Server access:

// In IIS Manager:
1. Open Application Pools
2. Right-click your pool → Advanced Settings
3. Under Process Model → Identity
4. Select "Custom account" and enter domain\user credentials

2. Configure SPNs and Delegation

For environments requiring ApplicationPoolIdentity, you'll need to configure Service Principal Names (SPNs):

// Command line steps:
setspn -S HTTP/webserver.domain.com domain\webserver$
setspn -S HTTP/webserver domain\webserver$

// Then configure constrained delegation in Active Directory

3. SQL Server Authentication Fallback

When Windows authentication isn't mandatory, consider using SQL authentication:

// In your connection string:
<add name="MyDB" 
     connectionString="Data Source=sqlserver;Initial Catalog=DBName;
     User ID=sqluser;Password=password;" 
     providerName="System.Data.SqlClient" />

For enterprises requiring strict Windows authentication, proper Kerberos configuration is essential:

// Check existing SPNs:
setspn -L domain\webserver$

// Typical output should include:
HTTP/webserver.domain.com
HTTP/webserver

Ensure the SQL Server service account has proper SPNs registered as well:

setspn -L domain\sqlserviceaccount

• Use klist to verify Kerberos tickets are being properly issued
• Check Event Viewer on both IIS and SQL Server machines for authentication-related errors
• Enable SQL Server logging to capture detailed authentication attempts

When troubleshooting, pay special attention to your connection string parameters:

// Good practice for Windows auth:
Server=sqlserver;Database=MyDB;Integrated Security=SSPI;
Persist Security Info=False;Trusted_Connection=Yes;

Notice the explicit SSPI and Trusted_Connection parameters which can affect authentication behavior.