I have a client that's insisting on deploying SQL Server 2000 as Windows Authntication mode only (not mixed mode). I've always done mixed mode in the past and I'm just looking for some input here. So, what's everyone think?
Either form of authentication will do. If NT Authentication will do everything that the client needs, it is certainly a workable solution.
NT Authentication is more secure than SQL Authentication, but it means that you need better "digital plumbing" to make it work, especially over a WAN. You need more bandwidth, more complex router/link settings, a more capable firewall, etc. If you have these things, and can absolutely rely on them, then NT Authentication is simpler and safer than SQL Authentication from a SQL user/administrator perspective.
I suppose my assumption was that I'd issue access based on Windows Auth, but I had intended on leaving it in mixed mode for those times when sa has to step in. Should I ignore SQL Auth altogether, or leave sa as the only SQL Auth user as an "in case of emergency break glass" user?
As long as you have an NT Admin, you really don't need sa for much of anything. The only case I can see where you might want sa is if you need to dial in remotely, and can't support NT Authentication. That is a considerable stretch of the imagination, and if you have VPN access or an on-site administrator it isn't even relevant.
I have only one small problem with the Windows Authentication. Suppose you have a Web server that runs several websites. All of those websites would have to log into the SQL Server as a single account under Windows Authentication. Namely, the windows account that the web service runs under (at least, with my simple understanding of IIS). This has the rather unfortunate effect of making all of the databases only as secure as the least secure website. If you have a single page on a single one of these websites that allows SQL Injection, then all of the security on all of the other websites is quite simply cooked. Microsoft has a very unsettling attitude towards security on SQL Server. They keep banging the "Least Privilege" model drum, but put out applications like SMS and Sharepoint that blatantly break that model.