Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

Excuse me if this is too open-ended but,

I have a Windows Server 2008 R2 Standard. It's running a SQL Server 2008 database server. (all 64bit if that matters). Earlier today they had a power outage. After the power came back on no clients could connect to the database from any machine other than the server itself.

I made sure Shared Memory, TCP/IP, and Named Pipes were all still enabled (which they were) and the SQL Server (MSSQLSERVER) and SQL Server Browser services are running.

I have a web server on the same network running a php app that only since the power outage can no longer connect.

I can still ping the database server and I can connect to the database locally. Any idea what the problem could be?

enter image description here

share|improve this question
Name resolution? – joeqwerty Aug 3 '11 at 19:28
Can you ping it from the other machine? – Alan Aug 3 '11 at 19:34
probably something like that. I also added a new firewall/router but that was 4 days ago and it's been working fine since then. My only DNS server is the ISPs server but again, that's not new. – Jeff Aug 3 '11 at 19:39
yes, I can ping the ip address but my connections are by the sqlserver name (skynet2). – Jeff Aug 3 '11 at 19:40
1  
Do the clients connect to the SQL server by name or ip address? If it's by name then you have a name resolution problem, which needs to be fixed, which is best fixed by installing an internal DNS server. – joeqwerty Aug 3 '11 at 19:49
show 1 more comment

5 Answers

up vote 1 down vote

Steps to troubleshoot SQL connectivity issues

share|improve this answer
I'm currently stepping through this on (social.technet.microsoft.com/wiki/contents/articles/…) but will try yours too. – Jeff Aug 3 '11 at 20:09
up vote 1 down vote
  1. Check events in system and application logs in computer management (compmgmt.msc)
  2. Check status all services for MS SQL server
  3. On the Firewall (firewall.cpl) - advanced Setting create new INBOUND rule for port TCP 1433
  4. Take disable firewall and test connection from client (only for the test, after test enable firewall).
share|improve this answer
up vote 1 down vote

As others have said in comments, it really sounds like this is a name resolution issue. Either that or you actually have another named instance of SQL Server on the machine and the service for that instance is not running. I somewhat doubt that's the case based on your description, but just throwing it out there anyway.

Is your server configured to get its IP from DHCP (I really hope not)? If so, then is it a reserved address? If not, then that could be your problem The server pulled a different IP address from DHCP than what it was using before.

How are you doing name resolution internally in the network? Do you have internal addresses in the dns zone on the name server hosted by your ISP? If not and you are sure you don't have an internal dns server then the web server and clients have to have an entry in the hosts file for your SQL server. Check to make sure that hosts file entry is the same as the IP address the SQL box has.

share|improve this answer
the server does have a static IP but the DNS is just listed as the ISPs. I planned to fix this but it's always worked fine until today. I added "192.168.1.202 skynet2" to the hosts file to force the name resolution to work at least for the web server but it still won't connect. – Jeff Aug 3 '11 at 20:49
What IP does it give you on the web server when you ping skynet2? Have you tried an ipconfig /flushdns? – squillman Aug 3 '11 at 20:56
pingging skynet2 works (reply from 192.168.1.202) but that's only because of the hosts file for that machine. But the way I understand it, the host file is as good as anything else, right? – Jeff Aug 3 '11 at 21:01
Yep, now we can start focusing on application level connectivity issues. And just to confirm, the default instance is the only SQL instance on the server? – squillman Aug 3 '11 at 21:14
As far as I know. I'll add some screenshots to the question. I think this shows that there's only one instance. – Jeff Aug 3 '11 at 21:53
up vote 1 down vote

The fact that you can connect to the SQL instance locally tells me that Shared Memory is working, but it doesn't tell us that network connectivity is working. I would run "netstat -a -n -o" on the server and make sure that the SQL server process is in fact listening on the port you believe it to be listening on. If that checks out then try to telnet to port 1433 on the server from one of the affected clients/hosts.

share|improve this answer
the netstat command gave me this. \\ (line 1) TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 1204 (line 2) TCP [::]:1433 [::]:0 LISTENING 1204 – Jeff Aug 4 '11 at 1:53
sorry about the formatting :-) – Jeff Aug 4 '11 at 1:53
Both lines tell me that PID (process ID) 1204 is listening for incoming connections on port 1433 on all interfaces for both IPv4 and IPv6 protocols. Does PID 1204 belong to SQL Server (sqlservr.exe)? – joeqwerty Aug 4 '11 at 2:45
After a reboot MSSQLSERVER has PID 1812. This matches the netstat command after the reboot. – Jeff Aug 4 '11 at 13:05
OK, so we know that the sqlservr.exe process is listening for incoming connections on port 1433, is there a firewall rule preventing inbound connections? The next step would be to run a packet capture on the server and the client to see what's happening at the protocol level. – joeqwerty Aug 4 '11 at 13:31
up vote 0 down vote accepted

These were all GREAT responses and I appreciate the help. Here's what the problem was. It would seem that the 'Network Location' setting was changed to 'Public'. I personally don't see how a power outage could do that but I suppose stranger things have happened. I changed it to 'Private' and all is back to normal.

share|improve this answer
Can a power outage create a NEW network location and this new location default to Public? – Jeff Aug 4 '11 at 15:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.