Most enterprises run some sort of Antivirus or endpoint protection software on their servers including on SQL Server estates. The software choice depends on things like how effective the software is at dealing with threats, is it easy to deploy and manage, how easy is it to integrate with your existing environment, performance, cost in terms of licensiing and support etc.
I have come across different software choices at different clients depending on the factors above – the most common being Microsoft Defender for Endpoint, Trend Micro, McAfee / Trellix, Sophos, CrowdStrike (Falcon), Symantec. I do see the trend of more organisations opting for Microsoft Defender for Endpoint, because of its tight integration with Windows.
In most larger organisations, the deployment and management of security software is taken care of by the IT Security deparment, rather than SQL Server DBAs. DBAs do not get full visibility of the configuration of the security software. Not properly configuring the secrity software on SQL Server hosts could lead to severe slowdowns and performance issues, CPU spikes, IO bottlenecks, server instability.
At one of my clients, Defender has recently replaced McAfee/Trellix – and that’s led to performance issues in the test environment. The Security team had deployed Defender without incorporating all the SQL Server specific exclusions. After narrowing down the issue to improper Defender configuration I’d worked with the Security team to get the configuration amended, which has resolved the issues. Its not fun scanning a 50 GB backup stripe…is it?
Microsoft has put together a comprehensive guide on how to correctly configure antivirus software for SQL Server. This guide is a must-read for DBAs and Security departments: Configure antivirus software to work with SQL Server.
So, how to check that the correct exlusions are in place for Defender?
Windows Defender exclusions can be queried via PowerShell using the Get-MpPreference module.
You must be an administrator to view the Defender exclusions – so make sure you open your command prompt or PowerShell ISE as an administrator
To check Defender exclusions for file extensions:
(Get-MpPreference).ExclusionExtension
bak
dmp
ldf
mdf
ndf
shd
spl
sql
sqlaudit
trn
xel
xem
To check Defender exclusions to make sure a specific extension (.dmp) is excluded:
(Get-MpPreference).ExclusionExtension | Select-String "dmp"
dmpTo list all the paths excluded from Defender scans:
(Get-MpPreference).ExclusionPath
%ProgramFiles%\system32\inetsrv\
%system32%\system32\spool\
%system32%\system32\spool\printers\
%SystemDrive%\inetpub\logs
%SystemDrive%\inetpub\temp\ASP Compiled Templates
%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files
%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files\
%SystemDrive%\Windows\System32\inetsrv\config\
%SystemRoot%\IIS Temporary Compressed Files
%systemroot%\system32\inetsrv\
c:\ibm\itm\
c:\ibm\itm\tmaitm6\
c:\ibm\itm\tmaitm6_x64\
c:\inetpub\
c:\inetpub\mailroot\
c:\inetpub\temp\iis temporary compressed files\
c:\program files (x86)\microsoft sql server\
c:\program files (x86)\veritas\vxpbx\
c:\program files (x86)\veritas\vxpbx\bin\pbx_exchange.exe
c:\program files\common files\microsoft shared\web server extensions\
c:\program files\microsoft sql server\
c:\program files\veritas\netbackup\
c:\program files\veritas\netbackup\bin\bpbkar32.exe
c:\program files\veritas\netbackup\bin\bpfis.exe
c:\program files\veritas\netbackup\online_util\fi_cntl\
c:\program files\veritas\netbackup\track\
c:\program files\veritas\pdde\mtstrmd.exe
c:\programdata\microsoft\crypto\rsa\Machinekeys\
c:\windows\cluster\
c:\windows\system32\inetsrv\
c:\windows\system32\inetsrv\w3wp.exe
c:\windows\syswow64\inetsrv\w3wp.exe
d:\ibm\itm\
d:\inetpub\wwwroot\default\
d:\msinetdata\
d:\mssql\
E:\inetpub\
q:\To list all the paths containing the word “SQL” in them, that have been excluded from Defender scans:
(Get-MpPreference).ExclusionPath | Select-String "SQL"
c:\program files (x86)\microsoft sql server\
c:\program files\microsoft sql server\
d:\mssql\
To list all the Windows processes excluded from Defender real time scans:
(Get-MpPreference).ExclusionProcess
%SystemDrive%\PHP5433\php-cgi.exe
%SystemRoot%\system32\inetsrv\w3wp.exe
%SystemRoot%\SysWOW64\inetsrv\w3wp.exe
spoolsv.exe
sqlagent.exe
sqlbrowser.exe
SQLDumper.exe
sqlservr.exeTo list all the SQL processes excluded from Defender real time scans:
(Get-MpPreference).ExclusionProcess | Select-String "SQL"
sqlagent.exe
sqlbrowser.exe
SQLDumper.exe
sqlservr.exeThere is a lot more you can do with Get-MpPreference in PowerShell. Feel free to explore Microsoft documentation!
