2

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.

Improve this question
2
  • What are you connecting from, SSMS? What errors are you getting? Commented Jul 2, 2009 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! Commented Jul 2, 2009 at 6:14

4 Answers 4

Reset to default
1

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.

Improve this answer
5
  • it's running ... i can connect just fine locally Commented Jul 2, 2009 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. Commented Jul 2, 2009 at 6:04
  • the port is wide open ... i didn't do telnet, but id did canyouseeme.org Commented Jul 2, 2009 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? Commented Jul 2, 2009 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 :) Commented Jul 2, 2009 at 7:19
0

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.

Improve this answer
12
  • 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 Commented Jul 2, 2009 at 6:36
  • how do i use the aliases when i try to connect using SSMS 2008? Commented Jul 2, 2009 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? Commented Jul 2, 2009 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 Commented Jul 2, 2009 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. Commented Jul 2, 2009 at 10:51
0

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).

Improve this answer
1
  • Beat me by less than a minute :-( Commented Jul 2, 2009 at 16:51
0

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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.