Why can't I connect to 2 sql servers with SSMS, but others can?

Question

Why can't I connect to 2 sql servers with SSMS, but others can?

WayneS

SSC Guru

Points: 95460
More actions
June 8, 2010 at 3:15 pm

#225460

I have three sql server instances that I usually cannot connect to. At all times, the other DBAs here can connect to these servers.
1. SQL 2005 Dev on a stand-alone computer. When I cannot connect to it, PING does resolve to the proper IP address. I cannot RDP into the server, or connect with SSMS (2008).
2. Two SQL 2005 Enterprise instances on a cluster.When I cannot connect to it, PING does resolve for the instance names, and the two individual node names. I cannot RDP into the instance names, but I CAN RDP into the nodes. I cannot connect to either instance with SSMS (2008)
Periodically, I CAN connect to these servers. Invariably, I will lose the connection - anywhere from 5 minutes to > 1/2 hour.
When I cannot connect to these servers, by either RDP or SSMS, the other DBAs here CAN, with both methods. They do utilize SSMS 2005.
We have eliminated HIPS / firewall by completely disabling them on both the servers and my laptop.
These three instances, out of nearly 40, are the only ones that I have an issue with. Most of the other instances are 2005, with a scattering of 2000 in there. There are also 3 other clusters that I don't have an issue with.
Any ideas? I'm getting ready to start throwing things around here... :w00t:
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

Are you seeing any error messages when your connections fail or when trying to connect and it fails? Either in SSMS or the SQL error logs?

Do you see different behaviour if you try connect with the IP address and port number?

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

WayneS SSC Guru Points: 95460 More actions · Answer 2

The error message that I get is:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Error connecting to '<my server>'.
------------------------------
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

I have all computers set up through "SQL Server Configuration Manager" aliases (since we use a non-default port).

I forgot to mention:

Servers: Windows Server 2003 Enterprise Edition SP2

Laptop: Windows XP Pro SP3

Trying to connect via Windows Authentication. Have attempted with SQL Auth.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Leo.Miller SSChampion Points: 13051 More actions · Answer 3

From the timeout error it looks like the problem is load on the database server. It may be worth running some Performance counters. like CPU usage to see if your problems co-incide with high CPU.

Also check if there are other apps getting timeout errors.

Also have you fiddled with the timeout setting on your laptop? Timeout is typically 60sec, but you, or some app you've installed could have butchered this. This could explain why only you get the error, and only intermitently.

Another thing to check is your NIC. I recomend you don't reley on AutoDetect, but try set it to the highest setting it will use and FULL Duplex.

That's about all I can think of at the moment.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Lowell SSC Guru Points: 323518 More actions · Answer 4

Wayne it's been a while, but i had a similar issue;for me, it was my laptop, which traveled between different networks a lot. i thought it was related to WINS/DNS and some caching, but i'm no expert.

for a while i would ipconfig/release and then renew, and that would fix it sometimes, but the thing that fixed it completely for me was when the IT guys gave me a DHCP reservation, so my laptop always had the same ip address when it hit the network.

give the ip config thing a try and see if that fixes it for you first...at least that might imply that the issue is with the network.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 5

Leo.Miller (6/8/2010)
From the timeout error it looks like the problem is load on the database server. It may be worth running some Performance counters. like CPU usage to see if your problems co-incide with high CPU.

No, there is a very minimal load on these servers.

Also check if there are other apps getting timeout errors.

Just sql apps... profiler, ssms.

Also have you fiddled with the timeout setting on your laptop? Timeout is typically 60sec, but you, or some app you've installed could have butchered this. This could explain why only you get the error, and only intermitently.
Another thing to check is your NIC. I recomend you don't reley on AutoDetect, but try set it to the highest setting it will use and FULL Duplex.
That's about all I can think of at the moment.
Leo

Thanks Leo. I'll check into these other items.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 6

Lowell (6/8/2010)
Wayne it's been a while, but i had a similar issue;for me, it was my laptop, which traveled between different networks a lot. i thought it was related to WINS/DNS and some caching, but i'm no expert.
for a while i would ipconfig/release and then renew, and that would fix it sometimes, but the thing that fixed it completely for me was when the IT guys gave me a DHCP reservation, so my laptop always had the same ip address when it hit the network.
give the ip config thing a try and see if that fixes it for you first...at least that might imply that the issue is with the network.

Lowell, interesting idea, and definitely worth trying.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2