May 7, 2009 at 8:24 am
Hi All,
I am trying to reslove an issue around an implementation I have.
We have a Web App which normally would access sql using an annonomous account, however we want it to use NT logins for everyone. We've come up with a plan which splits the users into two Active Directory groups, Users & Admin. The User have datareader access and limited insert/update/delete to just 3 tables. The Admin group has full control over the database. The application limits the users to seeing data just about themself and any one they are repsonsible for.
Now we have a problem where the user could actually hook up to the database through Excel/Access and read everybody's data, is there anyway we can limit which machines can connect to the SQL Server so that just the Web Server has permissions? Any other alternatives?
Any info would be greatly appreciated.
Thanks
May 7, 2009 at 8:47 am
I don't know of any way to do this within SQL Server. You could do it with a rule in a firewall.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2009 at 1:04 pm
Thanks - yes that makes alot of sense.
May 7, 2009 at 1:08 pm
Use logon triggers as well to check where the connection is coming from and kill it if its "not nice".
May 7, 2009 at 1:26 pm
You have to be wary of the logon trigger approach because client host can be set in the connection string of the application. So a "smart" person could spoof the name of the web server by setting the WorkStation ID attribute of the connection string.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2009 at 3:02 pm
Check out this article[/url] by Brian Kelley.
No offense to MS but Brian's solution is better than the MS one. sys.dm_exec_sessions can be spoofed by the Workstation ID and EventData is not.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply