Using AD group for permissions RRS feed

  • Question

  • I know this is a often discussed topic.  I'm researching it, but would like to get input from this forum as well.  If I use a domain group to give application users read/write permissions to a database, how do I prevent individual members of the domain group from accessing SQL Server independently via SSMS from the application server or another remote server?  Assume firewall ports open between the app server and SQL server.  Thanks.
    Monday, January 7, 2019 7:51 PM

All replies

  • The simple answer is by default, you cannot, since the user AD login via group is authorized to connect to SQL.

    However, you can use logon trigger to check APP_NAME, and dis-connect the connections when it is from those app_name you don't like. But be careful that the users can still fake the APP_NAME from the clients. It will work better when you limit the logins from a whitelist from a subnet.I have some example here (https://github.com/loganSQL/SQLDBA/blob/master/04SQLMonitor/WhiteListOnSubnet.md)

    Monday, January 7, 2019 8:28 PM
  • You make the application a three-tier application and then you segment the network, so that users cannot reach SQL Server directly. If the application is a fat Windows client, there is always Terminal Server and Citrix to introduce a new tier.

    But as LC2009, there is no secure way you keep them out from SSMS and let them in from the application, as long as they have network access to SQL Server.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, January 7, 2019 11:01 PM