One of the more important duties of a DBA is to make sure that their databases and the data is secure. In this post we’ll be looking at two utilities to increase the security of your server, the Windows Firewall and an antivirus software. Like with about everything else related to servers, you can’t just switch these on (well, you could, but…) and forget about them to get the best possible experience. They need to be properly configured for servers running Microsoft SQL Server. If you’re a DBA you might not be doing the configuration yourself, but you still need to tell your Windows administrators what they need to do.

Windows Firewall

Windows Firewall has to be one of the most neglected and underutilized features of the operating system I’ve ever come across. It’s very common to see this one being turned off by default and not being properly configured. While not a perfect solution, it does offer an added layer of security to your environments and this is something you should take an advantage of. Contrary to common belief it’s also not difficult to configure and you can do so easily by using Active Directory Group Policies.

Now that you know how it’s done, it’s just a matter of figuring out what sort of traffic you need to allow through the firewall, right? Depending on your environment you probably have one (or all) of these services running: Database Engine, Reporting Services and Analysis Services. The default ports and protocols used by SQL Server services are:

Port

Protocol

Description

80

TCP

Default TCP Port used by Reporting Services

1433

TCP

Default TCP Port used by Database Engine

1434

UDP

Default UDP Port used by SQL Server Browser Service

1434

TCP

Default TCP Port used by Dedicated Admin Connection (DAC)

2382

TCP

When running Analysis Services named instance or running AS in SharePoint mode.

If you’re running multiple instances of SQL Server and using different TCP ports for each, you also need to add them to the list above. When using Windows Firewall it’s also highly recommended not to use Dynamic Port allocations but to define static port for each instance. My personal recommendation is to always use Windows Firewall because having that extra bit of security is a good thing, always.

Antivirus Settings

Unlike the Windows Firewall antivirus software is something that is found on nearly every server today. And that is a good thing since the number of viruses and other malware has blown completely out of control, making any machine without adequate protection in your network a risk. However when you do install antivirus software to your servers, there are some configurations you want to do, to make sure that it doesn’t impact the performance of SQL Server.

First of all: It’s important that you check the documentation of your antivirus software, as they almost always have recommendations on how to set up exceptions for software like SQL Server. For generic advice, I’d recommend setting at least the following exceptions:

Target System

Exclude

Description

Windows Failover Cluster

Witness Drive

The witness disk is used for achieving quorum in your cluster.

Windows Failover Cluster

C:\Windows\Cluster

The directory for your clustering binaries.

SQL Server Data files

MDF, NDF, LDF

The common file extensions used by SQL Server.

SQL Server Backup files

BAK, TRN

The common file extensions used by SQL server

SQL Server Trace files

TRC

The common file extensions used by SQL server

SQL Server Backup files

BAK, TRN

The common file extensions used by SQL server

SQL Server Log files

MSSQL\LOG directory

The directory for SQL Server logfiles.

SQL Server Executable

SQLServr.exe

SQL Server executable.

SQL Server Agent Executable

SQLAgent.exe

SQL Server executable.

SQL Server Reporting Services Executable

ReportingServicesService.exe

SQL Server executable.

SQL Server Analysis Service Executable

MSMDSrv.exe

SQL Server executable.

There are also recommendations to exclude certain directories under C:\Program Files\Microsoft SQL Server\ directory structure, such as the MSSQLxx.InstanceID. There are few things you need to consider carefully before doing this however. While it’s possible that files required by SQL Server could be locked due to the scanning, excluding the directories would mean that you won’t be able to detect infected files at all. Personally I consider virus infection to be the worse of the two options for most environments.

And that’s it for the first post of 2016! I already have next two posts lined up, expect to see how to use Availability Groups to rollback database changes and how to use Excel for building T-SQL scripts. Yes, you read that right, Excel is a versatile tool 🙂

Share this:

Like this:

LikeLoading...

Related

Author: Mika Sutinen

Hi,
My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems.
I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.
View all posts by Mika Sutinen

Post navigation

About SQLStarters

Hello reader and welcome to SQLStarters!

Like so many other DBAs around the world, when I started my career in IT, I did not plan to become a DBA but more or less accidentaly end up in that role. That is also the main reason that I decided to write to other accidental and Junior DBAs, to help them get started with their journey into the world SQL Servers.