How to find the SQL Server 2005 port

[Cathy]

I am using a System.Data.SqlClient.SqlConnection to connect to a SQL
Server 2005 database. I instantiate the SqlConnection with a
connection string...

SqlConnection sqlConn = new SqlConnection(@"Network
Library=DBMSSOCN;Data Source=(local)" +
",1433;Database=CATHYDB;User
id=cathy;Password=pswd;");

When this code runs I can guarantee that everything about it will be
constant, except the port (here I've coded it as 1433).

I want to avoid hard-coding the port number. So, how can I either

a) find out which port SQL Server 2005 is using, or
b) use a different approach which doesn't require a hard-coded port
number?

Many thanks,

Cathy

 

[Kevin Spencer]

Hi Cathy,

There are 65,536 TCP ports on any machine. So, you can't exactly "discover"
the port, and that's not generally done. It is always expected that the
client will know what port to look at.

If you don't want to hard code the port number into your Connection String,
host the SQL Server on its' default port.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

 

[Mr. Arnold]

I am using a System.Data.SqlClient.SqlConnection to connect to a SQL
Server 2005 database. I instantiate the SqlConnection with a
connection string...

SqlConnection sqlConn = new SqlConnection(@"Network
Library=DBMSSOCN;Data Source=(local)" +
",1433;Database=CATHYDB;User
id=cathy;Password=pswd;");

When this code runs I can guarantee that everything about it will be
constant, except the port (here I've coded it as 1433).

I want to avoid hard-coding the port number. So, how can I either

a) find out which port SQL Server 2005 is using, or
b) use a different approach which doesn't require a hard-coded port
number?

SQL Server no matter what version it is from 6.0 to 2005 uses ports 1433 and
1434. Those are the standards. SQL Server is not going to be listening on
any other ports.

And why are you hard coding the port number? The only database solution I
have seen where the port number had to be give was Oracel using NHibernate.

 

[Marc Gravell]

Sorry to be the bearer, but you can specify any port you like (within
reason) via the network configuration tool / server network utility.
1433 is just the default (although a very common default [i.e.
probably 99%+])

Marc

 

[Jon Skeet [C# MVP]]

SQL Server no matter what version it is from 6.0 to 2005 uses ports 1433 and
1434. Those are the standards. SQL Server is not going to be listening on
any other ports.

Well, those are the defaults - for the default instance. It can be
changed even for the default instance, and I *think* each instance
will have its own port, so if you've got more than one instance then
at least *one* of them will be listening on a port other than the ones
you listed.

Jon

 

[Mr. Arnold]

Well, those are the defaults - for the default instance. It can be
changed even for the default instance, and I *think* each instance
will have its own port, so if you've got more than one instance then
at least *one* of them will be listening on a port other than the ones
you listed.

But I guarantee that 99.9% of SQL Servers are not listing on any other ports
and are using the standard ports. If different ports were being used, even
if I was pointing to another instance, that would be a know factor, and I
wouldn't be trying to come up with something to find SQL Server on high
ports.

 

[Jon Skeet [C# MVP]]

But I guarantee that 99.9% of SQL Servers are not listing on any other ports
and are using the standard ports.

Agreed. That's not the same as saying that SQL Server is not going to
be listening on any other ports though.

Jon

 

[Laura T.]

True.
Every instance uses a different TCP port. Named instances use, by default,
dynamic ports, and will change on every restart.
A named instance can be bound to a specific port but that's not necessary
with SQL 2005, because of SQL Browser service.
SQL Browser implements a lookup service via UDP 1434 (that will not change
easily) that the client uses to find out the port the named instance is
using.

http://msdn2.microsoft.com/en-us/library/ms181087(SQL.90).aspx

 

[Mark Rae]

Agreed. That's not the same as saying that SQL Server is not going to
be listening on any other ports though.

Quite so - one of my current clients has all of its SQL Server instances
running on ports above 50,000...

It's the usual story - somebody somewhere read something which said that
running SQL Server on the default port (even on internal networks) was a
massive security risk, so they went into panic mode and changed them all...

 

[Mr. Arnold]

Agreed. That's not the same as saying that SQL Server is not going to
be listening on any other ports though.

You can change a Web server not ot listen on TCP 80 as well. But since I am
not a DBA and would have no reason to know that SQL can run on other ports,
because that's all I have seen is the standards for connecting to SQL Server
in the many shops I have worked in, then so be it, as I have actually
learned something new about SQL Server.

And I'll also say that a vast majority of so called SQL Server Admins don't
know it either. They would just put up another SQL Server box.