Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


AD group has dbowner access to DB. How can I stop users from accessing from outside the application....


AD group has dbowner access to DB. How can I stop users from accessing from outside the application.

Author
Message
David C Young
David C Young
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 72
What am I missing. An application uses the users AD ID to execute against the database (read, write, create temp tables, execute procs). The users are all part of a single AD group and that group is granted dbowner access. (I have no control over the 3rd party application). How can I prevent these same users from accessing the database via any other application? With dbowner access they could access via SSMS, Toad or any number of other programs and do who knows what. All they need to know is the server name.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
You don't. You've opened the floodgates.

However, there's a hardware mechanism you can use if you've got a centralized N-Tier and these users aren't running the app off their PCs. Firewall your SQL Server and only allow particular IPs to access it, and then open up those IPs that the application/N-Tier resides on.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908
If there are other DBs which are legitimately accessible by users, you can't.

If there aren't, and if you can authorization to modify your firewall policy on the server, then look at firewalling off connections from everything but the DBAs and the app servers. Of course, if the DBAs aren't in their own vLAN, then this may not be an option, either.

The question I have is why does a group have dbowner access? Is this required of the app? If so, have you pushed back with the developer of the app?

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
David C Young
David C Young
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 72
It is required by the app and yes I have pushed back but gotten nowhere. Even if they weren't I would still have an access issue. Looking at the firewall option.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
from the TSQL side, you could create a logon trigger that checks the application name and the login name both.

you could make sure the login is using a specific application name, or the opposite: the login is using something it shouldn't be.

while you could rollback their connection, i might consider just monitoring non-compliance and reporting violators.

here's just one example of the many logon trigger examples found here in the forums:
http://www.sqlservercentral.com/Forums/Topic1199139-149-1.aspx#bm1199153

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David C Young
David C Young
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 72
Thanks, this looks like something that will work for us.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
Bear in mind though it's trivial to spoof the application name.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
I use a logon trigger for situations similar to this

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 912
Try creating a second AD Group and assigning the users you don't want to have db_owner access to that new group. Assign new permissions to the database via the group level and the problem is resolved.

At lease, if I understood the problem correctly.....

We had a similar problem whereby an application had to have SYSADMIN rights on the instance and DB_OWNER on the database. It wasn't negotiable and the vendor wasn't prepared to rewrite their code.

We didn't buy the App.
crmitchell
crmitchell
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 1707
I suppose you could set the application up to use a different account and set a windows shortcut to the application to run it as that account. Expect lots of support issues from users who redo their shortcuts though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search