Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got MS SQL Server 2005 (default instance) and SQL Server 2008 (named instance) running on Vista Ultimate x86.

I have no problem connecting to both instances locally, but when I try connecting to them from another NETWORK - no luck. SQL Server 2005 works fine, but I can't connect to SQL Server 2008.

I've got TCP/IP and Named Pipes enabled on SQL Server 2008, and remote connections are allowed.

Am I missing anything?

P.S.: I changed dynamic port to 1444. I don't seem to find a way to specify a port number when connecting to named instance via MS SQL Management Studio.

P.P.S: connecting from SSMS 2008.

More information:

I've also opened up TCP 1444 on the firewall and the router. I can't think of anything else.

UPDATE:

The following works:

  • connecting from the same machine using the ServerName without any ports
  • connecting from the same SUBNET using the ServerName without any ports
  • connecting from the same SUBNET using the LOCAL IP (192...) without any ports

The following DOES NOT work:

  • connecting from the same SUBNET using the OUTSIDE IP ADDRESS (not 192...) without any ports
  • connecting from the different NETWORK using the OUTSIDE IP ADDRESS (not 192...) without any ports

SOLUTION:

It turns out the problem was with the router port forwarding setup, I was forwarding my custom port to the default port ... duh ...

I got this sorted out by rewording the question.

Thank you all for the help.

share|improve this question
    
What are you connecting from, SSMS? What errors are you getting? –  squillman Jul 2 '09 at 5:25
    
FYI - removed my answer about the alias. I was remembering something different where I had to connect to a named instance across an AD domain. Sorry 'bout that! –  squillman Jul 2 '09 at 6:14
add comment

4 Answers

Make sure that the SQL Browser is enabled and running. It's probably disabled or stopped.

Also make sure that the instance is configured to allow remote connections.

share|improve this answer
    
it's running ... i can connect just fine locally –  roman m Jul 2 '09 at 5:30
    
When you connect locally SQL usually doesn't connect over TCP until netbios isn't available. TCP doesn't require the use of the SQL Browser. It may need to be restarted. Can you telnet from the remote machine to the TCP port that the named instance is running on. –  mrdenny Jul 2 '09 at 6:04
    
the port is wide open ... i didn't do telnet, but id did canyouseeme.org –  roman m Jul 2 '09 at 6:25
1  
Well the first thing you'll want to do is close the firewall from the general internet. That's just an invitation to having your SQL Server broken into. Telnet from the other machine to ensure that there isn't a firewall issue between the machines. The machine connecting to the instance may be blocking outbound connections going to port 1444. What's the error when you try and connect from the remote machine? –  mrdenny Jul 2 '09 at 6:48
    
the error is "instance failure" ... i'm not a sysadmin, so i'm not really proficient with all the telnet stuff. i'm a developer trying to make sql 2005 and 2008 working on the same box :) –  roman m Jul 2 '09 at 7:19
add comment

Probably you need top open SQL Server Configuration Manager Open SQL Native Client Configuration Check Client protocols - TCP/IP should be enabled Check Aliases - define new alias for TCP/IP protocol

Aliases should be different for different named instances of SQL server

See also this KB article

Also, my question is do you really need to use TCP/IP ? I'm not sure about Vista, never installed there, but on Win 2003 server I set up server and all clients to work through named pipes.

share|improve this answer
    
i don't really know the whole deal with TCP/IP and NP, just want to get BOTH of the instances to work ... if you can provide more details about NP setup ... remember - 2005 is DEFAULT instance and 2008 is NAMED instance –  roman m Jul 2 '09 at 6:36
    
how do i use the aliases when i try to connect using SSMS 2008? –  roman m Jul 2 '09 at 7:04
    
you write "don't seem to find a way to specify a port number when connecting to named instance via MS SQL Management Studio" you cannot provide it in Studio, because SQL Browser provides such details for clients. Could you in the New Connection -> Login screen select Server Name, <Browse for more...> Can you see both instances in server browser? –  Bogdan_Ch Jul 2 '09 at 7:07
    
??? how do i browse from the remote computer ??? again, all works fine without port numbers and aliases LOCALLY, only 2005 works when connection from remote computer –  roman m Jul 2 '09 at 7:14
1  
I recommend you to update the text of the question... If anybody else will read it, chances are that he/she will never go thru all these comments.. Seems that it is not related to SQL Server, rather to router configuration, so I can't help you much, sorry. –  Bogdan_Ch Jul 2 '09 at 10:51
show 7 more comments

To connect with a SQL Server on another port, try doing 192.168.1.1,1444\INSTANCENAME (where 192.xxxx is your IP, and 1444 is the alternate port, and InstanceName is whatever your instance is called).

share|improve this answer
    
Beat me by less than a minute :-( –  Joshua Nurczyk Jul 2 '09 at 16:51
add comment

Here when we have multiple versions on the server, we use one of two connection strings:

  • 111.111.111.111\InstanceName (where instancename changes for each version)
  • 111.111.111.111,PortNum\InstanceName

Which works depends on the exact configuration for the server. As I only connect to these, I do not know the difference in connection strings.

share|improve this answer
add comment

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.