Problems connecting to Sql Server through the network

I have a windows server 2012 machine that has two sql server databases. One uses sql server express 2008R2 and the other uses Sql Server Express 2012.
I have an Access application that uses odbc dsn connections to link tables to both of the sql server databases.

Until a few days ago both of them were working fine.

Then different things started happening;
The first thing was that I had to turn off the firewall on the server in order to connect to the databases using Access.
Then a few days later, I started losing connection even with the firewall turned off.
Both databases can be contacted using a ssms on the server where the databases are located.
Currently the 2012 database cannot be contacted using ssms from a workstation on the network. That same workstation can contact the 2008 database.

Earlier today, the Access application could only contact the 2012 sql server database.
Now it can only contact the 2008 sql server database database.
This is with windows firewall on the server turned off completely.
I have a feeling the problem is network related, but I'm not sure what it is.

Sql server network configuration settings:
protocols for 2012== shared memory, named pipes, tcp/ip   all enabled
protocols for 2008 == shared memory, named pipes, tcp/ip   all enabled

protocol: TCP/IP: Enbabled:  YES Keep Alive: 30000  listen all : YES

Ip Addresses: All ip addresses have enabled set to NO except the main ip address for the server, which is set to YES.  Dynamic Port set to 0
Native Client configuration: TCP/IP default port set to 1433
Any ideas?
Who is Participating?
Phil DavidsonConnect With a Mentor Commented:
If the firewall was turned off, I don't see how the network could effect things.  Was a router between the computer with Access and the SQL Server instances changed? (e.g., new firmware, new configuration settings)

Based on the description, I don't think it is a network problem.  I think the problem is related to the server.  Only one instance of SQL Server is working at a given time.  Are the SQL Server services for both instances (2008 and 2012) on when only one is working?  I would look at the started and running services, and take screenshots, and compare and constrast them when only one instance is working.  The time 2008 is working may look different from the time 2012 is working.

It seems like one instance is crowding out the other.  Was a Windows update recently applied?

Is the server healthy (e.g., not using too much RAM, CPU or hard disk space)?
QlemoConnect With a Mentor Batchelor and DeveloperCommented:
Make sure the SQL Browser Service is running all the time. It is required to negotiate the (dynamic and unknown) TCP/IP port of MSSQL instances different than the default one. The SQL Browser Service listens to udp/1434, so it is worth checking with netstat -ab whether the service really listens to this port.
Dave BaldwinFixer of ProblemsCommented:
You can't tell which SQL Server you are connecting to if they both use the same IP address and port.  You have to do something to tell them apart.  That's why 'sqlbrowser.exe' exists, to find 'named instances' on the machine.
Will You Be GDPR Compliant by 5/28/2018?

GDPR? That's a regulation for the European Union. But, if you collect data from customers or employees within the EU, then you need to know about GDPR and make sure your organization is compliant by May 2018. Check out our preparation checklist to make sure you're on track today!

Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the error message?
LearningToProgramAuthor Commented:
Hi Olemo, thank you for your suggestion. I checked sql browser and it is running and the port is open.

Phil, I checked the sql server service for both 2008 and 2012, and the "log on as" was different for sql server 2012, which wasn't running at the time. It had the log on as set to  an account (NTservice\MSsqlsqlexpress2012) instead of Local system account like the 2008 service. I tried changing 2012 to local system account. I can now connect through Access on both sql servers. When I get to the office I'll check if I can connect through ssms as well. and let you know thanks.
QlemoBatchelor and DeveloperCommented:
The service account should not have any effect on availability, and I doubt that was the reason. "which wasn't running at the time" sound more like the reason - you can't reach an instance not running, of course.
LearningToProgramAuthor Commented:
That has fixed it for now. Thanks for your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.