locked
Outbound port to open RRS feed

  • Question

  • I need to send a request to the infrastructure people saying which firewall ports to open to allow my SQL Server to extract data from an external SQL Server. (I can't test anything and they want a few ports or small range).

    I need to be able to connect from my server behind the firewall to the remote server - I guess the preference would be to use SSIS but would also like to be able to create a linked server connection.

    So which outbound ports would I need to be opened? I assume the destination would use the same port for the response? Does anything need to be done to allow that?

    Wednesday, August 6, 2014 1:14 PM

Answers

  • No, you don't have to open inbound ports in that range, only outbound ports.  Firewalls are smart enough to know that traffic originating behind the firewall should be allowed back through the firewall from the outside.

    Standard practice for firewalls is to open all outbound ports.  Typically if there are restrictions placed on users by firewalls those restrictions limit users to specific IP addresses and/or domain names (whitelist/blacklist).  Since outbound connections for many services use random port numbers it's basically impossible to restrict users to specific ports without essentially shutting your users down.

    Ask your network admins to allow the computer connecting to the external SQL Server to have all outbound ports opened to the IP or domain name of the external SQL Server.  That shouldn't pose a security risk to your network as it will allow only traffic from your PC to that single external server.

    Thursday, August 7, 2014 3:08 PM
  • I don't have any experience of enterprise-level firewall products, but the consumer-level firewalls I have used (Outpost, Kaspersky, ZoneAlarm etc), typicall permit outgoing traffic per application. That is, if I don't like Windows Media Player calling home, and I only use it to play files on my own machine, I can tell the firewall to block all outgoing traffic from WMP. A good firewall also lets me restrict an application to only use a certain port, a certain protocol or connect to a certain IP address.

    Blocking outgoing traffic per ports is completely crazy. And while the world of IT is full of people on high horses who know a lot less than the height of their horses, I would not expect a firewall administrator to be that stupid. (Because it would break down very quickly.) I suspect that you and the infrastructure people are talking past each other.

    What probably needs to be open is the incoming port on the remote server. That is, if that server is listening on port 1433, that is the port to open.

    As an example, while I can access the web in general from work, there is web-attest system that I can't access. This is sure due to that the URL includes :9898.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 7, 2014 6:57 PM

All replies

  • If the external SQL Server was configured to use the default port AND you aren't connecting to a named instance on the server then you should be able to connect to it on TCP port 1433.

    If the external server is configured to use a port other than the default port then you will need to find out what port that it was configured to us.

    If the external server is a named instance of SQL Server then things get more complicated because by default SQL Server uses a random port for named instances so the people who own the external server would have to configure it to use a static port instead.  Or they would have to open UDP port 1434.  For more details see...

    http://support.microsoft.com/kb/287932

    Wednesday, August 6, 2014 2:33 PM
  • There is no difference in the ports used based on how you connect to the SQL Server.  The SQL Server database engine only uses 1 port for all incoming connections.
    Wednesday, August 6, 2014 3:05 PM
  • Are you saying that if I get the outbound port 1433 opened then sql server will use that to connect?

    Note - I am not talking about the sql server that receives the request which is always on port 1433 but the one that sends it.

    I thought that was dynamic above 1024? That's no good for me?

    Checking the link I think Ed is talking about inbound and Tom specifies inbound so not really relevant.

    p.s. Don't know much about firewalls so the way I've phrased this might not even make sense.

    Is it that the outbound port that needs to be opened is the same as the inbound of the recipient?
    Wednesday, August 6, 2014 3:31 PM
  • Outbound ports are generally not restricted, they are random.  It would be strange if your network was restricting outgoing ports.

    Please see:

    http://support.microsoft.com/kb/287932

     ...client ports are assigned a random value between 1024 and 5000

    Wednesday, August 6, 2014 7:10 PM
  • Yes - I've said that by default it would need 1024-5000 opened and that the access should be restricted by a rule for the application but that has been rejected and they will only open a restricted set of ports.

    Other issue is that I can't discuss it with the infrastructure people - they will only respond to a change request.
    Thursday, August 7, 2014 8:00 AM
  • Hello Nigel,

    Just a note, it's a two-way communictaion with SQL Server, so you have to open outbound and inbound rules for the used port.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 7, 2014 8:06 AM
  • That would mean opening all inbound and outbound ports in the range 1024-5000. If that's the case then we won't be allowed and we won't be able initiate communication from the server to the outside world.
    Thursday, August 7, 2014 9:21 AM
  • No, you don't have to open inbound ports in that range, only outbound ports.  Firewalls are smart enough to know that traffic originating behind the firewall should be allowed back through the firewall from the outside.

    Standard practice for firewalls is to open all outbound ports.  Typically if there are restrictions placed on users by firewalls those restrictions limit users to specific IP addresses and/or domain names (whitelist/blacklist).  Since outbound connections for many services use random port numbers it's basically impossible to restrict users to specific ports without essentially shutting your users down.

    Ask your network admins to allow the computer connecting to the external SQL Server to have all outbound ports opened to the IP or domain name of the external SQL Server.  That shouldn't pose a security risk to your network as it will allow only traffic from your PC to that single external server.

    Thursday, August 7, 2014 3:08 PM
  • Again, you don't normally limit outgoing ports.  When you connect to a website on port 80, you don't connect from an outgoing port 80.  That is not how it works.

    You should not need to concern yourself with getting outside the firewall.  You need to configure your firewall to allow incoming ports INTO the SQL Server.

    Thursday, August 7, 2014 4:51 PM
  • I don't have any experience of enterprise-level firewall products, but the consumer-level firewalls I have used (Outpost, Kaspersky, ZoneAlarm etc), typicall permit outgoing traffic per application. That is, if I don't like Windows Media Player calling home, and I only use it to play files on my own machine, I can tell the firewall to block all outgoing traffic from WMP. A good firewall also lets me restrict an application to only use a certain port, a certain protocol or connect to a certain IP address.

    Blocking outgoing traffic per ports is completely crazy. And while the world of IT is full of people on high horses who know a lot less than the height of their horses, I would not expect a firewall administrator to be that stupid. (Because it would break down very quickly.) I suspect that you and the infrastructure people are talking past each other.

    What probably needs to be open is the incoming port on the remote server. That is, if that server is listening on port 1433, that is the port to open.

    As an example, while I can access the web in general from work, there is web-attest system that I can't access. This is sure due to that the URL includes :9898.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 7, 2014 6:57 PM
  • That would mean opening all inbound and outbound ports in the range 1024-5000.

    No, just the one by SQL Server + SQL Server Browser used port, nothing more, especially not a complete range.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 8, 2014 5:24 AM