We have applications connected to SQL using windows authentication. While having connection with Application user can also access to Database instance on the same time as well. We need to limit the access of user outside application. How we can limit user to access DB from application but not outside application?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
The first thing that comes to mind is a logon trigger. But that's not really the right answer. The right answer is to give the logins (your Windows users) only the permissions they require. An application is not a security layer and you shouldn't be treating it as such.
In other words, if the users are able to
Does your application need to directly access the data tables? If the application only uses Stored procedures, then I'd restrict the user to those - that way direct table access is avoided.
However, if you can change the application, I would suggest you investigate Application Roles - These can solve exactly the problem you have, that the application should have more privileges than the user alone. The user has little more than connect access, but when the application connects it runs a stored procedure with a password to gain the additional permissions it needs in the database.
There's a nice walkthrough here.
The general principle of giving users the minimum necessary permission to do their job applies here - don't give the Application more than it needs, either.