Post reply

Local firewall issue when connecting to SQL Server

  • January 17, 2023 at 10:35 am

    Go to Answer

    #4138511

    A client has moved to SQL Server 2019 on Windows Server 2022. Earlier they had SQL Server 2012 on Windows Server 2016.

    They use our client / server application, when the client connects to the SQL service on the server. We have opened ports 1433 and 1434 on the Windows Server.

    After the change, for connecting to SQL server (via the server's ip address and instance name), we have to disable Windows firewall on the client PC. Otherwise, it gives an error. From the client, we are able to ping the server's ip address.

    It was all working fine in the earlier deployment and we did not have to tinker with the client PC's firewall.

    Any idea how to resolve this?

    Regards

    Prem

  • January 17, 2023 at 10:40 am

    This was removed by the editor as SPAM

  • January 17, 2023 at 4:46 pm

    #4138669

    Strange that you would need to modify the clients firewall for outgoing traffic

    Does the SQL Server use TCP fixed ports or dynamic?

  • January 17, 2023 at 5:06 pm

    #4138677

    Earlier I had been in a rush to leave the office and asked if you had confirmed that the port of the server was open. I had suggested one of many online port checkers and so that comment was deleted as spam, so sorry.

    I find this an interesting problem and would like to help because maybe I'll face the same problem in the future, so it helps to solve the problem now.

    I have found that, at times, disabling the local firewall temporarily allows the environment to "observe" the "use-case" and it will then adapt the firewall settings once you "demonstrated" to the system what you trying to do, so I would try the followin steps as the next set of troubleshooting steps:

    1. Disable the local firewall

      1. This is temporary, I would enable once I have a successful connection

    2. Test the connection to the SQLCMD or SSMS
    3. If step 2 is successful especially SSMS - then, while keeping the connection open,

      1. Enable the local firewall
      2. Test your application

    4. If Step 2 is not successful then

      1. test the connection from another remote machine on the same network as the server to remote the network connectivity issues
      2. If this doesn't work revisit your SQL Config
      3. If this does work revisit the firewall settings

    Try that first and then let's chat to see what the next steps could be

  • January 18, 2023 at 12:10 pm

    #4138995

    Fixed port no. 1433

  • January 18, 2023 at 12:11 pm

    #4138996

    Now, today, even if I disable the local firewall, I am not able to connect to SQL server

  • January 18, 2023 at 2:20 pm

    #4139048

    That's bad. The script seanachim script posted is viable.

    From SQL Server Configuration manager:

    The server is up and running?

    TCP is enabled? (might be disabled)

  • January 18, 2023 at 4:31 pm

    #4139093

    Quick Test:

    Open Excel, Get Data from your SQL Server Database (See image)

    ExcelData

    When connecting to your DB remember to add the port in (see next image)

    SQL Connection

    If Excel can't connect then try this on a different machine - again using Excel

    Post a connected or unsuccessful progress state so that we can help with the next stage.

    Remember that it is important to include the port address in the server name.  If it is a FQDN (www.MyDOm.co.si,1433) or an IP Address (199.99.99.209,1433) it will still need to know the port.

    I know how stressful this can be, let's see if we can get you through it quickly:)

  • January 30, 2023 at 3:40 am

    #4143106

    Have you tried telnet SQLServername port, like Telnet server 1433 and see connection status.

    Check incoming and outgoing rules allowed in firewall.

    as said above check first for SQL server online and port status again.

    Regards
    Durai Nagarajan

  • January 31, 2023 at 5:37 am

    This was removed by the editor as SPAM

  • January 31, 2023 at 9:12 am

    #4143593

    Hello everyone, thanks for your suggestions and support. I have got the following results from my trial and error:

    1. We are able to connect to SQL Server 2012 through VPN using the server IP address + instance name (server ip\instance name)
    2. If we uninstall SQL Server 2012 and install SQL Server 2019, we are not able to connect through VPN using server ip\instance name. Have enabled all protocols including TCP/IP and Named pipes via SQL Server Configuration
    3. All other settings on firewall remain the same
    4. Surprisingly, if we install SQL Server 2019 on a normal PC, it is connecting through VPN. But if we install the same SQL server on a HP server or IBM server, we are not able to connect it through VPN. The Windows server OS remains the same on all the PC / Servers on which we have tested. On the server devices, as informed earlier, we are able to connect to SQL Server 2012

    Hope I have given all relevant information.

    Regards

    Prem

  • January 31, 2023 at 10:38 am

    This was removed by the editor as SPAM

  • January 31, 2023 at 7:14 pm

    #4143866

    It sounds like you are installing a named instance on the server - and not a default instance.  Named instances do not use the default port unless they are configured to do that when installed.

    If the SQL Browser service is not running for that named instance, then you would need to specified the port in the connection string as well as the server IP and instance name (e.g. IP\InstanceName,1433).

    Unless there is a specific reason you need a named instance, I would recommend installing SQL Server as a default instance.  Then, you only need to specify the IP address with an instance name.  With that said, since you are also trying to access this across a VPN I would highly recommend you don't use the default port.

    Instead, pick a static port and make sure the SQL Browser service is disabled.  Modify your connection strings to include the port number.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • February 1, 2023 at 7:14 am

    Answer

    #4144030

    Hello, yes I have used a named instance.

    I have found a solution to the issue I was facing. Have done the following (other than firewall settings, which were done earlier):

    1. In the SQL Configuration manager, in the client protocols for my instance, in the TCP/IP - IP Addresses tab, for the IPAll, gave 1433 as the TCP port

    2. In the SQL Configuration manager, for the SQL Server service of my instance, made the log on as built-in account "Network service"

    3. In the SQL Configuration manager, for the SQL browser service, made the log on as built-in account "Network service"

    After re-starting the services, am now able to connect to the database service using IP address\instance name and also using IP address,port number.

    Thanks a lot for all the support and guidance.

    Regards

    Prem

  • February 1, 2023 at 11:42 am

    #4144155

    Thanks for posting your solution

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply