The web site, it'll be on the Internet? Generally, unless you're using something like ISA server, it's not a good idea to allow your web server to be a member of a domain. Therefore, you don't have the option of Windows authentication unless you create a local Windows account on the SQL Server that matches a local Windows account on the web server. Even with that said, most of us who are paranoid still require web servers accessible from the Internet to be in their own workgroup, ISA server or not. In most of these cases, SQL Server logins are the easiest to deal with.

Some (Tim Mullen, aka thor from HammerOfGod and the Windows security columnist at securityfocus.com) would say make the SQL Server in a workgroup and stick it in the DMZ as well... pushing data to it that the web site needs to access. This ensures that should the web site be compromised (a matter of when, not if, in current security thinking), you know the SQL Server will be compromised as well. If neither box is on the domain, an attacker can't use either to hop directly to another server with domain credentials. With proper ACLs on the firewall, routers, etc., it's even more unlikely this can occur.