Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
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 –  user3055599 Dec 3 '13 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. –  Remus Rusanu Dec 4 '13 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. –  user3055599 Dec 4 '13 at 19:55
    
@user3055599 It doesn't resolve your problem. A SQL-savvy person can easily change the Application Name parameter 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. –  Thomas Stringer Dec 4 '13 at 20:00
    
You recommend is to go with setting up some application roles to achieve it? –  user3055599 Dec 4 '13 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.

share|improve this answer
    
Thanks DavegGreen for your response. –  user3055599 Dec 4 '13 at 19:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.