question

Mats-6626 avatar image
0 Votes"
Mats-6626 asked ErlandSommarskog answered

How to leverage SQL Server Application Roles in MsAccess?

Does anyone know if there is a way to use SQL Server Application Roles from within Ms Access?

I have an Ms Access application with tables linked to SQL Server. Users of the application can access and manipulate data in the linked tables according to their role in the application. I have also granted users access to the tables on SQL Server, since users authenticate via integrated windows authentication. This however means that the users can also connect to the tables on the server directly, bypassing the protection implemented in the Access application, which is of course not ideal. The Application Roles feature in SQL Server seems to offer a nice solution for this problem, however, there seems to be no direct support for this feature in Ms Access.

An option I was considering is to create a proxy ODBC driver which activates the application role using credentials passed to it in the connection string. However, I have no previuos experience with developing such a solution, so am interested to here what other ideas might be out there!

sql-server-generaloffice-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I don't know Access, so I can't say where in Access you would put in the application roles.

However, I like to point out that you can only achieve security-by-obscurity this way. The password for the application role must be available from the Access application, which means that users how know their ways can find it, and then employ it from SSMS.

If you want to achieve a fully secure solution, where users only can access the database through the application, you need to have three tiers, so that the middle tier connects to the database in a way that is not available to the user. (This can be achieved in a number of ways, both with networking and application users.)

Now, Access and three-tier application does not really go together, although you could put the Access application as a Remote Desktop application.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.