11

I have Windows 7 with SQL Server 2008 and SQL Express 2012 on it. I have also installed win xp on two vmware machines. I am trying my best to connect to sql server on host machine from the vm machines using management studio, but no luck. I had problem even pinging the host machine and visa versa. When I turned off the firewall, I was able to ping the host machine. I tried to add the vm machine's IP to allow access to host machine, but even that didnt work.

I have added a custom rule from here http://www.rackspace.com/knowledge_center/article/creating-an-inbound-custom-allow-rule-for-windows-firewall-windows-2008

I was running hotspot shield which i turned off, but still no luck. I have allowed remote connection on my sql server. but still not working. Can someone help here.

This is the error I get.

enter image description here

Here is the setting for vm machine. enter image description here

When the firewall is on, cant ping, but can ping when its off.

Regards

Parminder

Parminder
  • 3,088
  • 6
  • 37
  • 61
  • What error are you getting when you try to connect? Are you connecting in SSMS by name or by IP? –  Jan 20 '14 at 12:56

3 Answers3

20

You need to configure SQL Server to listen on the TCP protocol. To do this, click Start | All Programs | Microsoft SQL Server 2012 | Configuration Tools and open SQL Server Configuration Manager.

  1. In SQL Server Configuration Manager, expand SQL Server Network Configuration in the console pane.
  2. Click Protocols for instance name. (The default instance is Protocols for MSSQLSERVER).
  3. In the details pane, right-click TCP, it should be Enabled for the gallery images by default. For your custom images, click Enable (if its status is Disabled.)
  4. Right-click on TCP/IP and select Properties.
  5. Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
  6. Make sure that TCP Dynamic Ports is blank.
  7. Make sure that TCP Port is set to 1433.
  8. In the details pane, right-click SQL Server (instance name) (the default instance is SQL Server (MSSQLSERVER), and then click Restart, to stop and restart the instance of SQL Server.

Then, you need to open TCP ports in the Windows firewall for the default instance of the Database Engine. To do this, click Start | All Programs | Administrative Tools, and open Windows Firewall with Advanced Security.

  1. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  2. In the Rule Type dialog box, select Port, and then click Next
  3. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine (1433 for the default instance). Click Next.
  4. In the Action dialog box, select Allow the connection, and then click Next.
  5. In the Profile dialog box, select Domain, Private, Public, and then click Next.
  6. In the Name page, set the Inbound Rule's Name to SQLServerPort and click Finish. Close Windows Firewall with Advanced Security window.

Hope this help. Best Regards

ocuenca
  • 38,548
  • 11
  • 89
  • 102
6

This comes straight from MSDN blog in regards to network error 26. Details here

1) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \ If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

If that still fails you might want to run PortQry.

You can download PortQry from here details on application here, run "portqry.exe -n yourservername -p UDP -e 1434". If this command returns information and it contains your target instance, then you can rule out possiblity 4) and 5) above, meaning you do have a SQL Browser running and your firewall does not block SQL Browser UDP packet. In this case, you can check other issue, e.g. wrong connection string.

  • thanks mate. I installed it. it resolve the ip address as my laptop name, and says port is filtered. what does that mean ? – Parminder Jan 20 '14 at 21:21
  • 4
    `Make sure SQL Browser service is running on the server. `. Lifesaver... Thanks! :) – user2173353 Jun 07 '16 at 08:38
  • SQL Browser running did the trick. Also small correction: you need Any port (not just UDP) on 1434 for the SQL Browser in the firewall to allow. – Jan Nov 04 '16 at 15:25
0

Note if you're running SQLExpress it still might not work after applying the steps from octavioccl's comment. What fixed it for me was replacing my SQLExpress 2019 with SQL Server Developer 2019, applying the mentioned SQL Network config and Firewall settings and then I was finally able to connect to the Host's SQL server just by using the hostname, no IPs and ports.

Ivan Breslauer
  • 41
  • 4