Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Security: Dealing with Anti-Virus Programs

By Brian Kelley,

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:

Anti-Virus Providers

This list is by no means exhaustive. However, these are the pages I most often use when looking for information about a new outbreak.

 © 2004 by K. Brian Kelley. http://www.truthsolutions.com/
 Author of Start to Finish Guide to SQL Server Performance Monitoring (http://www.netimpress.com).

 

 

Total article views: 22706 | Views in the last 30 days: 13
 
Related Articles
FORUM

complete database backup directory is invalid in sql server 2000

complete database backup directory is invalid in sql server 2000

FORUM

Server Database Backup

Server Database Backup

FORUM

how to take DataBase Backup from remote server to local system

how to take DataBase Backup from remote server to local system

FORUM

System databases

System databases

SCRIPT

Script to Restore Entire database from a directory

Script to restore the entire databases from the backup directory just by passing the backup director...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones