You're also going to have to check the policy on password changes. If the password, for example, gets changed every 90 days, the application breaks because it can't connect. You're going to have to go into your web.config and change the password to the new one.

You can use a Windows account local to the SQL Server, but I have to agree that the best way is to join a domain. If your SQL login ever has to do anything outside of the SQL Server box itself, it's going to need a domain account and permissions to whatever network resources it needs to access. This isn't going to happen if you're using a local account.

Now, I don't have any idea what your application is or what your requirements are. You might not need any network permissions at all today, but you might need them tomorrow. Applications have a way of expanding in scope and functionality.