Should we run SQL Server on the default port 1433

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.

SQL Server Port 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.

Port Scanning

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:

SQLWATCH image e1643937688996

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:

SQLWATCH image 1 e1643937673402

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.

SQLWATCH image 2

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.

SQLWATCH image

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

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.

SQLWATCH image 1 e1643937713737

Let’s disable it and re-run the test.

SQLWATCH image 3 e1643937744917

With the SQL Browser disabled, we are not finding anything!

SQLWATCH image 4 e1643937459425

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:

SQLWATCH image 5

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?

SQLWATCH image 6 e1643990891106

Lessons learned

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 64120 and not derivation of the standard port 1433*
    • 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.

Thanks for reading!

Agree or disagree? Have I missed something, or do you want to add anything? Share your views in the comments below:

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments

Want to learn about Microsoft Data Platform?

Check out the community events page for more events.

Next Upcoming Event
16 May 2022
  • 00

    days

  • 00

    hours

  • 00

    minutes

  • 00

    seconds

Enter your email address to subscribe to this blog and receive notifications of new posts by email. No spam, promise, and you can unsubscribe anytime. Alternatively, you can subscribe via RSS

Windocks
1
0
Would love your thoughts, please comment.x
()
x