I'm going to preface all this by saying I'm making the assumption that you're talking about an internal web server on the internal private network.
Let's start with the impersonating the machine. If the application pool identity is Network Service and there is no impersonation in the .NET application, then yes, the web application will connect to the back-end SQL Server using the machine's computer account. And that would mean you've granted access to said machine account. Microsoft's CRM works this way.
However, if you've specified an identity, that user account will need access to the SQL Server. While you're right that if an attacker compromised the web server they effectively have the same access as the identity account, the truth of the matter is that using a SQL Server logon doesn't change anything here. Once I have access, I can modify the web application to do what I want and it will, to the maximum your security permits on the back-end SQL Server.
Now as to why to use SSPI. First and foremost, you aren't using a SQL Server based login. That means Active Directory is the sole source for security. That means you have the normal audit means to determine invalid access. Second, it means that unless there are other apps that require it, you can leave your SQL Server in Windows authentication only mode. That means no SQL Server logins are permitted. That means any attacks against sa are stopped before they even start. And finally, it makes recovery easier. If you use a SQL Server based login you'll need to extract the login with SID and encrypted password. If you're using a Windows based user account as a "service account," when you go to a new SQL Server, by creating the login, everything will be reconnected once you restore the database because the SIDs will match between login and database user.