Cannot connect to to remote database from SQL Server Management Studio

Discussion in 'Databases' started by Deepak, May 7, 2010.

  1. Hi,

    I've been trying to run T-SQL script (with schema and data) on the remote database through the SQL Management Studio. So far, I have not made any progress. I keep getting the following error:

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to s02.Winhost.com.

    ------------------------------
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    For help, click: http://go.microsoft.com/fwlink?Prod...rver&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

    --------------------------------

    As I understand, I need to do some settings for the SQL Management Studio to work. So I referred the following articles for the same:

    1. http://blog.sqlauthority.com/2009/0...ion-to-sql-server-microsoft-sql-server-error/

    2. http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

    Which basically told me to enable SQL Server browser service, add firewall settings and so on. However, the problem persists. Is there any thing else that I need to do? Is there another way to export the T-SQL scripts to the remote db?

    Thanks,
    Deepak
     
    Last edited by a moderator: Oct 14, 2015
  2. Ray

    Ray

    The error you are getting can mean several things.

    1. Your SQL Management Studio is using Named Pipes to reach out over the Internet and connect to our server. This will not work. Named Pipes is not routable over the Internet. You will need to go to your SSMS configuration manager and make sure you connect to our SQL Server using TCP.

    2. Another possible cause to this error is that s02.Winhost.com is not resolving to the IP address. It can caused by your firewall, proxy server, or just some corrupted table on your router. Also port 1433, which is the SQL port mayalso being blocked by these devices.

    To find out try running these tets.

    Go to your MS DOS prompt and type...

    ping s02.Winhost.com

    and then type...

    telnet s02.Winhost.com 1433

    If you get a time out error with the ping test or if it shows a different IP address other then 96.31.35.202 then s02.Winhost.com is not resolving to our server correctly. Check with your network administrator about this.
    If you perform a telnet test and you get an error that states in the realm of connection cannot be made, then port 1433 is being blocked by your network. Again you'll need to speak with your network administrator about this.
     
    Last edited by a moderator: Oct 14, 2015
  3. Hi Ray,

    Thanks for you reply. I tried the things you had mentioned.

    1. I can ping the s02.Winhost.com address and it is resolving to the correct IP. Howver, when i tried to telnet to the server, I am getting a "connection closed by remote host" message.

    2. I changed the networking protocol to "TCP/IP" instead of named pipes. Now I am getting a different error, which I am listing below:

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to s02.Winhost.com.

    ------------------------------
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. 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. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

    Could this still be a firewall issue? I am completely disabled the firewall for testing and it still doesn't work. I am trying this from my home computer so there is no network admin involved here. I am directly connected to the internet. Do you have any suggestions?
     
    Last edited by a moderator: Oct 14, 2015
  4. Ray

    Ray

    I suspect that port 1433 is being blocked on your network. Try running this telnet test...

    telnet s02.Winhost.com 14330

    If it blanks out and you get no error message then it means that this port is open and you should configure your SSMS to use port 14330
     
    Last edited by a moderator: Oct 14, 2015
  5. Hi,

    I tried this, but I am getting the same "Connection closed by remote host" error in PuTTy.
     
  6. Ray

    Ray

    The error is misleading because it does suggest that we are the one closing it. But in fact you never connected. I can only tell you from what you have done and the results that came up shows that both SQL ports 1433 and 14330 is blocked. You will need to talk with your network administrator and request that they open at least one of those ports.
     
  7. ISP had port 1433 blocked and Solution

    I struggled with this (getting SSMS connected to s02.Winhost.com) too. So here is some more clarrification to the knowledge base and other posts.

    Turns out that my ISP (TDS.net) blocks port 1433 by default. TDS.net covers a lot of the midwest, Tennessee, etc. (and I give them very high marks for DSL service and support). I could put in a request to unblock it, but choose to just change the TCP port to 14330. Needless to say just figuring out that this port was blocked took a little time.

    In SQL Configuration Manager; under SQL Server Network Configuration select Protocols for MSSQLSERVER. Then in the right handed window, right mouse click on TCP/IP and choose Properties. In the IP Address tab, I changed the TCP Port from 1433 to 14430 in all instances. Stop and start the SQL Server service. Note: This turned out not to be enough and I had to reboot.

    Next to connect SSMS to my Winhost db. In SSMS click on Connect; Database Engine and use these parameters:
    Server Name: s0.Winhost.com,14330
    Authentication: SQL Server Authentication
    Login: Your database name + "_user" (e.g. DB_xxxxx_test_user)
    Password: Your Winhost pw


    Hope this helps a few people.
     
    Last edited by a moderator: Oct 14, 2015
    LemonShark likes this.
  8. Ray

    Ray

    Actually if you specify "s0x.Winhost.com,14330" you do not need to go through the properties portion of the Network Configuration. Just stating "s0x.Winhost.com,14330" will force the call to port 14330. And in fact if you type "tcp:s0x.Winhost.com,14330" not only will you force it to use the specified port number, you are now specifying the protocol tcp rather then named pipes which is a setting SQL will also use.

    The best way to test if a port number is open on your end is to telnet test into it. As an example telnet s0x.Winhost.com 14330 should return some kind of response from the server or just a blank screen. If you get any type of connection failure message right off the bat, then that usually means that the port is blocked from your end. As you said either by your ISP or your own local area network such as your router. There are also some 3rd party tools out there that can check what out going ports are blocked on your end. Keep in mind that although the out going ports are being blocked it does not mean that incoming ports are block. Sometimes it is misleading to assume just because someone can connect to you via port 1433, does not mean your own out going port will allow 1433 and vice versa.
     
    Last edited by a moderator: Oct 14, 2015
  9. I am having a similar problem connecting to the database via the command window and a telnet to both 1433 and 14330 fail to connect. I am unable to get either of there ports opened am I not in no mans land? Is there a web interface to manage the sql server from?
     
  10. Ray

    Ray

    Sorry, unfortunately not. There is a web tool called myLittleAdmin but we can't offer it because of licensing issues.
     
  11. I have found a way to get telnet to connect to 14330 how do I include the port number in a command line?

    I tried
    sqlcmd -S w03.Winhost.com:14300 -U ....

    but no joy.
     
    Last edited by a moderator: Oct 14, 2015
  12. cracked it!
    You need to use sqlcmd -S tcp:s03.Winhost.com,14330
     
    Last edited by a moderator: Oct 14, 2015
  13. Ray

    Ray

    If you use sqlcmd to connect to our sql server using port 14330, then you should be able to use SQL Server Management Studio. All you need to do is set it up to use port 14330.
     
  14. Thanks Ray

    I don't have the management studio as yet but now I have the command line working (with the tcp: connect I posted) I am able to work on my database.

    Cheers

    Stephen
     
  15. Ray

    Ray

    Don't forget Visual Studio 2010 has a slimmed down version of a database management tool also. You can sort of manage your database from there such as creating tables. It sounds like you a pretty advanced SQL user so you know your way around with SQL statements. But if you can make it easier on yourself by all means.
     

Share This Page