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

Comments (6)

  1. BobbyTables says:

    I have to admit that we're doing the latter in a multiple instance failover cluster environment: the default instance plus three named instances, each listening on their own IP address on port 1433.

    Additionally we configure a DNS alias (CNAME) specifically for each application, so in the event that we move an application database to a different instance on the cluster, to a different server entirely (similarly configured,) or perform a fail-over to a disaster recovery server at another site, all that is needed is to change the target of the CNAME and flush the DNS cache on the clients.

    Unless the application is using a poorly-behaving data access driver (we have run into a couple,) they never know they're on a named instance.

  2. GrumpyOldDBA says:

    I've always argued against using non default ports for the exact reason you quote – it's easy to scan to find out which port is being used.

    always nice to see subjects like this revisted by the microsoft teams.

  3. Saravanan says:

    I have to admit that we are doing multi-instance cluster and all instances pointing to 1433 but to different IP, and we have CName assigned to all those IP's. But there is no default instance on that cluster.

  4. Evan says:

    The scenario that BobbyTables and Saravanan describes works just fine…until you change it to a port other than 1433 and the client don't know to check with SQL Browser.  The CNAME alias makes sense, but I still contend you would be better of letting SQL Browser do its job rather than using 1433.

  5. Ewan C says:

    Hi Evan

    We run the same scenario as Bobby and Saravanan. The problem with using non-1433 port is that you then can't use CNAME to redirect your clients if your server changes because DNS doesn't do instance names. And really, which are you more likely to change, server name or port number?

    I started doing this after reading an article by Linchi Shea…/bad-database-practices-allowing-apps-to-connect-to-the-hostname.aspx


  6. TJ says:

    We had an issue a few years back with a content firewall that sat between our WAN and the concerned SQL Server’s hosting data center.  After the SQL Slammer worm fiasco, a ruleset was distributed by the firewall manufacturer that would suspend particular source MAC/IP addresses, hence the client app, if the traffic was considered suspect.  This determination apparently was made based on the volume of either the payload or packet frequency on port 1433.  There was no deep packet inspection for TDS, either because the protocol unpublished at the time or it was considered too burdensome to develop suitable heuristic rules for deep packet inspection.  This caused us quite a bit of headaches trying to determine what was going on because the problem manifested without clear reason and absent easily discernable pattern.  We ended up switching to another port and intend to continue with our new “standard” SQL/TDS port even on new deployments.  It really is not an issue specifying the TCP port because we have to specify the DNS name or IP address anyways.  Moreover, it provides us a bit of obfuscation as to the presence of a SQL Server mitigating some attack potential.  Though using trusted authentication the mitigation can be of questionable value.

Skip to main content