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

x
?
Solved

Cannot Connect SQL Server Management Studio to remote instance of SQL Server

Posted on 2008-10-16
21
Medium Priority
?
1,008 Views
Last Modified: 2012-05-05
I have a named Instance of SQL Server 2005 on a DELL Poweredge server running Windows Server 2003. I cannot get a connection to the instance from a desktop computer running Windows XP service pack 2 using SQL Server Management studio. I am running Active directory on the server - which the client has joined and is logged into and the domain user has Admin Rights to the SQL server instance with a default database of Master.  

The SQL server Surface configuration is set up allowing remote connections
The server instance is set up to accept remote connections
The server instance is set up to accept unlimited connections
TCP/IP is activated in config manager.
I can ping the server by IP address and by name from the client machine
I have tested the connection with the firewall disabled.
I have tried connecting with Windows and SQL authentication

I get the same failure to connect error whatever I try. The only way I can see the instance is by using MSTSC and remoting in - or of course log into the server itself and runn SSMS.

If anybody can help it would be appreciated....Could ports be an issue?

Thanks

0
Comment
Question by:DavidHannen
[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
  • 11
  • 10
21 Comments
 
LVL 6

Expert Comment

by:mirzas
ID: 22730421
Are you running SQL 2005 Express Edition?

0
 

Author Comment

by:DavidHannen
ID: 22730473
No, I am running SQL server standard edition
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22730490
did you try connecting to  servername\instancename

What errors do you get?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:DavidHannen
ID: 22730541
Yes, I am connecting to the correct servername\instance. Please see attached screen shot.
error.doc
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22730575
Can you connect from the same machine?

Btw. check the services, is the instance running at all ?
0
 

Author Comment

by:DavidHannen
ID: 22730632
Yes, Either directly on the server, or via MSTSC all the instances on the server can be opened in SSMS. The SQL server service is on as is the agent service.
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22730878
I meant if you open start->run then type services.msc hit enter
and in the window that opens find all MSSQL server related services and see if they are running.
0
 

Author Comment

by:DavidHannen
ID: 22731005
Mirzas,
Yes that is what I was getting at above - both of the services are running and set as automatic start when the server starts. As part of my own diagnosis I did reboot the server and restart the relevant services
0
 
LVL 6

Accepted Solution

by:
mirzas earned 2000 total points
ID: 22731145
Can you do this..

start -> run -> cmd hit enter

type,   telnet server_ip_or_hostname 1433

if you get a blank telnet screen then the connection to the server was established.

If you have any firewalls on the client machine disable them too.

0
 
LVL 6

Expert Comment

by:mirzas
ID: 22731150
btw, try this from the client machine
0
 

Author Comment

by:DavidHannen
ID: 22731228
I received the error message Cannot connect to server.
I can ping though
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22731254
ok so it could be one of the following:


- the hostname or IP is wrong
- some firewall is blocking the connections
- SQL port is different for some reason

Looking at the screen shot you sent:

- your instance name could be wrong.
- be sure you are using the same username/password and settings when connecting localy


0
 

Author Comment

by:DavidHannen
ID: 22731413
I can assure that there is no issue with server\instance names or passwords - and I have tried connection via SQL and Windows authentication, each with failure. The same settings work when I am logged onto the server locally.

There isd only one firewall and have turned it off completely during this error resolution issue.

regarding your buttet points

What do you mean by 'SQL Port is different' what can I do to diagnose this?

My gut feeling is that this is where the error lies - somwhere in networking!

Thanks
0
 
LVL 6

Assisted Solution

by:mirzas
mirzas earned 2000 total points
ID: 22731453


You can do a netstat -ab on the server to see all open ports and applications that use them.

0
 

Author Comment

by:DavidHannen
ID: 22731611
I have run the command. The following string was returned which may be of interest (in that it relates to SQL server)

TCP IND-DOM-SRV1:1901 ind-dom-srv1.home.indicom.co.uk:0 LISTENING 1132 [SQLServr.exe]

Is this of help?
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22731748
It says that the port is 1901


Go to the configuration manager and check the default port for TCP/IP
It should be 1433


Check this:
http://www.databasejournal.com/features/mssql/article.php/3689846

0
 

Author Comment

by:DavidHannen
ID: 22731926
Thanks for this. I will work through it later as I have an appointment now. Thanks very much for all your help to date.
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22732064
You have fun now:)


I had some similar issues 2 days ago... spent some time doing magic tricks in order to get it working.
0
 

Author Comment

by:DavidHannen
ID: 22739180
The issue is resolved. Because I was using a custom port number on a named instance, rather than 1433 connection is not automatic. The quick solution is to ensure the SQL Browser service is running, which acts as a conduit to connection requests on the server.

Thanks for your input into this - I had spent hours on this prior to posting the question, and you have pointed me in the right direction very quickly. Cheers!
 
0
 

Author Closing Comment

by:DavidHannen
ID: 31506698
Thanks again, I got the server working AND learned some new stuff along the way
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22739256
I am glad I could help you.
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
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…

721 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