The focus for my answer was actually based around the phrase "the absolute need to have SQL Server Logins". Are they truly necessary? In the vast majority of cases I have worked on, an AD-based account was quite capable of doing the job of the SQL Server account. Where it wasn't capable of replacing it, it was only because the software had been hard-coded NOT to accept AD credentials.

One of my first questions to vendors is whether or not an SQL Server login is necessary. If so, why? Normally the answer is one that offers me the ability to create an equivalent AD-based login. An unfortunate fact as well is that a lot of vendors tend to understand their software and their own underlying database but not the server on which it runs and the security models available.

So in answer to my own question "are SQL Server Logins truly necessary"? I would say very rarely.