Security model (deployment) for MS Access application with SQL Server Backend

Question

We have an application, consisting of an MS Access frontend (2007, mdb format), a few .net libraries and an SQL Server (2008) backend. I am working on an installer, which automatically installs the MS Access Runtime, our application, our libraries, SQL Server Express and configures everything.

Clearly, the MS Access application and the libraries (running in a normal, non-admin user context) need access to the SQL Server database. What is the best way to grant access to the application?

---

This is what I came up with. Unfortunately, all of these seem to have drawbacks:

• SQL Server Compact Edition: Does not support views.

• Application Roles: This seems to be best practice. However, it requires executing a stored procedure before accessing the database (I cannot pass the app credentials in the connection string). Thus, I cannot use this to attach the SQL Server tables as a linked tables in the Access MDB, which is a requirement of our Access application.

• SQL Server User Instance: To quote from MSDN: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work..."

• SQL Authentication: Microsoft says: "When possible, use Windows Authentication."

• Using Windows authentication and granting BUILTIN\USERS full access: This is by far the easiest solution, but somehow it "seems wrong" to do that...

The application is targeted at a non-technical audience, so asking the user to configure permissions is not an option.

EDIT: Some clarification: It's a "local" application, i.e., the SQL Server is located on the same machine as the application; SQL Server access from the network is neither necessary nor desired. The software (a regular business application for managing stocks, invoices, etc.) will be available to download for free, so it should run in a variety of environments (domain/non-domain, different operating systems, etc.), and IT knowledge should not be required to install it -- apart from the usual "click on setup.exe, confirm UAC prompt, acknowledge the installation directory, etc.". I expect the most common scenarios to be "Windows XP, local admin user" and "Windows Vista/7, local admin user with UAC enabled". Since we want to follow good practices, running the application should not require "Run as Administrator" in the latter case.

Answer 1

@Heinzi write:

Using Windows authentication and granting BUILTIN\USERS full access: This is by far the easiest solution, but somehow it "seems wrong" to do that...

The usual approach here is to add a custom user group (e.g., "db-users") and put the users in that group. That way you can control exactly who is allowed access.

Answer 2

How about:

• Use an Access ADP project, pre-configured to connect to the locally installed SQL Server instance.
• Connect using BuiltIn\Users group (or SQL authentication) but grant only the bare minimum credentials. Enough to logon and ...
• Call sp_setappprole to "elevate" the client connection to your defined application role's identity.