locked
MS Access querying SQL Server: How to configure Network ACL? RRS feed

  • Question

  • I need to set up a highly restrictive ACL for access to a particular SQL server in our network.  This ACL will reside on our core switch at the "front door" of our network and permit access to the SQL server only using the ports that are necessary.  The purpose is to A) Try to keep unauthorized users from gaining access to the host server and B) Should someone somehow gain unauthorized access to the host server. keep them from being able to "hop off" to other PC's on the network. 

    The server will be accessed by clients using MS Access to query the SQL database and bring back reports.  A few admins are actually able to make minor changes to the database such as updating a user list or location list. In other words, both read and write access is needed to the SQL database.

    I know that the default SQL server port is 1433, but according to a Microsoft Support article I read, "client ports are assigned a random value between 1024 and 5000".

    I was really hoping I could just put something like "permit PC1 access to SQL Server on Port 1433" in my ACL, but after reading the MS Support article it sounds like I've got to allow almost 4,000 ports through?

    Could someone help demystify this for me so I can build the right ACL?

    Friday, May 16, 2014 12:04 AM

Answers

  • The tool to use is SQL Server Configuration Management.

    But what you can configure is which port SQL Server listens to. Which port the client listens to is not controllable as far as I know. But that can of course be many, since a client can have many connections to SQL Server. (And the range is not restricted to 1024-5000. I connected over TCP locally, and I see this with netstat -a

      TCP    127.0.0.1:6621         NATSUMORI:ms-sql-s     ESTABLISHED

    Then again, I don't see why you would have to open any ports for the clients at all. I have never heard of this being a problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, May 19, 2014 5:45 AM
    • Marked as answer by Sofiya Li Friday, May 23, 2014 9:04 AM
    Saturday, May 17, 2014 9:17 AM
  • You can't accuse me for knowing too much about network ACLs in core switches, so I may be missing something, but...

    In the firewall on my desktop I disallowed all connections from the network for my host-only VMs. And, consequently, I was not able to connect to any of the SQL Server instances on the desktop from the VM. However, I was still able to connect from the desktop on an SQL Server instance running on the VM, despite that network being blocked in my firewall.

    Thus, I am not sure that you need to open any ports at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, May 19, 2014 5:45 AM
    • Marked as answer by Sofiya Li Friday, May 23, 2014 9:04 AM
    Saturday, May 17, 2014 3:10 PM

All replies

  • Hello,

    The used IP port depends on the SQL Server Network configuration. You can define that a dynamic port is used; on every SQL Server restart it Looks up a free port in the range between 1024 and 50000. But you can also define that a fixed port is used; see Configuring a Fixed Port . So you can define any free port you like.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 16, 2014 6:24 AM
  • Olaf,

    Thank you for your reply!  So if I understand correctly,  I can go into the SQL management studio and designate a fixed port (say 55555) for client connections. Then I just need to do the following:

    permit <clients> access to <SQL SERVER> on port 1433  AND

    permit <clients> access to <SQL SERVER> on port 55555

    Then every client who needs to query the database from their MS Access tool will be able to do so?  Even if multiple users need to access it at the same time?

    When MS access first sends the request to the SQL server is it on port 1433 then the response is on 55555?

    Saturday, May 17, 2014 2:28 AM
  • The tool to use is SQL Server Configuration Management.

    But what you can configure is which port SQL Server listens to. Which port the client listens to is not controllable as far as I know. But that can of course be many, since a client can have many connections to SQL Server. (And the range is not restricted to 1024-5000. I connected over TCP locally, and I see this with netstat -a

      TCP    127.0.0.1:6621         NATSUMORI:ms-sql-s     ESTABLISHED

    Then again, I don't see why you would have to open any ports for the clients at all. I have never heard of this being a problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, May 19, 2014 5:45 AM
    • Marked as answer by Sofiya Li Friday, May 23, 2014 9:04 AM
    Saturday, May 17, 2014 9:17 AM
  • Hello,

    The issue is The SQL server is being housed in a different network, like a DMZ.  Everyone who is accessing the SQL server will be coming into the DMZ and be subject to a network ACL on our core switch.  

    In the ACL I want to give only the specific access that is needed for the Access front end to talk to the SQL back end.  With everything else that needs to communicate with the SQL server (Anti-virus updates, SCCM, monitoring software/etc.) This is accomplished by allowing only 1 or 2 ports.  SQL is the only one that seems to allow a huge range of ports which kind of defeats the purpose of trying to lock down access in the ACL unless I'm missing something...

    Saturday, May 17, 2014 12:49 PM
  • You can't accuse me for knowing too much about network ACLs in core switches, so I may be missing something, but...

    In the firewall on my desktop I disallowed all connections from the network for my host-only VMs. And, consequently, I was not able to connect to any of the SQL Server instances on the desktop from the VM. However, I was still able to connect from the desktop on an SQL Server instance running on the VM, despite that network being blocked in my firewall.

    Thus, I am not sure that you need to open any ports at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, May 19, 2014 5:45 AM
    • Marked as answer by Sofiya Li Friday, May 23, 2014 9:04 AM
    Saturday, May 17, 2014 3:10 PM