locked
Remote Connection RRS feed

  • Question

  • Could someone tell me how to remote connect to other server. Everytime when I try to connect to other server, I get an error message. Eventhough I have opened the 1433 port, allow the remote connection using both TCP/IP and named pipes, and open the firewall for connection.

    "An error has occurred while establishing a connection to the server. When connection to
    SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)(Microsoft SQL Sever)"

    Thank you
    Wednesday, April 12, 2006 2:14 AM

Answers

  • Connections normally "live" on port 1433 and browsing on 1434. Assuming that your firewall is stateful after allowing an incoming port of 1433, the connecting works finde, but only for incoming request. If you allow port 1433 for outgoing request (on the server that didn´t work) you should be able to connect to the other servers. if you want the Service on the current non working computer to see the other instances, you have to allow outgoing 1434 as well.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, April 14, 2006 1:27 PM

All replies

  • Hi,

    are you sure that the default port (1433) is setup ? This can be due to another choosen configuration (You don´t need to put the port on 1433) or if you want to connect to another instance than the default (which normally has another port than 1433, because *normally* the default instance has this port assigned).

    HTH, Jens Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Wednesday, April 12, 2006 5:59 PM
  • Hi,

    I also have MS SQL 2000 installed in the same computer.  Is it because of this MS 2000 and MS 2005 are sharing the same port that causing problem?  However, when I check the Config Manager, the MS 2005 is using dynamic port.  Should I set it to static port, if so, could you tell me how to set it to stactic port.  Thanks.

    Thursday, April 13, 2006 6:33 AM
  • Hi,

    is the SQL Server Browser startetd ? If you are using dynamic ports you need to have the brwoser started, that this one can redirect the request (without the port number, because it can change every start of the service) to the dynamically choosen port. You can choose a static port by getting a free one on your system which isn´t currently used and type this in the box TCP Port and leaving the dynamic port box blank.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Thursday, April 13, 2006 7:47 AM
  • Hi,

    Still get the same error while SQL Server Browser already started and using dynamic ports.  Do you know any other reasons that might cause this problem.  Thanks

    Thursday, April 13, 2006 9:46 AM
  • I have both MSSQL 2000 and MSSQL 2005 installed in the computer, when I tried to browse for connection to another computer that also have both version installed.  I could only see the MSSQL 2000 Server instance.  However, If I turn off the firewall in both computer, then I can see both MSSQL 2000 and 2005 instances listed, and can make the remote connection.  I think this might be the problem of ports.  Do you know how to keep the firewall on and also can make remote connection to other MSSQL 2005 server.  Thanks
    Friday, April 14, 2006 3:05 AM
  • Connections normally "live" on port 1433 and browsing on 1434. Assuming that your firewall is stateful after allowing an incoming port of 1433, the connecting works finde, but only for incoming request. If you allow port 1433 for outgoing request (on the server that didn´t work) you should be able to connect to the other servers. if you want the Service on the current non working computer to see the other instances, you have to allow outgoing 1434 as well.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, April 14, 2006 1:27 PM