Solved

Problems connecting to Sql Server through the network

Posted on 2014-11-16
7
202 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 68

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 82

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

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 68

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

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.

Join & Write a Comment

There have been a lot of times when we have seen the need to enter a large number of DNS entries in a forward lookup zone. The standard procedure would be to launch the DNS Manager console, create the Zone and start adding new hosts using the New…
Network ports are the threads that hold network communication together. They are an essential part of networking that can be easily ignore or misunderstood, my goals is to show those who don't have a strong network foundation how network ports opera…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now