none
Limiting SQL access

    Question

  • Is there a way to limit/restrict the number of concurrent users able to access a given SQL DB?

    We need to force a department to only have the ability to allow a certain number of users at a time to a DB where the front end is an IIS website.

    The website is the front end app for a SQL DB and the department is being monitored for auditing, so we need to only allow the number of users that have SQL CAL's to access the website


    Alert from Microsoft Forum

    • Moved by Tom Phillips Friday, May 10, 2019 12:38 PM Better security question
    Friday, May 10, 2019 12:35 PM

All replies

  • I am afraid not to a single DB. You can set the number of concurrent users to the entire instance. If you have only one db in your instance so you can use:

    USE AdventureWorks2012 ;  
    GO  
    EXEC sp_configure 'show advanced options', 1;  
    GO  
    RECONFIGURE ;  
    GO  
    EXEC sp_configure 'user connections', 325 ;  
    GO  
    RECONFIGURE;  
    GO  
      

    or using the GUI:

    Please mark as answer if this post helped you

    Friday, May 10, 2019 12:56 PM
  • No I'm afraid there are multiple DB's per instance.

    Alert from Microsoft Forum

    Friday, May 10, 2019 1:10 PM
  • That is not how SQL Server licensing works.   CALs are required for every device which could "potentially" connect to an instance, not concurrent users.  

    I highly suggest you contact MS Licensing and read the licensing guide under "Licensing SQL Server in a multiplexed application environment":

    https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing

    Friday, May 10, 2019 1:58 PM
  • The department head was told something by a developer that we had to dispute, using the guide, so we have it.

    I'm looking at it now, and I can't find anything supporting the "potentially" instead of concurrent.

    Let me know what you're referring to please so I can take that to management about the direction they need to go.


    Alert from Microsoft Forum

    Friday, May 10, 2019 3:14 PM
  • Please contact a Microsoft representative at (800) 426-9400, or your volume license contact.

    SQL Server has never been licensed on "concurrent users".

    • Proposed as answer by pituachMVP Saturday, May 11, 2019 12:36 PM
    Friday, May 10, 2019 4:45 PM
  • We did.

    I wasn't disagreeing about the concurrent, as much as nothing stipulating "potentially" or the like in the documentation, but I understood.

    Thank you


    Alert from Microsoft Forum

    Friday, May 10, 2019 6:49 PM
  • Hi DarkAngel11,

     

    Have you solved this problem ?  In order to close this thread, please kindly mark your replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 13, 2019 6:14 AM
  • Hey there. No it wasn't solved.

    Alert from Microsoft Forum

    Monday, May 13, 2019 10:12 AM
  • To answer your original question, you can use a logon trigger to limit the connections.

    Please see:

    https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017

    However, it will be difficult to determine if they are in that department.  Possbily by group?

    Monday, May 13, 2019 2:29 PM