Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problems connecting to Sql Server through the network

Posted on 2014-11-16
7
Medium Priority
?
234 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 71

Assisted Solution

by:Qlemo
Qlemo earned 400 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 1600 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 84

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
Independent Software Vendors: 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 52

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 71

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

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