July 15, 2008 at 7:30 am
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.
July 15, 2008 at 8:52 am
Block the users from the database with the firewall. More rules, not so elegant, but it solve the issue.
Right now, there's no way to change this. There have been suggestions to "authenticate the application, but I haven't seen plans to do this. You could submit it on Connect and see if you can get people to vote for it and have it in SQL 11.
July 15, 2008 at 8:57 am
Thanks, Steve. At least now I know that I'm not missing something that would invoke the "durr" response 😀
July 24, 2008 at 2:02 pm
Actually, we figured out a way.
The application holds the application role connection string with an encrypted password. The decryption key is placed in a table on it's own database that is not on a backup schedule. This prevents the developers from casually obtaining the application role rights.
Application user accounts are limited to reading the decryption key, then initiating the application role that grants the necessary production rights. Developer user accounts cannot access the decryption key database.
This obviously doesn't address collusion, where an application user passes the decryption key to a developer, but that's a whole other can of worms.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply