SQL Server Security: Dealing with Anti-Virus Programs

With the recent slate of "virus" attacks plaguing us, a question

that has come up fairly often is how to handle anti-virus programs running on the

same system as SQL Server. If you tried to do an Internet search on SQL Server

and anti-virus best practices you've probably been left with a bunch of links to

descriptions of the SQL Slammer worm but not a whole lot else. I won't say this

is unexpected because in an ideal world we'd want SQL Server to have the

system all to itself without any other agents or programs running at the same

time. Also, SQL Slammer's effects were felt far and wide and it dominates

any searches that don't specifically exclude "slammer" in the search

criteria.

But we don't live in an ideal world. Once upon a time running without anti-virus

software may have been possible but it is no longer. Then again, virus

definitions would come out monthly. Then the pace accelerated to weekly. Now

it's not unusual, especially given rapid variants like with Beagle/Bagle and

NetSky, to see multiple definition downloads in one day. With the change in the

environment, anti-virus programs are now deployed on servers without a second

thought. However, there are some considerations to take into account when

deploying anti-virus agents and SQL Server in conjunction. I'll cover them in

this article.

The Paging File

The Microsoft operating systems all make use of a paging file in order to

"expand" memory virtually. Named pagefile.sys, the operating systems

needs unfettered access to this file. This file is usually opened and locked by

the operating system and there's no point to scanning it. As is pointed out in

the Small Business Server FAQ, this is one of the files you want to exclude from

scanning. I've included a link to that FAQ in the Additional Resources section.

While .sys files aren't executable, that's not to say some savvy virus writer

won't decide to use the name "pagefile.sys" as part of his or her

overall attack. When configuring what not to scan, only mark the actual

paging files the operating system is going to use. For instance, a pagefile.sys

entry under c:\winnt\ or c:\windows\ is not an actual paging file for the

system. The paging files would only be found in the root directory of any drives

they are configured to reside on. We would want our anti-virus solution detecting

such "out-of-place" files.

Recommendation: This recommendation is true regardless of the

application running on the server. Configure the anti-virus software to exclude

pagefile.sys in its scanning but ensure you match this exclusion to what the

operating system is actually using as the paging file.

Database Files (.mdf, .ndf, .ldf)

The standard extensions for SQL Server database files are .mdf, .ndf, and .ldf.

Theoretically, these should never get infected as they aren't "run"

like a .exe or .com file and they don't have the ability to execute macros like

Microsoft Office documents (of which .doc for Word and .xls for Excel are the

most prevalent for having macro viruses). Therefore, there's not a whole lot of

reason to scan these files. In fact, there's a very good reason to exclude them

from your anti-virus scans.

Microsoft KB article 309422 points out that if a virus sweep has a database

file open when SQL Server tries to open the database, the database may be marked

suspect. SQL Server is going to need exclusive access to the files and an anti-virus

agent touching one at the same time SQL Server is trying to open it is

going to cause a conflict. Of course, the same is true of any agent software,

such as with backup software. The question that comes to mind is, "How

often does SQL Server attempt to open the file?" The short answer is,

"It depends." SQL Server will always attempt to open all the database

files when it first starts up. In fact, you can often see entries in the log

which begin with "Starting up database" and the database name:

2004-04-01 10:18:04.59 spid3 Starting up database 'master'.

2004-04-01 10:18:06.98 server Using 'SSNETLIB.DLL' version '8.0.818'.

2004-04-01 10:18:06.98 spid5 Starting up database 'model'.

2004-04-01 10:18:07.20 spid3 Server name is 'MySQLServer'.

2004-04-01 10:18:07.39 spid8 Starting up database 'msdb'.

2004-04-01 10:18:07.39 spid9 Starting up database 'pubs'.

2004-04-01 10:18:07.58 spid10 Starting up database 'Northwind'.

2004-04-01 10:18:07.58 spid11 Starting up database 'distribution'.

2004-04-01 10:18:08.98 server SQL server listening on 192.168.1.101: 1433.

2004-04-01 10:18:08.98 server SQL server listening on 127.0.0.1: 1433.

2004-04-01 10:18:11.35 server SQL server listening on TCP, Shared Memory, Named Pipes.

2004-04-01 10:18:11.35 server SQL Server is ready for client connections

2004-04-01 10:18:11.37 spid5 Clearing tempdb database.

2004-04-01 10:18:18.25 spid5 Starting up database 'tempdb'.

2004-04-01 10:18:20.15 spid3 Recovery complete.

The other time SQL Server may attempt to open a database is if a database is

set to AutoClose. When someone attempts to access the database, SQL Server will

have to open up the files and bring the database on-line. Since it is typically

rare for production databases to be configured to AutoClose, generally the only

time we'd see SQL Server attempt to open a database file is when SQL Server

first starts up. However, while SQL Server has the file open the anti-virus software isn't going to be able to scan it. Therefore, there's really

no point in attempting to scan files ending in these extensions.

Recommendation: Exclude files of .mdf, .ldf, and .ndf extensions from

scanning.

Backup Files

In my environment we typically let SQL Server backup a database to a file on

the local system and then copy that file to a central server. Nightly, backups

run on both the SQL Server and the central backup repository, meaning we tend to

have two copies of critical backup files on tape. Every once in a while we'd

notice that the backup agents would be busy reading the file and writing to tape

at the same time SQL Server would attempt to do its backup to the very same

file. Now, the backup agent had opened the file first and therefore SQL Server

was unable to lock the file. SQL Server would give up and the backup wouldn't

run. The reason we had this problem is we were simply re-using the same backup

file names. When we switched over to Perl scripts and an enterprise job engine,

we started time stamping the backup files in the names themselves. That didn't

eliminate the contention with the backup agent but what it did do was ensure SQL

Server was always the winner. Given we're copying the file to a second server as

well as a few other steps we took, we've minimized the overall risk of not

having the right backup.

Needless to say, if SQL Server and a backup agent can contend over a backup

file, so too can SQL Server and an anti-virus agent. Therefore some folks

recommend excluding .bak and .trn files (or whatever extensions they prefer)

from scanning as well. I have seen a case where we think the anti-virus agent

was jumping in between the closing of the backup and a renaming process to do

its scan. The rename failed as a result and the next step to copy didn't execute

either. We have since gone to a strategy of excluding the backup files from

scanning.

As for risk, these files aren't typically executable

programs so the risk isn't very great if they aren't scanned. If you're reusing

the same names, such as with a backup device or just a standard script that

doesn't timestamp or do something to make the names different, you may want to

exclude the backup extensions as well. It's better not to scan a file that you

can't execute than have SQL Server fail out trying to write a backup you very

well may need.

Recommendation: Exclude file extensions corresponding to backup files

if you reuse file names.

Full Text Catalogs

KB article 309422 also states if an anti-virus agent is scanning a full-text

catalog at the same time as the Microsoft Search service (MSSearch AKA Full Text

Indexing), "you may experience problems with the full text catalog."

Therefore, if you're using full-text indexing be sure to exclude the directories

containing the full-text catalog files. By default this is the FTDATA directory

underneath your SQL Server instance directory. A completely default install

would have the full-text files under C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA.

However, you can specify the location of the files when creating the catalog. If

you need to check the paths, take a look at sp_help_fulltext_catalogs.

Recommendation: Exclude any full-text catalog directories if you use

full-text indexing.

System Directories

Once upon a time I saw literature recommending the exclusion of C:\Windows

when doing anti-virus scans. At the time, though, definitions were showing up

about once a month or so and the danger was the "rogue floppy" being

brought into the office. Those days are long gone and I would say this

recommendation should be as well. I've heard it repeated from time to time, but

for me it doesn't make sense any longer. So why was it ever a suggestion?

The main rationale was the same one used for the paging file: the system

should be using the file(s) in question and we don't want to take a chance on an

anti-virus agent locking the file when the operating system needs it. While

these points may have been of great concern in the past, I don't feel they

outweigh the risk nowadays, especially with efforts to make the OS more reliable

and fault tolerant. Let's face it: a lot of viruses and worms now drop their

files in the system directories. For instance, the known Sasser variants drop

their files in %WINDIR%, a known directory variable that corresponds to wherever

Windows is installed (C:\WINNT or C:\WINDOWS usually). Welchia's variants tended

to use %SYSTEM%, which typically corresponds to the System32 directory

(C:\WINNT\SYSTEM32 or C:\WINDOWS\SYSTEM32). Therefore, choosing to exclude the

system directories is no longer a viable option.

Recommendation: Scan system directories as you would any other

directory.

Program Directories

I put program directories in the same category as system directories.

"Old school" viruses would often attach themselves to already existing

.exe and .com files. While the latest batch of viruses and worms haven't taken

this approach, it never goes out of style. One need only look back as far as

Nimda for a very widespread worm that went after executable files. Therefore,

program directories should be scanned as well.

Recommendation: Scan program directories as you would any other

directory.

Summary

Here is a summary of the recommendations.

Don'ts:

• Do not scan the paging file(s) on the system.
• Do not scan the SQL Server database file extensions: .mdf, .ndf, and .ldf.
• Do not scan extensions for backup files.
• Do not scan Full-Text directories.

Do's:

• Do scan system directories.
• Do scan program directories.

Concluding Remarks

This article presents my opinion and recommendations for how to handle SQL

Server and anti-virus agents in conjunction. Yours may differ and I hope this

article spurs some discussion on the topic. I've tried to explain why I've made

a recommendation to offer up for consideration. Ultimately your organization is

the final arbiter of how you go about implementing anti-virus agents in your

environment. Carefully consider all the factors when determining how to

implement anti-virus and SQL Server on your systems.

Additional Resources

SQL Server Related Resources:

• Frequently Asked Questions About Small Business Server - http://www.microsoft.com/sbserver/community/sbs_faq.asp
• INF: Consideration for a Virus Scanner on a Computer That Is Running SQL Server -

  http://support.microsoft.com/default.aspx?scid=kb;en-us;309422

• Running Virus-Scanning Software on the Same Server as SQL Server - http://www.microsoft.com/sql/techinfo/tips/administration/virusscanning.asp
• MVP N. Vyas Kondreddi's Overview of SQL Server security model and security

  best practices - http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Anti-Virus Providers

• AVG Anti-Virus Virus News, Warnings page - http://www.grisoft.com/us/us_vir_tt.php
• F-Secure Virus Info page - http://www.f-secure.com/virus-info/
• Kaspersky's VirusList - http://www.viruslist.com/
• McAfee Security Virus Info page - http://us.mcafee.com/virusInfo/default.asp
• Sophos Virus Info page - http://www.sophos.com/virusinfo/
• Symantec Security Response site - http://securityresponse.symantec.com/

This list is by no means exhaustive. However, these are the pages I most

often use when looking for information about a new outbreak.