port number of SQL Server

views:

466

answers:

+2 Q:

port number of SQL Server

Hello everyone,

I am wondering what ports are used by SQL Server database engine? I need such port number to write configuration scripts to grant access to specific port of the machine installed with SQL Server to make it safe. A related question is whether SQL Server database engine will use one static port number to serve all client requests, or using one port for each request?

BTW: my background is SQL Server 2008 enterprise.

thanks in advance, George

+2 A:

1433 is what SQL Server uses by default. It has since at least SQL Server 6.0.

Generally, for security reasons, you don't want to open this up to the world. People should be accessing your DB only through an application/web service. Direct SQL Server connections over the tubes are rife with security perils.

All sessions will use this port (a la port 80 for a web site), but you can change it, using the SQL Server Configuration Tool, described here.

Eric 2009-08-15 04:33:21

Thanks, 1. do you mean all sessions will use port 1433 only to deal with all operations on SQL Server? 2. If SQL Server configuration is different, i.e. using some other port, where to see the change?

George2 2009-08-15 04:37:18

Thanks, so open this port should be enough for ADO.Net applications to access SQL Server 2008 database engine, all instances are using the same port?

George2 2009-08-15 04:45:30

@George: Yep. But please make sure you aren't opening it to the extranet!

Eric 2009-08-15 04:47:48

All instances on the same server will use the same port?

George2 2009-08-15 04:52:04

@George: Yes, yes the will. Connections will *always* hit the SQL Server port. Again, just like port 80 for Apache or IIS.

Eric 2009-08-15 04:55:44

Sorry Eric, maybe I have not made myself understood. Instances I mean different named instance for the same SQL Server, not means different sessions to the same SQL Server instance database engine. So, in my context of instance, all of them will use the same port number, i.e. 1433 by default? My confusion is from the below reply from Chris, seems different instances will use different port number?

George2 2009-08-15 05:00:08

@George: Yeah. My SQL 2008 dynamic port is set to 1660. Go to Start-->Microsoft SQL Server-->Configuration Tools-->Configuration Manager, then go to Protocols for <Instance>, double click on TCP/IP, and go to the IP addresses tab to get the definitive answer.

Eric 2009-08-15 05:06:44

Thanks Eric, I am confused about the concept of Dynamic port. Does it mean the instance will always use the port number value assigned by Dynamic port? or the instance will choose any port number available dynamically?

George2 2009-08-15 06:38:57

+1 A:

:1433 is the default. However, it is possible to change this port, and if you're dealing with multiple instances, each one will have a different port.

A quick Google search turns up the following link:

http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance/

... and I'm sure Technet will have more information.

Chris Miller 2009-08-15 04:47:46

... and what Eric said, too.

Chris Miller 2009-08-15 04:49:13

The url can not be opened?

George2 2009-08-15 04:58:16

I'm not having a problem accessing it, either by clicking or copy-and-paste; What error do you see?

Chris Miller 2009-08-15 19:54:10

+5 A:

The default instance will, by default, listen on tcp/1433. It could possibly also listen on a named pipe (tcp/445) - but I think that must be explicitly enabled these days.

Named instances, like SQLEXPRESS, listen on a dynamic port. The dynamic port is resolved by the client through SQL Server Resolution Protocol (aka SQL Browser) - which listens on udp/1434¹. This dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if there's a contention, SQL will choose a new port.

You can, and generally should, configure all instances of a production SQL server to use a static port. This makes firewalling much easier.

¹ The only reason that you have to put the named instance in, say, a connection string is so that the client knows to ask SSRP for the dynamic port. If it's a static or otherwise known port, you can simply point the client to Server=server.com:port, leaving off the instance name.

Mark Brackett 2009-08-15 05:20:06

Thanks Marc, I am confused about what do you mean -- "but if there's a contention, SQL will choose a new port."? Especially what means there is a contention. Do you mean if the dynamic port is occupied by other process other than SQL Server, a new port will be choosen?

George2 2009-08-15 06:40:37

I want to confirm with you that if we use no-default port number, and in connection string we do not explicitly specify the port number, SQL Server and client will always be able to use SSRP to resolve to find the port number to use?

George2 2009-08-15 06:42:15

Yes - if the "preferred" port (chosen on first run) is in use, SQL will choose a new one on startup. And yes, as long as you allow udp/1434 and the Sql Server Browser Service is running. http://msdn.microsoft.com/en-us/library/ms181087.aspx

Mark Brackett 2009-08-15 07:46:36

Thanks Mark, 1. my confusion is the word dynamic makes me think that each time a client connects to SQL Server, SQL Server will serve the client using a new (dynamic) port, so the port number value is hard to determine. From your reply, the dynamic port is actually fixed value (choose an unused value), correct? 2. when we configure dynamic port in SQL Server Configuration Manager, we need to specify value 0, does it mean it is still impossible for us to know the "fixed" value of dynamic port?

George2 2009-08-15 07:50:58

Correct - it's dynamic in that it's chosen at startup - each client connects to the same port. I believe you can check Config Mgr to see what the current value is when the server is running. Otherwise, it is stored in the Registry and also written to SQL logs I think.

Mark Brackett 2009-08-15 15:26:46

Sometimes the port is not 1433

From http://www.php.net/manual/en/function.mssql-connect.php#76256 Look in the registry using regedit.exe at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp.

One of the nameValue pairs has name TcpPort and a value, which is the port that the SQL Server is listening on.

keikkeik 2009-10-15 07:05:53

ansaurus

tags:

views:

answers:

port number of SQL Server

related questions