AppPoolIdentity (Default) works because it uses the web server's machine account for delegation.

When you configured constrained delegation in AD, you likely did this for the web server's computer object (not the domain service account). This allows the machine account to "forward" the user's identity to SQL Server.

Your Domain Service Account isn't working because:

Constrained delegation isn’t configured for it – You set delegation for the web server, not the domain account.

Double-hop limitation – When using a domain account, you must explicitly allow it to delegate credentials to SQL Server via:

AD Delegation Settings: Mark the domain service account as "Trusted for Delegation" to the SQL Server’s SPN (MSSQLSvc).

Correct SPN Binding: Ensure the SQL Server’s SPN is properly registered in AD.

Fix:

Configure constrained delegation directly for the domain service account (not the web server) to the SQL Server’s SPN. This tells AD: "This service account is allowed to forward user credentials to SQL Server."