SQLStarters

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.

Here’s a link to TechNet article on how to do that.

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.
2383	TCP	Analysis Services default TCP port for clustered or standalone instances.

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 🙂