Results 1 to 13 of 13
  1. #1
    Join Date
    May 2014
    Posts
    6

    Exclamation Unanswered: Concurrent Logons

    I am trying to limit every logon on each of my SQL Server 2012 SP1 database servers to 5 concurrent logons. I am using Active Directory groups for security which users in those groups changes frequently. Is there a way to limit the logons globally so that each person that logs on can only have 5 concurrent logons? I know that I can do it for each individual account using a logon trigger but would like to know how to do it globally or for each AD security group on the server since the group's membership change frequently and would be hard to manage over all of our servers. Thank you in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ... why?
    George
    Home | Blog

  3. #3
    Join Date
    May 2014
    Posts
    6
    I have been told I need to by our IA.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Okay.

    But why do they want to do this?

    Personally, I can't see any reason why at all..
    George
    Home | Blog

  5. #5
    Join Date
    May 2014
    Posts
    6
    I hear ya, they say it is to help stop dos attacks...

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the Active Directory groups changed every day, then I can almost see a need to make sure that a connection does not survive for more than 24 hours, so the rights associated with the login that connected are sure to change, but I have never seen a situation where data was so sensitive that a person who had access to a thing on Monday, lost that access on Tuesday, and regained it on Wednesday. If such were the case, then a midnight restart of the SQL Service would ensure that all connections had fresh credentials at the beginning of the day. Limiting a login to 5 concurrent connections would do nothing to alter their permissions, or group memberships. It would just potentially slow down what work they can do. Some applications (Mangement Studio for example) will happily open multiple connections that the user does not even know about.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    per seat versus per processor licencing probably. or perhaps really bad code on bad hardware.

    Have you played with this guy...

    SELECT * FROM master.[sys].[syslogins]
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by asbpenguinman View Post
    I hear ya, they say it is to help stop dos attacks...
    oh giggity. Is this the first line of defense or the last?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    May 2014
    Posts
    6
    It is the last line of defense. We have plenty of other security measures in place to help stop ddos and dos attacks.

    Just to clear up, I am not worried about when a user is taken out of/put into a group in regards to access to the database. What I am trying to implement is when a particular user has more than 5 concurrent connections to the database that the user gets denied access until he/she closes the other connections to get under 5. The problem with that is that the users could be added/removed from the AD group that has access quite frequently which would cause a lot of overhead for the database administrator to administer and keep up because as far as I can tell SQL Server only allows Logon Triggers to be associated with an individual account and not an Active Directory Group or set globally.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Tell them to uninstall MS/DOS, then ;-).

    Seriously, though, This may be more of a job for Resource Governor if they are really worried about DOS attacks. I have servers that accumulate over 4,000 connections with no real noticeable effects, other than me asking 'What the heck is the app doing with all these connections??'

  11. #11
    Join Date
    May 2014
    Posts
    6
    I think it is ridiculous as well. Just a fancy new thing they found out SQL Server could implement. They aren't concerned with 4000 different connections from different people, I guess they are concerned with the 1 million connections from 1 person??? Anyways, it would be easy to implement if I could use Active Directory groups to limit concurrent connections but I am not sure I can without coming up with a crazy script to handle it.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I suppose you could make a login trigger. Something like:
    Code:
    create trigger syntax here
    as
    if ((select count(*) from sys.dm_exec_sessions where login_name = system_user and host_name <> serverproperty('machinename')) > 100)
      begin
        rollback
      end
    this kind of trigger is bound to come with some fairly nasty side effects, even beyond the usual dry mouth, and diarrhea. It could in fact disable the server entirely, depending on how the server applies this trigger to system sessions. Eiter way, I would not implement this at 5 connections, I would implement this at no less than 100. If the IA wants to go counting connections that closely, he is welcome to.

  13. #13
    Join Date
    May 2014
    Posts
    6
    I think that is exactly what I was looking for. I appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •