Currently our users authenticate into our application through terminal services, the application uses a single database user/login (basically just like an application role), and none of the users have direct access to the db.
It would be nice to have the users authenticate directly to the database with NT Authentication. More control for us. We can easily have the application authenticate each user with their NT info, but that exposes us to letting the users log directly into the db at will (unless we firewall such that only the terminal services servers can access the production db machine - inelegant).
The problem with having the application authenticate to the database, as is currently the case, is that the application must store the connection string with password. This works fine, but our developers can then derive the connection string/password to the production db server. And since the application does both read and write, that opens us up to a SOX hassle.
So what's the silver bullet here? We need a solution that says "User A can access the db but only through application B". Or one that allows for the use of an application role, yet somehow exposes the connection string and password to the application yet not to the developers who make the application.