Every once in a while, one of my SQL Server Consulting clients asks me for my opinions about running Anti-Virus on production SQL Servers. And, amazingly enough, I actually (personally feel and) argue that anti-virus really shouldn’t be run in production data centers. Along those lines, I personally haven’t run anti-virus for over a decade on any of my own machines. Though, on the other hand, I do recommend that non-technical friends and family all run anti-virus (even though I tell them it typically won’t keep ‘crapware’ off of their computers in far too many cases).
Why I Dislike Anti-Virus Solutions On Production Servers
Simply stated, anti-virus solutions cost money to license, can be expensive to maintain, and can commonly cause expensive problems in production – such as when anti-virus solutions block custom code or configuration options or when they block access to critical IO requests.
More importantly, though, I feel that anti-virus solutions on production servers can sadly provide a sense of false-security to many NON-TECHNICAL managers – who falsely sometimes assume that anti-virus is some kind of a magical ‘security blanket’. Because the reality is that anti-virus solutions are far from magical; they can’t simply stop all inbound attacks by virtue of the fact that you pay money for protection from viruses. Instead, anti-virus solutions simply chronicle vast ‘definitions’ of recognized and cataloged attack-vectors and then ‘monitor’ system-activity for similar attack vectors and signatures during run-time.
In other words: anti-virus solutions can’t protect against zero-day exploits, and can only focus on ‘known’ exploits instead. Or, at least, that’s the theory – because recent testing and validation shows that most anti-virus solutions can’t even keep up with the rapid pace of new exploits very well.
Consequently, if you curtail or restrict access on production servers to only competent IT folks (who won’t be browsing the web or opening email attachments from their RDP sessions on production servers), and keep your servers well fire-walled and patched, then you’ll typically never really even going to need to worry about viruses. Or, stated differently, since the vast majority of viruses either take advantage of well-known exploits or require users to ‘invite’ these virus in, then avoiding ‘risky behavior’ is going to do a much better job of protecting against viruses than software can. In most cases.
SQL Server and Anti-Virus
Another huge concern when it comes to your data, SQL Server, and anti-virus solutions is the fact that anti-virus solutions simply can’t protect against bigger security threats and problems that exist in your applications or environment because of your code and practices. In other words: anti-virus software monitors system activity for attacks against well-known vulnerabilities – something that’s not going to be of any use if you’re running custom software to manage your data.
Which, in turn, is where things like SQL Injection come into play – because anti-virus solutions simply can’t protect against coding problems or application vulnerabilities within YOUR business applications as anti-virus is only, really, designed to protect against known vulnerabilities.
SQL Server, Anti-Virus, and Regulatory Compliance (oh my!)
Regardless of my own thoughts about whether or not anti-virus is actually worthwhile on production servers, the reality is that regulatory compliance is riddled with the need for anti-virus – meaning that if you’re looking to become PCI compliant, or trying to stay ahead of SOX or HIPPA, you’re going to need anti-virus.
In my mind, anti-virus is required for these types of compliance primarily either because the legislators who wrote these laws were either non-technical enough to assume that anti-virus is ‘magical’ in some way or another to the point where it had to be included, or that they were (most likely) susceptible to anti-virus lobbyists when these regulations were being written. And that’s not to say that there isn’t any benefit to having anti-virus on production servers. Admins are, after all, human and prone to mistakes. My point, however, is that regulatory compliance isn’t a magical ‘seal of security’ or panacea against being hacked – as, for example, PCI compliant companies (sadly) get hacked and experience data leaks and other problems – even when running anti-virus.
Of course, regardless of how I feel about anti-virus and regulatory compliance, there’s no way you’re going to be able to get around this requirement – and I’m certainly not advocating that you try and argue with auditors that anti-virus isn’t ‘worth it’ or arguing that merely having ‘smart IT’ folks on those servers is protection enough. Because they won’t buy it – and that’ll cause all sorts of problems (i.e., it’ll give them the wrong idea, raise all sorts of red flags, and cost you more headaches and your organization tons of additional cost).
So, long story short: don’t bank on anti-virus as being able to provide you with any ‘real’ protection IF you’re already practicing ‘safe computing’ out on your servers. But, by the same token, in many situations, you’re going to need to run anti-virus on your SQL Servers.
Anti-Virus and SQL Server – Playing Nicely Together
Simply put, the best way to get SQL Server and anti-virus programs to play nicely together is to think in terms of compartmentalization. Or, in other words: let anti-virus programs deal with what they do best, and let SQL Server handle what it does best and avoid, at all possible costs, any interaction between the two.
So, for example, if you’ve properly configured and secured SQL Server (and if you’re using it correctly) it will only need access to a handful of resources on your server including:
- Binaries. Or the the paths to the actual executables for any of your running SQL Server Services (MSSQL, SQL Server Agent, SSAS, etc). Typically these are found, by default, in the C:\Program Files\Microsoft SQL Server folder – though this could easily be a different path on many production machines. (And, note, you’ll likely want to make sure that C:\Program Files (x86)\Microsoft SQL Server is included in any exclusions as well on x64 machines).
- SQL Server Error Logs. Not your database log files, but the text files that SQL Server uses to keep its own ‘event logs’ running or up-to-date. (Which, in turn is also different than Windows’ system event logs as well.) By default the path to these files is, in turn, covered in the paths outlined above – or it’s part of the ‘program files’ data associated with your binaries – though you CAN move the location of these logs if desired (as an advanced operation via the startup parameters).)
- Data And Log Files. Yup – your actual .mdf, .ndf, and .ldf files – or the locations of your data files and log files. (Which you’ll want to make sure get excluded from anything that anti-virus monitors – otherwise creation of new databases, file-growth operations, and other normal ‘stuff’ can/will get blocked by anti-virus operations – which would be fatal in many cases.)
- Backups. Yup, the path to any of your backups – or backup locations is also something you’ll want to make sure that anti-virus doesn’t monitor.
Accordingly, to get anti-virus to play nicely with SQL Server, you’ll want to make sure that it’s been instructed to exclude all of the paths listed above from any type of scans or real-time monitoring. (Likewise, if your anti-virus tries to monitor processes, you’ll want to make sure that it stays away from all of your SQL Server Services such as MMSSQLSERVER (sqlservr.exe), the Full-Text Daemon, the SQL Server Agent, the sqlwriter.exe process, and any other services you might be running (such as MSDTC, SSAS, SSRS, integration services, and so on).
From here, you can let anti-virus do whatever it needs to do and monitor overall system interactions, operations, and processes as needed – but without doing any monitoring of SQL Server. And, in my experience, once you’ve correctly configured anti-virus and SQL Server to avoid any type of interactions or overlap, then you’ll hardly even notice or remember that your SQL Server host is even running anti-virus – which is exactly the situation that you want to be in.