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 🙂
Leave a Reply