Solved

Problems connecting to Sql Server through the network

Posted on 2014-11-16
7
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 70

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 70

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 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