Why avoiding the “Change port 1433 Best Practice” might be the “Better Practice” to follow
A lot of quality resources in the DBA community (e.g. MS, Jonathan Kehayias, Green SQL, etc.) state that changing a default instance’s port to something other than 1433 is a security “Best Practice.” While I may agree that you want to avoid using port 1433, I contend there are better ways to go about it.
Dirty Little Secrets
Changing the port on a default instance is more than just a few keystrokes and a service restart. Most guides don’t point out that if you change the default instance’s port to something other than 1433, you are also required to reconfigure all of your client connections. No, you can’t just turn on the SQL Server Browser service and consider your work done as it doesn’t serve requests to the default instance, as per KB 823938:
The Browser service or SSRP is never used for a default instance of SQL Server.
Additionally, the default instance cannot run on dynamic ports like a named instance, meaning that if a port is in use when the service comes online, it will fail to search for a different, unused port and throw the TDSSNIClient initialization failed with error 0x2740, status code 0xa error:
To address these shortcomings, you will need to do one of the following tasks for each client connection to the instance: specify the port in the client’s connection string, setup a client-side alias, or change the global default port that is defined for that client computer. This MS Blog steps through each of these workarounds in detail.
The Alternative
Instead of going through the headaches of reconfiguring the port on the default instance, choose instead, to only install named instances of SQL Server. A named instance will never run on 1433 (unless you explicitly set it to). It can utilize dynamic ports and since the SQL Server Browser service forwards requests to the instance, changing ports doesn’t require additional reconfigurations for any inbound connections. I recommend that the only time you ever install a default instance is when an Independent Software Vendor (ISV) requires you to do so. Unless a default instance is explicitly needed, opting for a named instance will accomplish what this best-practice intends without actually doing any work outside of the default configuration.
You’re not done yet
During a recent Oracle event, one of the speakers, Luke Roguet (sp?) said the best line I’ve ever heard in relation to security: “The only secure server is one that is disconnected from the network and powered off.” As Luke quipped, if your server is on, you’re going to be exposed to threats, internal or otherwise. Avoiding port 1433 is a good start, but it is by no means the final step in keeping your instance secure.
Patching should be a high priority and will always be an ongoing practice that we, as DBAs must stay on top of. The SQL Slammer virus is a prime example of what can happen when you don’t have a rigorous patch cycle. The security vulnerability that the SQL Slammer virus exploited actually had a patch issued for it a full six months prior to the virus’s release. The fact that the virus became as widespread as it did is a sad statistic about how many DBAs were behind on their patch cycles.
In fact, if we look at the number of Zero-Day (an intrusion for which the source is unknown and therefore no patch is available) threats in 2010, a Symantec Report from April 2011 reported only 14 , out of more than 286 million, identified threats. That’s such a small percentage, the number of zeros after the decimal point is dizzying (4.89 x 10-8). What you should be taking from this is that patching should help protect you from an overwhelming percentage of issues out there.
Care must also be taken when configuring direct access to the instance. Ensuring accounts are only given what is minimally required to accomplish their work is a huge step toward limiting your threat exposure. No firewall, port reconfiguration, or patch will prevent a privileged user with ill-intent from reeking havoc on your database environment.
Proactive Avoidance is the better “Best Practice”
So, to sum it up, the intention of the “Change port 1433” best-practice is to limit your exposure to threats. As I hope to have pointed out, doing this the traditional way involves some additional headaches when it can just as easily be accomplished by avoiding default instance installations. Doing this though, by no means makes your instance secure. Proper patching and security configurations are also needed to help minimize the overall threat exposure of your environment.
-John
John Eisbrener, DBA 
As you mentioned, changing your default port really doesn’t buy you much. It might save you from a 0-day attack, but more than likely it’ll just cause you administrative headaches.
Don’t succumb to “Security through obscurity”. IMO, creating named instances just creates more obscurity but only minimally protects you. It adds to the complexity but not the overall security. Don’t get me wrong, there are plenty of good reasons to use named instances. You already mentioned dynamic ports and service forwards. I just don’t think security should be one of them.
I’m assuming that good DBA (you) would rebut to an ignorant outsider (me) that any additional security to protect your company’s IP is worth it. You’re certainly right about that. I just want to emphasize your later point that readers shouldn’t feel safe with named instances just by using them.
There needs to be proactive security considerations.
That’s why I love your last sentence — “Proper patching and security configurations are also needed to help minimize the overall threat exposure of your environment.”
Great read! I’m glad to see that you’re blogging. I look forward to reading more.