SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Listening on a Non Standard Port

I had a note to myself to write up some notes about listening on a non standard port, but it turns out my friend Greg Larsen has already done a nice job of it in an article he wrote last year called Using Non-Standard Port for SQL Server. The reason for the note was an earlier post about security by obscurity that in turn led to a blog post by another friend - Brian Kelley - that recommends using a non standard port as a reasonable and successful deterrent to the bad guys. Not recommending it as your only security plan, but as a nice addition.

Greg's article (for SQL 2005) shows it being done via the GUI, but you can also alter it directly via the registry. The values you want are stored at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[Instance Name Here]\MSSQLServer\SuperSocketNetLib\Tcp, where each IP has a sub folder containing a port assignment. Comes in handy if you decide to modify all your servers.

Once you set it you're probably not going to change it, but for max flexibility keep in embedded in a configurable connection string (this for applications) so that you can easily change it if needed. Which leads to probably the most common question about non standard ports - how to use them? Easily done, instead of just specifying the server name, specify servername plus a comma plus the port name (SERVER1, port# instead of SERVER1). This works across the board; Query Analyzer, Management Studio, and ADO/ADO.Net.

If you really want to play by the rules you should you a port that is not well known, and you figure that out by looking at the list of well known port assignments (they recommend 49152 through 65535). In practice I don't think it matters because we're seldom going to have all that many other services running on our servers.



I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


Posted by bkelley on 8 July 2008

And if you really want to give it a go, use dice to generate the port #. When we had a service go in, to prevent it being guessable based on knowing the implementers, we grabbed a bunch of 10-sided dice and rolled.

This prevents things like ports coming up on 1701 because a particular member of the team is a Star Trek fan (Enterprise is NCC-1701).

Leave a Comment

Please register or log in to leave a comment.