Can not connect to SQL Instance without specifying Port.

[Vikki McCormick]

Hi,

Having a connectivity issue here.

We have a default instance which is not being used (totally inactive and shut down), and a named instance on a SQL Cluster with 2 physical machines, and 2 VMs. This cluster is active / passive. For a very long time everything was working fine. We recently got a call that none of the developers could connect to the instance without specifying the port. So we checked to see that TCP/IP was turned on, we checked the browser service, we made sure all the required ports were open. All looks okay.

We then reboot the offline server, and failed over the active server and then reboot that one.

Still no luck.

We checked the cluster setup. We are using dynamic ports and the Ipall was auto assigned 56191. That is the port you have to designate in order to connect. We have a reporting server on another box which requires the port to connect.

We had to set up Kerberos to allow for a double hop situation. We registered service accounts and servers and now are getting an error which states that the server is unknown, the error message lists the name of the server with the port.

Of course locally we connect fine. We think there is something wrong with the SQL browser but are not sure of the best way to troubleshoot.

We are using MS SQL Server 2008 on a Windows 2008 R2 box.

Any ideas? Thanks for your help.

[Vikki McCormick]

I am sorry if I did not word this issue properly. English is a stumbling block for me so I understand if there is criticism....or if there is something so obvious, and I have missed it, that I may not warrant a response, but any help even a criticism would be appreciated. As long as it points me in another direction. I am reading tons of material and not finding what could be wrong in the setup or anything that might indicate why a Port Number suddenly became required. I did not set up the cluster or instance but I see nothing that stands out as obvious in the setup, or on the firewall and the browser is running. It is not running on the passive node but when it fails over the cluster will automatically start that.

If you use a non-standard port do you always have to designate the port when connecting? I thought sql browser still could resolve the port. Am I wrong?

Thanks.

[NeoPa]

Vikki,

I am possibly one of the most demanding of experts when it comes to the clarity and the proper expression of questions, and I was impressed by how well your question was presented, so I wouldn't worry on that score. Your question is beyond my limited SQL experience though I'm afraid. What I will do for you though, is post something in the Experts area drawing attention to this question.

I'm afraid not all forums offer prompt responses all the time. The site is staffed exclusively by volunteers and so they are free and available only when they are (most having busy working lives already).

Anyway, good luck with your question :-)

[Vikki McCormick]

NeoPa,

Thanks very much for your response. I am not the best communicator and a lot of what I know I have self taught so I wonder sometimes if I am speaking the correct way. I appreciate you taking that extra step. I have had plenty of views on this one but no takers as of yet.

I am now using Network Monitor to trace the packets and the only clue so far is that the packet is "disreguarded" when no port is specified. I believe it is possible there may be some corruption. I am discovering other errors. The DBA before me setup a maintenance plan and that has not been running and throws an error that Integration Services must be running so that service used to be working also but now is not.

I am going to try and do some integrity checks tonight.

It is a high traffic production server and I am very concerned it is heading for disaster. We received additional phone calls from the developers that more and more jobs are failing. The backups are also failing so this could turn out to be one of those fun nights. If we can't find something soon. Okay back to work. Thanks again. Wish me a miracle.

Vikki

[NeoPa]

I certainly wish you a miracle Vikki. I just want to be clear that I'm not providing any technical help here, as experts often leave a thread if it seems like it's already being handled. Best of luck anyway.

I have put out a call-to-arms for you, and we do have a number of reliable experts in SQL Server that I've been helped by in the past. I expect you must be States-side as you're referring to this evening as upcoming still, so hopefully there's still time for someone to assist, even at this late stage.

Having said that of course, the question, though clearly explained, is equally clearly not trivial. But, have yourself a miracle and may this evening be a peaceful one :-)

[Vikki McCormick]

Thanks NeoPa. I really appreciate your assistance. I hope I can also be of help some time.

I think I got a good answer on msdn. There was a fix for this on SQL Server 2008 and it turns out that it was only a partial fix. It does not apply to X64 machine.

"It might be a SQL browser is not cluster-aware and listens on IP ANY. Meanwhile it might be the response UDP packet is dropped by the firewall and /or IPSec. Windows Firewall does not drop the packet. However, a third-party firewall may drop the packet. In addition, IPSec may also drop the packet if IPSec policy is enabled on the client and it can not establish a trust connection between the client and server."

They added a fix for this on SP2 but I guess it doesn't work for all machines.

We are going to try and apply SP3 to see if that works if not we are going to rebuild in the whole server on 2008 R2.

Thanks again.

[NeoPa]

That sounds like a result Vikki. Good for you :-)

I would certainly consider upgrading the server to R2 anyway. I generally find that when a product even has an R2, the previous version is FUBAR. Lots of time and energy saved by avoiding any such product in my view (which may be somewhat harsh in some circumstances, but will never lead you too far astray). Personally, I think of Office 2010 as Office 2007 R2, and Windows 7 as Vista R2, but I never said only those with an R2 were duds did I :-D

[Vikki McCormick]

Agreed. R2 is the way to go. After further investigation, I concluded that we have the required service packs, but for some reason they are not working.

I think it might be corruption now. There are 2 seperate errors occuring both were fixed in SP2. We also had a huge power outage a short time ago, maybe that caused the errors to occur though no way to confirm that.

Due to work load the senior admin wants to try to apply service pack 3 till there is some down time. The upgrade will be time consuming, but when stuff like this starts happening I think it will continue to see problems, and you are right, you can save yourself tons of time. On to the next project I guess.

I will follow up with the results in case some one else runs into something similar.

Have a good day. Vikki

[NeoPa]

Vikki McCormick:
I will follow up with the results in case some one else runs into something similar.

It's a pleasure dealing with you Vikki :-)

[Vikki McCormick]

Follwing up as promised. It was definitely corruption. We installed SP3, and rebooted the cluster. All is working fine.

Thanks.

Vikki

[NeoPa]

Excellent, and thanks for following up.

May I ask, was anything done to the data (to recover or fix it), or was the upgrade to SP3 all that was required?