1

I configured both my development computer and my staging computer to be able to access SQL Server over the local network about one year ago (and everything worked fine until today). Yesterday evening, the head of our support team made some major changes in our physical switches and router equipments.

This morning, I had no internet access anymore on both my computer. What a mess!

We were able to to solve that problem by changing the default gateway (I have static IP configured) on both my computer.

Configure static IP

Now I can access my development database server from my staging computer using either: "192.168.1.58\SQLEXPRESS" and "JONATHAN\SQLEXPRESS", but I cannot access my staging development database server from my development computer using: "192.168.1.56\SQLEXPRESS" or "STAGING\SQLEXPRESS".

Still, I can access my staging database server on the computer itself using both ip and named pipe.

That problem kind of bugging me up since both my SQL Server are using the same configurations (note: my development computer works fine without "Named Pipes". Activating it on my staging computer didn't solve the problem.)

SQL Server Configuration manager

Yet, every time I try to connect, I get the following error message:

SQL Server error message

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) (.Net SqlClient Data Provider)

Both my SQL Server (SQLEXPRESS) (I log on as "Network Service" as suggested in some threads) and SQL Server Browser services are running (tried to restart them several time) and turned off my firewall.

My SQL services are activated

I can ping my staging computer:

Ping staging via CMD

Access the shared file via the network (\\STAGING):

I'm able to access the computer

And even access it's phpmyadmin via my browser (http://staging/phpmyadmin/).

I'm able to access phpmyadmin

I have the feeling I'm missing something obvious, but I can't seem to figure out what. I guess I need some more coffee ha, ha, ha.

Thank you all for your help

halfer
  • 19,824
  • 17
  • 99
  • 186
Jonathan Parent Lévesque
  • 1,902
  • 1
  • 25
  • 35
  • I read a comment from [this post](http://stackoverflow.com/questions/18060667/why-am-i-getting-cannot-connect-to-server-a-network-related-or-instance-speci) that might help. "... Open Sql Server Configuration Manager --> Protocols for --> TCP/IP --> IP Addresses(Tab). Go to the last entry IP All and mention TCP Port 1433. Now restart SQL Server () using services.msc..." – BJones Jul 14 '16 at 15:57
  • 1
    @bjones Thank you, I just found the problem, my developpment computer was on the "*work network*" and my staging computer was on the "*public network*". It seems that there are additional restrictions with a *public network* even with the firewall turned off. I will write an answer for that since nobody over the net suggest that possibility. – Jonathan Parent Lévesque Jul 14 '16 at 16:01

1 Answers1

0

I'll write the answer since nobody suggest that on the internet.

When I regain internet/network access on my computer, I selected "work network" on my development computer and "public network" on my staging computer.

After playing some with the physical devices, the tech. who made the change suggested me to check out on which network I was (which was the same network "Network 4" except for being public vs work).

According to http://www.dummies.com/how-to/content/how-to-change-a-network-type-in-windows-7.html :

Windows 7 puts up your PC’s shields. Windows Firewall plugs almost all its openings and protects its internal programs for being connected to potentially hostile network.

But it seems to be a little bit more complicated than this since I had completely turned off my firewall on my staging computer. Beware of these additional public protections when configuring your SQL Server.

Jonathan Parent Lévesque
  • 1,902
  • 1
  • 25
  • 35
  • That is not an issue for SQL Server, but for all network processes. By being on a different network location some services will not work, like authentication services for example. The hosts are not trusted, etc. It all seem as a simple error of mis-configuration on your part (or of your hosts) – Ricardo C Jul 14 '16 at 18:04
  • @Ricardo C from my part and I admit my culpability on this, but it worth mentioning the solution since nobody on the internet suggested the possibility of such could be a problem. Even the best of us can make obvious configuration error (that might seem not be so obvious when searching the root problem somewhere else). Please consider that this is not an issues on everything. After all, I was able to access my shared files and my MySQL databases via phpmyadmin (look on the screenshot in my question) even on a "public network". – Jonathan Parent Lévesque Jul 14 '16 at 18:15
  • 1
    I was not trying to toss guilt around, I should have worded that differently. I was trying to say that the issue is not specific to SQL Server, that if affects the whole system and all components, the fact that you were working with SQL Server is just coincidental. But I do see your point. – Ricardo C Jul 15 '16 at 17:59