none
Connecting to SQL server 2008 r2 remotely (connection refused)

    Question

  • Hi guys i'm  having trouble connecting to sql server 2008 r2 remotely. The server is running on window 7 laptop.

    The WAN is the problem, I can connect to the server fine by using LAN. In sql server management studio i used following detials to connect via LAN, for WAN i have just replaced the ip obtained by www.whatismyip.com 

    ServerType: DatabaseEngine

    Server name: 192.168.1.22,1433\mssqlserver

    With sql authentication.

    This works fine, but connection via WAN is the problem i get following error when connection via WAN

    (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

    I have the followings ticked :)

    • firewall is off on both machines client and server both are window 7
    • laptops tcp/ip is enabled in sql server configuration manager Service
    • port 1433 is forwarded from my rourter to 192.168.1.22
    • browser is running

    Cheers guys some help would be appreciated 

     
    Tuesday, May 08, 2012 5:37 AM

Answers

  • Actively refused" means that you got at least as far as a tcp connect packet arriving to the named server.

    http://blogs.msdn.com/b/sql_protocols/archive/2007/07/19/tcp-provider-no-connection-could-be-made-because-the-target-machine-actively-refused-it.aspx



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 08, 2012 7:29 AM
    Answerer
  • Hello,

    As Uri pointed out, actively refused means it got as far the the correct host, but the host refused the connection.

    I also noticed that your ServerName information is not correct, not sure if it was a typo or not but it should be (if used in a connection string) HostnameOrIP\InstanceName,PortNumber. In your case a 192.168 address is a non routable address, I hope it was replaced. MSSQLSERVER is the default instance and should be taken out, the port number should be after the instance name, which is the default instance in this case which can be specified as Hostname,PortNumber.

    -Sean


    Sean Gallardy, MCC | Blog

    Tuesday, May 08, 2012 1:06 PM
    Answerer
  • Hi harmy,

    An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

    How to troubleshoot: http://blogs.msdn.com/b/sql_protocols/archive/2005/12/22/506607.aspx.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Wednesday, May 09, 2012 3:23 AM
    Moderator

All replies

  • Actively refused" means that you got at least as far as a tcp connect packet arriving to the named server.

    http://blogs.msdn.com/b/sql_protocols/archive/2007/07/19/tcp-provider-no-connection-could-be-made-because-the-target-machine-actively-refused-it.aspx



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 08, 2012 7:29 AM
    Answerer
  • Hello,

    As Uri pointed out, actively refused means it got as far the the correct host, but the host refused the connection.

    I also noticed that your ServerName information is not correct, not sure if it was a typo or not but it should be (if used in a connection string) HostnameOrIP\InstanceName,PortNumber. In your case a 192.168 address is a non routable address, I hope it was replaced. MSSQLSERVER is the default instance and should be taken out, the port number should be after the instance name, which is the default instance in this case which can be specified as Hostname,PortNumber.

    -Sean


    Sean Gallardy, MCC | Blog

    Tuesday, May 08, 2012 1:06 PM
    Answerer
  • Hi harmy,

    An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

    How to troubleshoot: http://blogs.msdn.com/b/sql_protocols/archive/2005/12/22/506607.aspx.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Wednesday, May 09, 2012 3:23 AM
    Moderator