There are two types of DBAs. Those that run SQL Server on the default port 1433 and those that don’t. Those that do not run on the default port
1433 say it’s more secure, and those who do say it’s “security by obscurity” do not make it any more secure.
Which DBA are you? Let’s find out who is right.
What are ports
Network ports are a bit like hotel room doors. Each room has its doors and own number, and if you are going to visit Mr S. Quel, you have open the doors to enter the room to talk to him. You must know which room Mr S. Quel is staying in. Otherwise, you have to knock at every room and see if Mr S. Quel answers. In our hotel, Mr S. Quel lives behind doors number 1433.
But what if someone who Mr S. Quel does not like wants to visit? As long as the visitor knows the door number, they come in and bother Mr S. Quel.
Security by obscurity
Mr S. Quel has had enough unwanted visitors and decided to move to another hotel. He is now staying behind doors number
14331. Visitors who knock on his old doors
1433 get nothing. However, one annoying visitor decided to find Mr S. Quel by knocking on every door. Unfortunately, Mr S. Quel is very polite and answers every time someone knocks. The annoying visitor who found Mr S. Quel now knows that he answers at the door
14331 instead of
1433. It only took him a few seconds to find him. However, Mr S. Quel’s family and friends do not now know where he lives, and they are not as fast in finding him. This means they can never visit him again.
In this example, the very annoying visitor is the “hacker” trying to find your SQL Server. The disappointed family are your end-users trying to connect to SQL Server via SSMS but don’t know the non-standard port.
Knocking on every single door and waiting for who answers is called port-scanning. This is a known technique to see what sits behind each port. There are 65535 ports on each computer, and it could take a couple of minutes to check every single port.
The most common technique is to scan a range of IP addresses (range of computers) for given open ports or scan a single computer for open ports. This way, a hacker can find any computer running SQL Server regardless of what port the SQL Server is running on.
An example of a good port scanning tool is Nmap. It takes 33 seconds to scan my home network to find hosts with port
1433 open, i.e. those with SQL Server running on the default port:
But what happens if we change SQL Server to run on
14331 port? Since we are now running a custom port, the attacker must scan an entire range of ports to find which one responds with SQL Server. Scanning a known port will not give any results:
Ok, so let’s scan the entire network for any open port and find those with SQL Server. This means scanning all 65535 ports for every computer. Well, we can probably skip the first 1000 ports as these are the most common that would unlikely host SQL Server.
So we have found our new port
14331 but I didn’t know what I was looking for. I would not know what was behind it. We just know it’s open, and there is something listening behind it. We have also found all sorts of all other ports that could potentially be running SQL Server. We don’t know what. The most important difference is that this scan took 18240 seconds. That’s 5 hours. The
-T argument tells Nmap how aggressive the scan should be. The
-T4 is as fast as I could make it. Anything faster would not return any results as it would start skipping services that do not respond fast enough (we’re talking micro and milliseconds).
Keep in mind that my network is tiny C class range
192.*. If you are running A
10.* or B
172.* class network that 5 hours will turn into days.
If you have someone hanging around your network undetected for 5 hours you have a bigger problem than what port SQL Server runs on.
Most firewalls can detect port scanning attempts and would probably block my attempts or/and raise an alert with the security team.
SQL Server discovery with Nmap
The problem with finding SQL Server running on the arbitrary port is that we don’t really know what service we are talking to. Port scanning looks a bit like this:
- Scanner: Hey, are you there?
- Service: Yep, I’m here.
- Scanner: Thanks, mate. Who are you?
- Service: I don’t understand you.
Nmap can help us with this task, too with the help of the ms-sql-info script.
The above had no problem finding my “hidden” SQL Server in my network. It had scanned the entire network, and it only took 1266 seconds (~21 minutes) to find my SQL Server running on a non-default port.
SQL Browser service is a bit like a reception in a hotel. It is a bit of a dork telling everyone where Mr S. Quel lives. It will tell you which port each instance is listening on, and it will also tell all the available instances. This obviously creates a security risk. If you have SQL Browser running, why do you bother hiding your SQL Server in the first place? I have had SQL Browser running for the above tests, which explains why the Nmap
ms-sql-info script was able to find it so quickly.
Let’s disable it and re-run the test.
With the SQL Browser disabled, we are not finding anything!
Nmap Version Detection
Forget about the
ms-sql-* scripts. We don’t need to go that far for simple detection. If we run a standard network scan with some extra Nmap parameters
-sV to make it force-detect the software version, it will quickly find our “hidden” SQL Servers even when the SQL browser is disabled. We don’t even need to use the
ms-sql-* scripts. We can even make it run faster with some extra parameters:
It took 3500 seconds (~1 hour) to find my “hidden” SQL Server with SQL Browser off.
I was scanning around ~64k ports between 1000 and 65355. How many SQL Servers have you ever seen running on non-standard ports in the 40, 50 or 60k range? 9 out of 10 times the non-standard port will be
1433 with an extra number
1433* like in my example.
How long would it take to scan my network for a smaller port range? What? 90 seconds?
There are some important lessons in this post:
- If you run SQL Server Browser it does not matter what port you are running SQL Server on. The Browser will tell everyone which port SQL Server is really running on.
- It may be advantageous to run SQL Server on non-default port providing you:
- Don’t run SQL Browser
- Run something completely random, like
64120and not derivation of the standard port
- Run a decent firewall with intrusion prevention that can detect and prevent port scanning.
- The disadvantage of a non-standard port is that your users must know about it and have it typed every time they want to connect to the SQL Server.
- Best to scan your own network and see how easy it is to find your “hidden” SQL Servers.
- Never run SQL Server open to the public (I can’t believe I have to say this but a lot of people do this).
- It is not more secure to run non-standard port but it may make it a little bit more difficult for an attacker to find the non-standard port.