locked
Can not remote-connect to SQL with public IP after changing the port number RRS feed

  • Question

  • Hi 

    Well, 2 hours I searched the internet to look for an answer but failed.

    I have SQL 2008 R2 developer installed on the windows 2008 standard server and is a part of domain. Firewall is off. 

    I used port 1433 and with that port, I was able to access to the server with a public IP. 

    I have just changed the port number to 14330 (for example), then i can not no longer access using the management studio using the public IP address. I can with local ip address.

    My IT guy opened the port and I tested on www.ping.eu and confirmed it. 

    I even tested by changing port number to something else and check with the site above.

    so it is definitely SQL server configuration issue. 

    I have just changed the port number back to 1433 and tried to connect and I can! 

    There must be something I am missing. 

    Does anyone has an idea? 

    1. Firewall off - but I still made a change. 

    2. Allow remote connections to this server is ticked

    3. In SQL server configuration manager - Port changed. I even changed three places

    Client protocols in SQL Native 10.0 Configration (32bit)

    Client protocols in Client Native client 10.0 configuration 

    Protocols for MSSQLSEVER. - TCP setting 

    I have just changed everything back and it's working now. Could someone please help? I need to change the port number.

    Thanks

    Tuesday, June 4, 2013 9:44 AM

Answers

  • Hi SQLMA,

    I was reading through your post and posts by responders. 
    If I read everything correctly, you are able to connect internal IP fine, while external (Public) IP there is a problem. Assumption is in the past it has been successful on 1433? (Then it might be likely it is a "network" issue)

    1.  Have you tried connecting to your SQL Server when not connected via VPN ?  (e.g. At home using SSMS connecting to public IP.  If that works maybe there could be some internal routing issues maybe? 

    2.  If you can't connect to the public IP, but internal IP works (xxx.xxx.xxx, Port #) I am not familiar with PING.EU (if you can explain more)  I might be suggesting something you have already tried. But to re-attempt, have you ever tried a tool called:  PortQry link: http://support.microsoft.com/kb/832919

    This attempts to connect based on IP and Port and returns message of success/failure with some details. If you have not tried this already (if PING.EU is same)  note the begin time frame you attempted to access the (Public IP,Port)  make several attempts. (to help capture in Network logs) Then note the end time frame.  Provide those time details, to your Network Administrator to check the network logs for access to Public IP.  Maybe the port is blocked on the public side?  Your network admin should be able to verify and confirm.

    Hopefully that helps and what I wrote makes sense.  Also as mentioned by responders, if you have some error messages (error logs/pop up windows msgs) it would help with responses.

     

    • Marked as answer by SQLMa Wednesday, June 19, 2013 12:41 AM
    Sunday, June 9, 2013 9:47 PM

All replies

  • Hi,

    Is the SQL Server Browser service running (via configuration manager).  Have you tried connecting to the server using the ipaddress,port number, e.g. 10.10.10.10,14330?



    Thanks, Andrew

    Tuesday, June 4, 2013 3:21 PM
  • The reasons for Andrews advice are at SQL Server Browser Service http://msdn.microsoft.com/en-us/library/ms181087(v=SQL.105).aspx 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, June 4, 2013 4:06 PM
  • And what error message do you get?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 4, 2013 10:12 PM
  • Hi Andrew

    Thank for your reply.

    Yes. SQL Server Browser service is running. Otherwise it wouldn't have worked with port 1433, would it? 

    And I always use IP address only with a port number. Never used the instance name.

    My IT guy mapped any incoming port number 14430 to 1433 so i can connect to the server but this didn't work either. 

    I am not so sure about what's happening here.

    Cheers



    • Edited by SQLMa Wednesday, June 5, 2013 2:00 AM
    Tuesday, June 4, 2013 11:47 PM
  • Hi Ricky 

    Thank for the reply. 

    I read the article and I am still not sure what I am missing. 

    SQL Server Browser has always been running. Is there anything I need to it after changing the port number? 

    Just curious..  Does UDP port 1434 need to be open to the external? Does it matter with a custom port number? (It is ok with default port 1433 but not ok with any other port number. )

    From the article, the following connections do not work if the service is not running. 

    1. Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

    - I only use IP address with the port number so this doesn't apply

    2. Any component that generates or passes server\instance information that could later be used by other components to reconnect.

    - not clear to me. 

    3. Connecting to a named instance without providing the port number or pipe.

    - always use port number

    4. DAC to a named instance or the default instance if not using TCP/IP port 1433.

    - What is DAC? I don't understand this. 

    5. The OLAP redirector service.

    - We do running Analysis service, which is the whole point of using SQL server. I need to have better understanding about the service. but it looks like nothing to do with connecting to SQL server suing the management studio. 

    6. Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

    - don't know much about this. 

    cheers



    • Edited by SQLMa Wednesday, June 5, 2013 12:17 AM
    Wednesday, June 5, 2013 12:16 AM
  • The error is 

    The server was not found or was not accessible. Verify that the instance name is correct and that SQL server is configured to allow remote connections.

    Cheers

    Wednesday, June 5, 2013 12:19 AM
  • Can you telnet to that port using the public IP to confirm that it is truly open?

    telnet hostname:14430

    You shouldn't get a "Connection refused error"

    Wednesday, June 5, 2013 4:56 AM
  • When you connect without instance name, then the client tries 1433 - the browser service isn't involved. So in order to connect without instance name, then you need to have the server listening to 1433, or do some port routing outisde SQL Server.

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 5, 2013 5:02 AM
  • Have you got UDP 1434 open as well?  That's for the SQL Server Browser service


    Thanks, Andrew

    Wednesday, June 5, 2013 7:23 AM
  • Guys, SQLMa is connecting by port number, not instance name. Thus, the Broswer server does not need to be running, nor does UDP 1434 need to be open.

    So this is something else. Really stupid question, SQLMa: you did restart SQL Server after the port change, didn't you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 5, 2013 7:40 AM
  • Ah, I missed that the Connection is attempted using port number...

    Also, the SQL Server errorlog documents what port number the instance is listening on.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 5, 2013 8:21 AM
  • ditto :(


    Thanks, Andrew

    Wednesday, June 5, 2013 8:39 AM
  • Hi

    Yes. Of course. ^^

    Thursday, June 6, 2013 12:58 AM
  • Thanks for the reply! 

    I can not do this test today. I will do it on the weekends. I can't interrupt the users no more. 

    but as I mentioned above, I tested it using 

    http://www.ping.eu/port-chk/

    Thursday, June 6, 2013 1:05 AM
  • Next time you test, please post the opening lines of the SQL Server error log where it reports which ports it is listening to.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 6, 2013 8:32 AM
  • Hi SQLMA,

    I was reading through your post and posts by responders. 
    If I read everything correctly, you are able to connect internal IP fine, while external (Public) IP there is a problem. Assumption is in the past it has been successful on 1433? (Then it might be likely it is a "network" issue)

    1.  Have you tried connecting to your SQL Server when not connected via VPN ?  (e.g. At home using SSMS connecting to public IP.  If that works maybe there could be some internal routing issues maybe? 

    2.  If you can't connect to the public IP, but internal IP works (xxx.xxx.xxx, Port #) I am not familiar with PING.EU (if you can explain more)  I might be suggesting something you have already tried. But to re-attempt, have you ever tried a tool called:  PortQry link: http://support.microsoft.com/kb/832919

    This attempts to connect based on IP and Port and returns message of success/failure with some details. If you have not tried this already (if PING.EU is same)  note the begin time frame you attempted to access the (Public IP,Port)  make several attempts. (to help capture in Network logs) Then note the end time frame.  Provide those time details, to your Network Administrator to check the network logs for access to Public IP.  Maybe the port is blocked on the public side?  Your network admin should be able to verify and confirm.

    Hopefully that helps and what I wrote makes sense.  Also as mentioned by responders, if you have some error messages (error logs/pop up windows msgs) it would help with responses.

     

    • Marked as answer by SQLMa Wednesday, June 19, 2013 12:41 AM
    Sunday, June 9, 2013 9:47 PM
  • You are saying that browser service is running, I am suspecting it is just showing that it is running please restart the browser service and check. This happens with me also and it was sorted out by restarting browser service.
    Tuesday, June 11, 2013 10:59 AM
  • Hi Norm chan

    Thank you for the reply. 

    I will try what you have suggested. Can not do in the next couple of days but I will report back after I tried. 

    Thanks again!!!

    Wednesday, June 12, 2013 12:04 AM
  • Hi

    It works now but not sure why. I did exactly what I did before and now it works. 

    :(

    Wednesday, June 19, 2013 12:41 AM
  • Hi

    It works now but not sure why. I did exactly what I did before and now it works. 

    :(

    Glad to hear....I'm guessing there have been no changes in the infrastructure?

    Thursday, June 20, 2013 7:23 AM