SQL server has conventionally used port 1433. However recently Microsoft made some changes and when suing named instanced you are likely to find your SQL Server is using dynamic ports.
This means that SQL server chooses the port to use, which can cause issues if trying to open up the correct port number in a firewall for example. Note that although it is called “dynamic”, once allocated (after SQL restart) it is likely to remain fixed – as on any subsequent restart SQL Server will try and use the same port and will only change port number if that port is no longer available.
If running over a LAN a client to the SQL Server can work out which port to connect on so long as sqlbrowser.exe is allowed in the firewall on the SQL Server machine. This publishes the existence of the SQL Server instance and also which port to connect on. This TechNet article explains that to connect, the client will send a UDP packet to port 1434 first to resolve the dynamic port. Then it will connect as normal.
There is a good description of what needs opening on a SQL Server server firewall (https://cs.thomsonreuters.com/ua/toolbox/cs_us_en/kb/k73037522.htm). Basically open port 1433 and allow exceptions for sqlservr.exe and sqlbrowser.exe.
Having done this you should be able to connect from any client so long as outbound ports are not blocked (unusual on a LAN).
However if you are connecting from the internet you may need to open the specific port that sql server is running on. This is a little trickier, as unless sqlbrowser.exe is allowed to communicate through your external firewall, the remote client will not know what port to attempt communication on.
On SQL Server
Start SQL server configuration manager and open the correct instance name and 32/64 bit option (double check you are using the right section!)
Make sure TCP/IP is enabled and then look at the properties.
You will see various sections for each LAN card connected (and various virtual ones). Normally you would expect all these to be set as Enabled=No and Dynamic port = 0
The setting we are really interested in is at the bottom in the IPAll section where it gives us the dynamic port number (52056 in this example). If you have allowed sqlserver.exe and sqlbrowser.exe through the servers firewall there should be no configuration needed on the server itself – but any external firewall will need traffic on this port allowing and direct traffic to the SQL Server's IP address.
Once this is allowed – and the external machine can connect by specifying the port on the connection with a comma e.g.
mysqlserver.mydomain.com,52056\SQLEXPRESS
This will work in enterprise manager or in your connection string to SQL server
From the client you can run from the command line:
This will list all ports currently in use - so if you can connect to the SQL Server server from another machine on the LAN (or witha VPN up) this can also help identify port in use.
Note: Please add / edit above if incorrect / incomplete as its based on my best understanding to date !
Useful links
http://dba.stackexchange.com/questions/47651/when-is-a-dynamic-port-dynamic
http://support.microsoft.com/kb/823938
http://blogs.msdn.com/b/bgroth/archive/2004/11/11/256190.aspx