locked
Connect from Host to remote/virtual RRS feed

  • Question

  • Hi,

    I need some help on configuring this setup:

    Host PC: IP Address: 20.168.1.2

    host PC has VMWare. This VMWare image has SQL Server 2008 R2 with remote connections and everything enabled.

    The VMWare image has the following IP Addresses:
    192.168.106.216
    192.168.106.218

    The VMWare image can obviously connect to itself in terms of the database (instance name: SQL2008)

    however when trying to connect FROM the host to the VMWare image, I cannot connect. it just says that it could not find the network instance/resource etc...

    I can ping successfully from the host to the VMWare image (192.168.106.216) and RDP to it no problem.

    any ideas what I am doing wrong? how can I connect my host SSMS to the remote/VMWare SQL Server instance?

    C# MVP (2007-2010) MS Vendor - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

    Thursday, January 3, 2013 8:26 PM

Answers

  • Ahmed, assuming you can connect when firewall is off, it means something is not right with exceptions setup. Are you sure that this exception is enabled in firewall profile that used by the host connectivity? Also, can you please try to add sqlbrowser.exe as the exception? 



    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by ahmedilyas Friday, January 4, 2013 6:18 PM
    Friday, January 4, 2013 6:06 PM

All replies

  • Hi Ahmed,

    While I don't have enough VMWare experience, I'm pretty sure that issue is generic. Check the following:

    1. You have TCP/IP protocol enabled. You can check it in SQL Server configuration manager. Don't forget to restart SQL Server service after you enabled it
    2. SQL Server browser service is running
    3. Firewall does not block SQL Server traffic.

    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, January 3, 2013 9:34 PM
  • Thanks!

    Well, yeh the TCP/IP protocol is enabled.

    if I disable the firewall then I can access it but the network connection is for public internet, which is not what I really want. I dont want to risk exposing data or even getting hacked into, even if it is on a VM system.


    C# MVP (2007-2010) MS Vendor - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

    Thursday, January 3, 2013 9:42 PM
  • Hello,

    In the firewall you have to unblock those ports used by SQL Server. Default ports are e.g. TCP 1433 for SQL Server access and UDP 1434 for SQL Server Browser; both in- and outbound. You can lookup the used ports in "SQL Server Configuration Manager" => Network settings. You could also change the port to a different one to make it more secure, because the port 1433 is well known.

    See TechNet: Configure the Windows Firewall to Allow SQL Server Access


    Olaf Helper

    Blog Xing



    Friday, January 4, 2013 5:37 AM
  • For Connection issue to SQL Server refer

    Problem No: 8 Connection Problems with sql server.

    There is some problem with connecting sql server database from ssms.

    SQL Error: 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 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

    Solution:

    Steps to follow.

    Step.1 Check the connection information.

    Instance Name, Username and password.

    Step.2 Check the services of sql server running.

    Step.3 EnableTCP/IP in SQL Server Configuration

    Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

    Enable All Protocols except VIA.

     

     

     

    Step.4 Enable Remote Connection

     

    Go to Left Tab of Connections and check “Allow remote connections to this server”

     

    Step 5. Enable SQL Server Browser Service

     

     

     

     

     

     

     


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Friday, January 4, 2013 9:22 AM
  • Can you post error message ?

    Cuong

    Friday, January 4, 2013 9:48 AM
  • Error msg:

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

    Cannot connect to 192.168.106.218\SQL2008.

    ------------------------------
    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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    as I said before, everything is enabled except for VIA and named pipes is also enabled and yes, the services are restarted also.

    my host ip is 20.168.1.2

    my VM ip is 192.168.106.216 and 192.168.106.218

    I can RDP to it but cannot connect my SQL to it


    C# MVP (2007-2010) MS Vendor - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

    Friday, January 4, 2013 5:25 PM
  • Have you setup the firewall configuration, as I suggested?

    Olaf Helper

    Blog Xing

    Friday, January 4, 2013 5:40 PM
  • Olaf: the problem then is that it would be exposed to the internet which is not what i want :)

    is there a way to restrict that but still be able to open the firewall?


    C# MVP (2007-2010) MS Vendor - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

    Friday, January 4, 2013 5:41 PM
  • Ahmed,

    You don't need to turn off the firewall completely. You can either open ports as Olaf suggested or you can add sqlservr.exe to application exception list and keep firewall running. 



    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, January 4, 2013 5:52 PM
  • Thanks Dmitri. I tried adding sqlserv.exe to the exception list but still get the same problem (to both inbound and outbound rules allowing it access) - cannot connect to it from the host machine.

    C# MVP (2007-2010) MS Vendor - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

    Friday, January 4, 2013 5:56 PM
  • is there a way to restrict that but still be able to open the firewall?

    It depends on the firewall features. You can unblock ports only "global", then no. If you can setup firewall rules by the caller ip, then you could unblock the port for only internal ip addresses.

    Olaf Helper

    Blog Xing

    Friday, January 4, 2013 6:02 PM
  • Ahmed, assuming you can connect when firewall is off, it means something is not right with exceptions setup. Are you sure that this exception is enabled in firewall profile that used by the host connectivity? Also, can you please try to add sqlbrowser.exe as the exception? 



    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by ahmedilyas Friday, January 4, 2013 6:18 PM
    Friday, January 4, 2013 6:06 PM
  • Thanks all.

    Dmitri: firewall exception is enabled.

    I also added sqlbrowser.exe - and now it works! Thanks all and thanks Dmitri.


    C# MVP (2007-2010) MS Vendor - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

    Friday, January 4, 2013 6:18 PM