Regarding SQL logins, I think the point here is to use them as a last resort when no other options are available.

In a web server/db server example, you could only allow the web server to communicate with a 'data broker' https web service, and then use windows authentication to permit that broker to connect to the SQL database. You could then use a client cert to secure the data broker web service, and only load that cert into the account that runs your app pool, making sure your website is the only website in that app pool (IIS solution).

But yeah with a vendor supplied solution you can often be left with no other option.