SQL Server 2008 R2 stop working over local network (error 26)

Question

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.

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.)

Yet, every time I try to connect, I get the following 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.

I can ping my staging computer:

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

And even access it's phpmyadmin via my browser (http://staging/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

Answer 1

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.