In my view Windows Authentication is still more secure. Just.
With windows authentication you can allow a group of people (via a Windows Group) to have access to the database. Only those users can use an application, any application, to connect to the database.
If you use a SQL Server account then anyone who knows the credentials (and invariably these always leak out) can connect to your database. Again with any application.
You are absolutely correct that with Windows authentication can connect directly to the database and issue any query they see fit - due to the model your app requires.
A third option
What I would suggest in this situation is the use of an application role: http://msdn.microsoft.com/en-us/library/ms190998.aspx
The problem you have here is you have to be able to code the application to be able to use the application role - that's not always easy. Afraid I'm not an Access expert to be able to advise here specifically.
The advantage of an application role is that you can restrict which applications can perform certain commands. Its similar to a database role but has a password assigned to it that is needed before the rights of the role can be inherited. Using an application role with a Windows group you could limit the users who can connect to a database and the application that they can use to query the database.
Risk mitigation would indicate creating a separate account for each service on each machine. The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations.
Microsoft Best Practices recommend using separate service accounts for all services.
See http://msdn.microsoft.com/en-us/library/ms144228.aspx#isolated_services for details.
The salient points being:
Isolate Services
Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:
Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Configure Windows Service Accounts and Permissions.
There is also a KB talking about securing SQL Server that mentions how to configure service accounts properly:
http://support.microsoft.com/kb/2160720
When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts.
Technet has an article, titled Configure Windows Service Accounts and Permissions at http://technet.microsoft.com/en-us/library/ms143504.aspx that has this to say:
Security Note: Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.
"MSA" in the above paragraph refers to "Managed Service Accounts" which is the default for installations on Windows 7 or Windows Server 2008 R2 and above. Managed Service Accounts are defacto unique to each machine.
As an aside, one issue I think about when configuring multiple servers to run under the same service account is account lockouts. If you use a single service account for all SQL Servers, and the service account gets locked out, all your servers might be affected. If you have one account per service, at most one server can be affected by a lockout.
Best Answer
The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user.
You could create the Windows login and the user (both having the same name) followed by script that copies the permissions from the SQL user to the Windows user. (Search for "sql server clone user permissions").