Connection to SQL Server requires TCP communication on port 50235?

Question:

We have an SQL Server 2008 R2 with a database instance running on port 1433. 1433 is open for TCP and 1434 is open for UDP.

When connecting through Microsoft SQL Management Studio, our firewall detects TCP traffic at port 50235. Why? The SQL Server does not mention this port as far as I can tell.

Ans1 :

Are you connecting to a named instance? In which case, the initial connection is to the SQL Server Browser service (UDP 1434) and then that dynamically allocates a port for named instances. This port is allocated on instance startup.

See this TechNet Resource on how the SQL Server Browser Service allocates a port for named instances.

There is a way to explicitly define a static port that a named instance will be listening on, but it appears that you are seeing a dynamically allocated port.

Ans2:

You sound like you may already know this but you can try doing a telnet to the sql server machine on port 1433:

telnet <hostname> 1433

If you get a blank screen (open connection) then your service is running on that port and there's no firewall in the way. If that's not working try:

telnet <hostname> 50235

If that works, then as the previous answer suggests you're configured for dynamic ports instead of 1433 (dynamic is the default for Express edition). You can change that in:

SQL Server Configuration Manager > SQL Server Network Configuration > TCP/IP (Right Click > Properties > IP Addresses.

In the IPAll section set TCP Dynamic Ports to blank (not 0) and enter 1433 for the TCP port)