To 1433 or not to 1433...that is the question
I recently ran into a problem with one of my own internal applications and it re-raised a philosophical question I have had before with customers. There are really two sides to the question:
1) Should I set my non-default instance to listen on TCP 1433?
2) Should I set my default instance to listen on something other than TCP 1433?
In both cases, I recommend "no".
Let me tackle the second question first since that is a simpler question. I know that years ago one of the common security recommendations was to put your default instance on a port other than TCP 1433 to make it more difficult for attackers to find it. However, I can say with complete comfort that if an attacker has a network trace that has a connection attempt to your instance, they can figure out the port on which your instance is listening very easily. Even if you encrypt the conversation, the first five packets of the conversation are unencrypted because you cannot encrypt anything until you have contacted the instance. Since those first five packets are the same for any connection attempt, it is very easy to detect them in a network trace. Although I don't do this maliciously (I swear!), I do this on a regular basis when someone sends me a network trace to a named instance and neglects to tell me the port on which the instance is listening.
In addition, if you change your default instance to a port other than TCP 1433, you now need to specify it in every connection string - either directly (servername,port#) or indirectly via client alias. Given how easy it is to find this conversation in a network, I really cannot see the additional effort as being worth the negligible security benefit (security by obfuscation is never a great idea).
The first question is a little bit more complex. Setting your default instance to TCP 1433 does indeed give you the benefit of not having to specify the instance name or port number in the connection string. This is because the SQL Server client libraries don't bother querying SQL Browser for the port number if they don't detect an instance name in the connection string. Instead, they go straight to TCP 1433.
The downside to this approach shows up when you are working with application administrators who don't know anything about the SQL Server instance. If they don't know that the instance is a named instance, they might configure their connection string as if the SQL Server instance was a default instance. Since the instance is listening on TCP 1433, the attempt to connect will succeed. The the real problem comes later when you decide to change the port on which your SQL Server instance is listening (maybe you read my blog:)). If you do, but don't change the client connection string, the client won't be able to connect. And, because the client thinks your instance is a default instance, it won't query SQL Browser, so will never find out the new port. The only way to fix this is to create an alias on the client (tough to maintain over time) or to modify the connection string to specify an instance name. Now, instead of just getting downtime on the SQL Server side, you have to take downtime on the client side, too.
In conclusion, given that there is a negligible security benefit to modifying the port for your default instance and there is significant potential for outages with setting your named instance to TCP 1433. Therefore, with the exception of setting a static port for your named instances, I recommend you just leave the port settings to default.
P.S. Please don't set any of your instances to TCP 1434 either. While not technically wrong, it is very confusing since SQL Browser listens on UDP 1434 and hardly anybody references the protocol (TCP vs. UDP) when talking about ports. Making sure both sides of the conversation are talking about the same service can then get quite confusing if you put SQL Server on TCP 1434.
Evan Basalik | Senior Support Escalation Engineer | Microsoft SQL Server Escalation Services