Solved

Problems connecting to Sql Server through the network

Posted on 2014-11-16
7
211 Views
Last Modified: 2014-11-17
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?
0
Comment
Question by:LearningToProgram
7 Comments
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 100 total points
ID: 40446367
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.
0
 
LVL 7

Accepted Solution

by:
Phil Davidson earned 400 total points
ID: 40446373
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)?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40446382
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40446768
What's the error message?
0
 

Author Comment

by:LearningToProgram
ID: 40447095
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.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40447167
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.
0
 

Author Comment

by:LearningToProgram
ID: 40448641
That has fixed it for now. Thanks for your help!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question