I’m looking for either a solution, or a definitive answer that what I want to do can’t be done.
My situation:
I have a MS Access Front End that is located in a Citrix Store Front.
The Front End connects to a 2017 MS SQL Server Backend.
The server backend is configured to use Row-Level-Security as described in this article.
Out of the Citrix Store Front, my Access Front End connects to the server, but the row-level-security only recognizes the user and password that I send via the access connection string, not the user that opens the Access Front End. This makes sense to me, since I’m passing a User ID and Password via the connection string.
The idea of having the MS Access Front End in the Citrix Store Front, is that our IT does not want to open up the firewall for each individual user, and that thus we can use one connection string for everyone.
My Question:
Given that this is the case, can I still implement Row-Level-Security, or is it not possible under the one connection string scenario?
Best Answer
The only way you have here is to use INTEGRATED SECURITY in the connection string.
In this way the connecting login is also used against the database but if there is a firewall between database and frontend that recognize windows authentication (it filters in application layer i suppose) every users need to be included in the allow rule. Usually, it is easily done using domain groups.