Today I downloaded and installed the Sql Server 2005 Express Advanced edition for a test project I was working on. I needed to enable remote connections, so I when to the surface area configuration wizard and enabled remote connections thru tcp, and I opened port 1433 on the computer's firewall. However, I could not connect.
Running a netstat -an showed nothing listening on port 1433. Doing some searching I found an outdated post on the sql express blog that helped me fix the problem.
- Open Sql Server Configuration Manager
- Expand Protocols for SQLEXpress under SqlServer 2005 Network Configuration and click on Tcp/Ip
- On the IP Addresses tab, scroll to find the IpAny entry.
- Clear out the field for dynamic ports and put in 1433 for the TCP Port
- Restart SQL Server.
The reason you have to do this, is by default the SqlBrowser service (port 1434) is not running by default. When clients try to connect, they must know the port to connect to. The default sql port is 1433, so once we have that setup, even if the SqlBrowser service is not running, or is blocked by the firewall, we can still connect.