Non default port

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Victag - 13 Mar 2008 06:49 GMT

I am using SQL 2005 and have multiple instances installed on multiple
servers. None are on the default port. When I connect to box 01
without specifying the port in the connection string, it connects.
But on box 02, it will only connect to the instance if I specify the
port name. Is this because it will only connect to the default
instance without specifying a port? or is there something else I need
to have setup on box 02 to make this work?

thanks in advance :)

Reply to this Message

Tibor Karaszi - 13 Mar 2008 08:53 GMT

See if the SQL Server Browser service is started on box 02.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>I am using SQL 2005 and have multiple instances installed on multiple
> servers. None are on the default port. When I connect to box 01
[quoted text clipped - 5 lines]
>
> thanks in advance :)

Reply to this Message

Dan Guzman - 13 Mar 2008 12:26 GMT

> Is this because it will only connect to the default
> instance without specifying a port? or is there something else I need
> to have setup on box 02 to make this work?

Check to ensure the SQL Browser service is running as Tibor suggested.
Another possible cause is that UDP port 1434 is blocked.

When you connect to the default instance, TCP port 1433 is used by default.
When you connect to a named instance without a specifying a port, the client
requests a list of the named instances and corresponding ports via UDP port
1434 (this is the SQL Browser service). The client then determines the
named instance port.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>I am using SQL 2005 and have multiple instances installed on multiple
> servers. None are on the default port. When I connect to box 01
[quoted text clipped - 5 lines]
>
> thanks in advance :)

Reply to this Message

Victag - 13 Mar 2008 17:57 GMT

On Mar 13, 4:26 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:

> > Is this because it will only connect to the default
> > instance without specifying a port? or is there something else I need
[quoted text clipped - 26 lines]
>
> - Show quoted text -

Thanks folks! I have checked and the Browser service is disabled on
both boxes (event he working one), so I could try turning it on, but I
would rather explain/understand what the difference is between these
boxes. I have also confirmed that no ports are blocked at all. Any
other suggestions?

Reply to this Message

Tibor Karaszi - 13 Mar 2008 19:12 GMT

<<Thanks folks! I have checked and the Browser service is disabled on
both boxes (event he working one), so I could try turning it on, but I
would rather explain/understand what the difference is between these
boxes. I have also confirmed that no ports are blocked at all. Any
other suggestions?>>

We can only explain how things work:

When you connect to a default instance (no backslash and instance name), the data access components
will connect to port 1433. The SQL Server service by default listens to 1433 for a default instance
(well-known port).

The port number for a named instance is determined dynamically the first time you start the
instance. SQL Server asks Windows for an available port, uses it and stores the port number in the
registry (as you can see using SQL Server Configuration Manager). It will try to use the same port
number on subsequent start-ups.

If you from the client app only specify machine name/IP backslash instancename, then you need some
component to translate this instance name to a port number. That component is SQL Server Browser
service. The client data access components connects to the SSB service using port 1434 UDP and
passes the instance name, and the SSB service returns the port number. You can also from the client
app specify machine/IP comma portnumber, so there would now be no use for the SSB service. SQL
Server 2000 didn't come with an SSB service so for 2000 the database engine did this job (listening
on port 1434).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

On Mar 13, 4:26 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:

> > Is this because it will only connect to the default
> > instance without specifying a port? or is there something else I need
[quoted text clipped - 30 lines]
>
> - Show quoted text -

Reply to this Message

anxcomp - 14 Mar 2008 00:59 GMT

Hi,

You can access named instances (if you know port number) in this way:

tcp:server_name,port_number

Signature

Regards,
anxcomp

Reply to this Message

Victag - 14 Mar 2008 18:32 GMT

On Mar 13, 11:12 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:

> <<Thanks folks! I have checked and the Browser service is disabled on
> both boxes (event he working one), so I could try turning it on, but I
[quoted text clipped - 69 lines]
>
> - Show quoted text -

Thanks again. I have made progress and found that the boxes that are
not working have many client aliases created on them, some of which
appear to be invalid. When I deleted the aliases that did not exist
on the server that is working, it fixed them. So now that I know how
to fix it, I am curious to know how these bad aliases got created
since we did not create them manually.

Reply to this Message

anxcomp - 17 Mar 2008 00:32 GMT

>I am curious to know how these bad aliases got created
> since we did not create them manually.

Did you install Visio? For example Visio installation process create for me
some alias. I have to delete it manually.

Signature

Regards,
anxcomp

Reply to this Message