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?
2 Answers
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 SELECT and INSERT into dbo.Table1, then they only need those permissions. Don't make them members of the sysadmin fixed server role in the hopes they only hit the database through the application.
answered Dec 3, 2013 at 23:13
-
Thanks Thomas for your response on my problem. I think i am unable to explain my problem. Let me repharse it so that you can better understand my problem. I have develop an application which is connecting to SQL DB using windows Authentication and its working perfectly fine. What i want is to limit the access of the User to SQL management studio and may be some other application to access my Database. May be they can connect to SQL but cannot access (Read/Write) to my Database. I hope i have now explain my problem clearly. Sorry to bother you again. Thanks once Commented Dec 3, 2013 at 23:22
-
2@user3055599: Thomas answered perfectly exactly your question, and your clarification does not bring anything new. You just don't want to hear the answer. Commented Dec 4, 2013 at 10:55
-
Thanks Remus for your comment. I am figuring out some other way around of doing this. and i found that LOGON TRIGGER may resolve my problem. Commented Dec 4, 2013 at 19:55
-
@user3055599 It doesn't resolve your problem. A SQL-savvy person can easily change the
Application Nameparameter in the connection string to mimic what your application's name is, thereby forcing a form of impersonation, and getting past the logon trigger with SSMS, or whatever client they have the ability to control the connection string with. Commented Dec 4, 2013 at 20:00 -
You recommend is to go with setting up some application roles to achieve it? Commented Dec 4, 2013 at 21:30
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.
