Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

AD group has dbowner access to DB. How can I stop users from accessing from outside the application. Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 2:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:18 AM
Points: 16, Visits: 49
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.
Post #1455227
Posted Tuesday, May 21, 2013 3:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
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
Post #1455233
Posted Tuesday, May 21, 2013 4:49 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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
Post #1455261
Posted Wednesday, May 22, 2013 8:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:18 AM
Points: 16, Visits: 49
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.
Post #1455538
Posted Wednesday, May 22, 2013 10:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1455600
Posted Wednesday, May 22, 2013 4:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:18 AM
Points: 16, Visits: 49
Thanks, this looks like something that will work for us.
Post #1455746
Posted Wednesday, May 22, 2013 4:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 40,385, Visits: 36,827
Bear in mind though it's trivial to spoof the application name.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1455751
Posted Friday, June 14, 2013 12:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 6,619, Visits: 14,185
I use a logon trigger for situations similar to this

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1463397
Posted Tuesday, June 25, 2013 6:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 15, 2014 12:45 AM
Points: 328, Visits: 544
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.
Post #1467119
Posted Wednesday, June 26, 2013 7:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:18 AM
Points: 220, Visits: 887
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.
Post #1467639
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse