Last week at SQL Live I gave a talk “Configuring SQL Server like a Microsoft Certified Master”. It’s a session Tim Chapman (blog | @chapmandew) and I built previously and I’ve made an effort to keep updated. I remember we wanted to call it “Configuring SQL Server Like a Boss”, but thought it boring to watch someone click ‘next’ a few dozen times and then ‘finish’.
On one slide I mention the use of named instances, non-default ports, firewalls, and anti-virus exclusions. What I didn’t mention during the session was how one of those items has the potential to create the largest SQL Server nerd fight since server core was introduced. Here is the slide I was using during the point in the session:
My advice to the attendees was to use each of the items listed. I am not saying that this is an absolute rule, mind you, but in the absence of information about systems, servers, applications, etc., I would configure SQL Server to be a named instance, using a non-default port, using a firewall, and making sure that the data and log files were excluded from anti-virus scans. That’s how I roll. You are free to do you as you see fit, and I’ll be me.
The contentious issue here is using non-default ports for SQL Server. The argument against non-default ports typically goes something like this:
“It’s security by obscurity.”
“It’s difficult to remember all those different port numbers.”
“An attacker can do a port scan in less than three seconds.”
All those items are true. I am not here today, writing this post, to dispute their veracity. I’m not interested in watching a nerd fight (OK, maybe just a little one).
I am here today to help you understand the bigger picture.
First, security by obscurity is not always a bad thing. Do a little research and you can find plenty of examples where security by obscurity has a valid use case. No, it’s not for everyone, in every situation. Don’t be silly. But, much as there is always someone to point out some edge case server they work with that has a unique workload such that WidgetX won’t work for them, I am here to remind you that edge cases exist with regards to security, too.
Second, you should know that there are tools out there to help you keep track of your inventory, and things like port numbers. You should also know that other DBAs in the world manage systems (Oracle, DB2, and the artist formerly known as Sybase) that assign non-default ports all the damn time, so it’s not impossible for a human to keep track of such things. Am I the only one that knows how to use a DNS alias?
Lastly, yes, an attacker can do a port scan in just a few seconds. But if a non-default port causes an attacker to take longer, allowing me to detect the activity, that is a good thing in my book. And it’s not just an attacker from the outside we need to worry about. Sometimes you want folks on the accounting team to have to slow down and ask questions about why they can’t connect to a server, it forces everyone to double check that their request for data is a legitimate one.
All that aside, there is one final point I want to present for consideration.
Let’s assume that you continue to install default instances of SQL Server, using the default port of 1433, and you do not use the windows firewall. Now, let’s assume that an attacker gains entry to your servers and database instances and you have a data breach. Now, let’s assume that there is a formal review about the incident.
You are asked to come to a room. You sit across from a group of people. They ask you questions about your role. They ask questions about the server, the database, and security.
You explain to them how SQL Server works, the difference between default and named instances, and why security by obscurity is a bad, bad thing. “Completely useless” you tell them.
Then, as you are getting ready to leave, someone does their best Columbo impersonation and asks you just one more thing:
“Is it possible to run SQL Server on a non-default port?”
Think about what answer you would want to give at that moment.
Think again about your answer, and your reasons, your justifications for using the default port.
Now think about your answer as if your job, or jail time, depends on it.
I know what my answers would be. How about yours?
So you’re saying that if it takes an attacker 3 seconds to find the correct port, you’ve identified and stopped them by then? I would literally pay money to see how you manage to do that.
No, I didn’t say “stop”. While I am certain that may be possible, I don’t know of any tool out there to do that right now. I was focusing on using those 3 seconds to detect anomalous activity and try to capture details to use for the forensic team afterwards. You are free to feel this is a waste of time, but I know how I would want to answer the questions in that review.
I’m going to shock you Tom. I’m not going to approach this from the security perspective, though I will arrive at the same answer.
Let’s talk about loosely coupling component pieces of our architecture. If the SQL Server is user-facing, I want it on a default instance on the default port using a friendly alias via DNS. That way, when I have to switch the server out (such as for a SQL Server upgrade), I change the friendly alias and break nothing. Named instance or different port for an end user invites trouble and makes things less loosely coupled.
If you’re talking about a SQL Server that’s only hit by servers, then do what you want. But as you’ve pointed out, it’s trivial for an attacker to find the SQL port. Why not invest the energy in layer 7 firewalls and segmenting off the network to prevent the access anyway? Also, I’d want to loosely couple here again, too, because we upgrade systems all the time. So if I stay consistent, default instance on the default port using a friendly alias via DNS, I make operations easier, faster, and improve my ability to automate. So why would I complicate things with named instances and anything other than tcp/1433?
As for firewalls, yes, please. Why does an end user need to be able to RPC to a SQL Server? He or she doesn’t. So why permit the connection?
Thanks KBK, all valid points. And I didn’t write the post to say they are wrong. I know that changing ports does not guarantee any level of security. And I bet the folks on the other side of the table know that, too.
Let’s say you are the DBA for a federal agency. And that a breach is discovered. And you have to go before Congress to answer some questions. And they ask you that one, simple question. It’s not “hey would it work”, it’s just “hey, is it possible”.
Changing ports is but one layer of security. By itself it does nothing. But if you don’t do it, your answer to that question could have significant impact to your next paycheck.
Every decision we make comes with cost, benefit, and risk. You have listed some of the costs. And others say there is no benefit. But changing ports allows for you to make a minimum effort in helping to mitigate risk. So when you get asked the question, you have the right answer.
I’m with Brian here 😉
. Using DNS to abstract the physical server name is super useful.
I’ve created 10+ instance clusters with all of the instances on default ports.
Using using a dns Cname to ensure connections aren’t made to physical server name saves sooo much risk and effort on consolidations and migrations..
Agreed, default ports makes consolidations and migrations easier in a lot of scenarios.
After a few additional days of reflection on this topic I have noticed that much of the discussion is around the technical aspects using non-default ports. I think people are looking past the human element here.
If you choose to run on a default port, and the forensics of a security breach reveal that the bad actor used code that relied on default ports…in that edge case…how do you want to answer the inquiry? The persons on the other side of the table are not interested in all the technical aspects of the discussion, they just want to know if you did everything you could have done, or not.
We can go back and forth forever on the technical stuff, and workarounds for everything. But at the end of the day none of that will matter. Either you chose to use a default port, or not.
Exactly how hard does anyone imagine it is to detect and block port scanning? It’s not rocket science. Find requests for different ports from similar IP addresses within a short period of time, and intentionally delay them. I remember building a tool to do that back in the 80’s. So the premise that an obscure port can always be found just as easily, simply isn’t true. How hard would it be to build a honey pot that responded as SQL Server on a bunch of ports and detected such connection attempts? The dev side of me says that doesn’t sound too hard. In fact, letting them connect to a honey pot and finding out what they’re trying to do might be much more valuable.
I was thinking about that as well, find a way to let the intruder stick around a bit longer in an effort to track them, like the old police shows doing a phone trace. But then I think about how the data is the most valuable asset that any company has so maybe I don’t want them poking around at all.
I find the discussion about default ports to be a valuable one, and I know which way I would want to answer the question at the end of the day.
The other aspect to consider is when you last saw a TDS exploit. I keep hearing how having SQL Server behind IIS, etc, etc. is much safer than exposed on its own. I’m entirely unconvinced of that. Almost every time I hear of a SQL Server intrusion, it’s been the application or IIS that’s been the culprit, not direct access to the DB via TDS. They always say that multiple layers of defence is stronger but I’d respectfully suggest that you don’t make a safe stronger by putting it in a glass house with the key for the safe taped on the outside. That’s what most web apps are closer to.
Being quite late to the discussion, I think I learn towards Tom’s advice. Not because an attacker can’t find the port, but because random scripts that people download, or a virus, expect to find TCP 1433 or UDP 1434. These are the vandals, the script kiddies, the ones that just try something to see and give up if it’s not there. They don’t port scan. They’re not determined attackers. They’re wasting time and looking for easy targets.
There certainly is an administrative aspect to this as well. Despite all the arguments ojune could make that technically this doesn’t increase security, I think I disagree. Doing something is better than nothing. Mr. Kelley makes the best argument that this can cause me to rethink this. Simplicity and not causing issues with Operations. I might buy this as an argument, but a determined auditor or manager might really question whether adding ,5222 to a connection string is that hard, or that a build can’t pre-set a port for a company, or Get-DbaTcpPort. However, if I need to ensure every operations person knows our port, then am I providing additional security? It’s debatable, but it’s not a simple answer.
Ultimately, I do think that security by loosely coupling things, not being too standardized, and using layers does help. Changing the default port helps. Maybe not much, but it does help.
Thanks Steve. That’s the point made in the security posts I linked to above, that adding the layer isn’t a bad effort. Relying solely on a different port is useless for protecting the data, but could mean everything when doing the forensics later. I certainly wouldn’t want to be reviewing a breach, going through the attack vector, and seeing that a simple port change could have thwarted someone out phishing.
I throw a vote in, named instances(nope), non-default ports(nope), firewall(yup), virus scan(yup).
Micro-segmentation and principal of least privilege are a couple things that are very challenging to implement. I think the line has to be drawn somewhere so we can spend time on useful security methods and tools and not waste our time on ones that can be easily bypassed.
Thanks Tom, now the auditors have an “imright.com” blog post from a respectable blogger. That respectable part needs some debate too =]
You’re welcome…I think?
Hi Tom,
I do agree with your approach. I give the same recommendations to my customers.
I would even go one step further and recommend to disable the SQL Browser Service and Hide the instance to be even more secure. Of course, this depends on what the customer wants but some of my clients do this by default.
Regards
Pieter
Thanks Pieter, I forgot to mention the use of the browser service in the post. Disabling it makes sense if you have taken all the other steps.
Tom,
Thanks for your post.
Has anything changed from 2016 to 2022 in the context of this discussion? That is, I see changing ports as a starting point (phishing) but certainly not an end point (port scan) as the success depends on attack sophistication.
The reality of ransomware today makes it that much more important to do more than changing ports but its still good advice. The thief can break through the window but the police are still likely to ask whether taping the key to the door wasnt a bit ….. unthinking
A number of years back I had proposed the techniques described in the below article, all of which were approved by the Architecture and Security teams, and later implemented in my workplace. As commenters mentioned in this thread this is not an attacker-proof solution, however will help mitigate potential threats and as ThomasLaRock stated, give you more fuel when an audit (or post-incident review) is carried out.
There is no silver bullet to security and the best we can do is apply multiple protection layers. This is just one of them. Yes, there is the overhead of keeping track of the “application to DNS alias to port” mappings, however any decent CMDB would do the trick. In a small shop you could simply use an Excel spreadsheet (even though Excel is not a database…). Point is, the administrative/management processes should not be a barrier to security.
SQL Server Connection Strings, Unique Application DNS and Listening Ports
https://sqlserverdiaries.com/blog/index.php/2011/04/sql-server-connection-strings-unique-application-dns-and-listening-ports/
The same technique applies to all SQL Server versions and Editions, and is still valid today with the most recent release.