Since we’re on a recent theme of revising long-held best practices that are not, here’s a timely one for you:
Don’t change your default SQL Server port for security reasons.
In SQL Server Configuration Manager, you can set a custom port for your SQL Server instance. If you’re running named instances, you might even find this a necessary step, because maybe you don’t want the SQL Server Browser service running. Or maybe you have multiple SQL Server containers on your Red Hat Enterprise Linux host.
Those are good reasons to set custom ports. Security, however, is not one of them. If you think that having SQL Server running on the default port of 1433 is a security risk, you’re doing security wrong. Default ports are there for a reason and changing this for security reasons means you’re thinking about security the wrong way. You are also adding complexity to your environment, and you might not be documenting things correctly.
A fundamental misconception about SQL Server is that it is insecure. It isn’t if you follow best practices:
- Don’t expose your SQL Server instance to the Internet. That’s probably rule number one for any production environment. If you’re exposing anything on the Internet, changing the default port is going to slow down an attacker less than a millisecond, because chances are they’ve already used a port scanner to figure out you’re running SQL Server.
- Make sure you are fully patched. Cumulative Updates are released on a regular cadence, and given your testing process, you should at least be only one or two CUs behind.
- In your internal network, you should also ensure the only traffic that is allowed to connect to your production database1Remember, if you’re restoring production data to a developer instance, it’s still production data. is your API. Then, protect your API host and don’t leak database connection information.
Security can be scary, I get that. But I also know firsthand that if you have a patched SQL Server instance and a strong password, the only thing that’s going to happen is your error log will grow as SQL Server denies and logs all the invalid connection attempts. There is a chance your server may run out of disk space if you’re not monitoring that, but you are monitoring disk usage on your production server, right?
It’s easy to get caught up in behaviour that is based on a belief system as opposed to measured results. MAXDOP 1 was a great example of this with SharePoint for the longest time. Recently Erik Darling sounded the alarm on enabling optimize for ad hoc workloads by default.
Don’t change the default port on SQL Server as a “best practice.” Don’t do it for “security reasons.” There are great reasons why you might want to — which I outlined at the start — but don’t do it because you think it’ll keep people out of your database.
Most data leaks are caused by internal vectors. Practise the zero trust model and use defence in depth. Take care of things like backup file encryption, securing your offsite backups, and keeping port 1433 away from the Internet.
Share your security best practices in the comments below.
Photo by Kaffeebart on Unsplash.
Can you link to more info on the SharePoint MAXDOP 1 thing you’re talking about?
https://docs.microsoft.com/sharepoint/administration/best-practices-for-sql-server-in-a-sharepoint-server-farm#configure-specific-sql-server-settings-before-you-deploy-sharepoint-server
I’m pretty sure our sharepoint team configured the SQL servers with MAXDOP=1 because that’s what Microsoft’s recommendations are. It’s right in the Microsoft documentation, as pointed out by randolph, and while I was researching SQL server configurations I never ran into anything that suggested otherwise. I just did a specific Google search for this, I’m not finding anything that indicates that this is incorrect. Can someone link me something that I can show the team that explains why this isn’t a good setting?
SharePoint config recommends MAXDOP 1 by default, yes. The point is, a lot of “best practices” going around aren’t best practices because they don’t apply everywhere.
We have a commercial SQL instance exposed to the internet (the horror!). Folks connect from an EXE, and we use very strong credentials. By switching the port from 1433 to something else, the number of hits went down 95%. It just gives us less to look at, and gives our brute force log checker/firewall denier less to do.
Thank you for sharing your anecdote.
Perhaps I might learn something here. If you don’t expose the SQL server, and pass everything through a webservice first – What have you gained? You still have an open port, and you have a different Login and Password for folks to crack. You have moved the problem from your SQL Server to your Web Server. Please tell me what is so much better about that?
Sure thing! Another layer of defence is part of defence in depth. Gaining access to the web application does not give you access to the database. It’s not just a different port.
If I hack your API, then I can do anything your API allows me to, which wont be very much if the API is well written. It’s unlikely that your API will have an interface to drop all tables and delete all SQL backups, so I probably won’t be able to do that. If I hack your SQL server, the number of things I can now do is almost limitless, and I can do far more damage in much less time.
Ah hah! Now I understand. I had thought that you meant it was a “bad” best practice. I guess that since it’s in the sharepoint-related SQL documentation, that I would never have confused it as a “general” best practice. Thank you!
I can see where the confusion came in!
I disagree.
Hundreds companies around the world were victims of ransomware attack even they had implemented best security practics ( all described in the article )
Its time to say the thruth that SQL server is not secured at all and is full of security bugs which can be easily exploited.
Its why attackers could access DB files ( even it was locked by SQL service ) and encrypt entire environment.
Restoring in such situation is not solution as due to bottleneck it could take weeks to restore everything.
And yes i was a victim of this attack and i know exactly what is it and how to deal.
The only work around i found ( not mentioned in the article ) is hiding DB behind mountpoints. By this attackers scripts cannot access related MFT and will not encrypt DB files.
The worst thing is that all security gaps which were exploited are still present in SQL and Microsoft is aware of it.
Comments are closed.