Client Security and SQL Server 2005

Client Security and SQL Server 2005

Microsoft Forefront Client Security

Client Security uses SQL Server 2005 to store information reported from the managed computers in the organization. Client Security uses two databases to store data:

Client Security collection database (also called the MOM OnePoint database)
Client Security reporting database (also called the MOM SystemCenterReporting database)

These databases are created during Client Security server setup. On the Component Installation page of the Setup wizard, you select the Collection database and Reporting server and reporting database components. Later in the wizard, on the Collection Database page and the Reporting Database page, you specify the server name of the computer running SQL Server for each database. Server setup connects to these computers running SQL Server and creates the respective database.

The Client Security databases use the value of the SQL Server default database location when determining the location for the database and transaction log files for both the collection and reporting databases. For information about database and log file placement, see "Disk considerations" in Tuning Client Security database components in the Client Security Performance and Scalability Guide (http://go.microsoft.com/fwlink/?LinkID=89667). If you need to place these databases in a location other than the installation path of SQL Server, you need to modify this setting on the collection database server and the reporting database server.

To change the default location for SQL Server databases and log files

Start Microsoft SQL Server Management Studio.
In Object Explorer, right-click ServerName, and then click Properties.
In the Server Properties dialog box, under Select a page, click Database Settings.
Under Database default locations, enter the paths for the data and log locations, and then click OK.

The Client Security databases are used for short term (collection database) and long term (reporting database) storage of data (including events, alerts, and definition update information) from managed computers. Additionally, they store:

Information (from the definition updates) about threats and malware.
Information (created by the Client Security administrator) about the Client Security policies.
Additional MOM configuration information, such as MOM management pack configuration and computer information.

SQL Server data flow

Client Security agents send events and alerts to the Client Security collection server. The collection server runs Windows Server 2003 and the Client Security–optimized version of MOM 2005.

Data from agents is received by the Client Security collection server and placed in the server's inbound data queue. The collection server then passes the data to the collection database server.

After the data has been passed to the computer running SQL Server, the data is written first to the transaction log file for the collection database. SQL Server 2005 uses transactions logs to maintain the integrity of SQL Server databases. Data submitted to SQL Server is first written to the transaction log as a complete transaction. After the data transaction has been completely written to the transaction log, the data is written to the database itself. This database write procedure guarantees the integrity of your data. The entire transaction is either committed to the database or no data is written to the database.

The purpose of the collection database is short-term reporting. After data is written to the collection database, it stays there for four days. After the fourth day, it is transferred to the reporting database and removed, or groomed, from the collection database. This data transfer from the collection database to the reporting database is performed by a SQL Server DTS job.

The following diagram illustrates the data flow from the Client Security agent to the collection server and then, via the DTS job, to the reporting server.

SQL Server jobs

The Client Security–optimized MOM 2005 installation provides a default set of SQL Server 2005 maintenance jobs. For more information about the jobs for the collection database, see Default Database Jobs in the MOM 2005 product documentation (http://go.microsoft.com/fwlink/?LinkID=87244).

Additionally, MOM 2005 adds the SCDWGroomJob to the reporting database server. This job, which is scheduled to run nightly at 03:00 (3:00 A.M.), runs the grooming stored procedure on the reporting database. None of these default MOM database maintenance jobs are modified by Client Security.

The installation of Client Security does, however, add two additional SQL Server jobs to the reporting server.

Job name	Schedule	Purpose
Microsoft Forefront Client Security	01:15 (1:15 A.M.) every day	This is a DTS job that pulls information about threats and Client Security policies from the collection database to the reporting database.
FCS - Update SystemCenterReporting Partition Function	06:00 (6:00 A.M.) weekly on Sunday	This job updates the partitioning done on the tables and indexes in the reporting database. This job is only installed if the SQL Server edition is Enterprise Edition.

The DTS job that copies data out of the collection database and inserts it into the reporting database runs as a scheduled task in Scheduled Tasks in Control Panel. This job runs at 01:00 (1:00 A.M.) every day on the reporting database server. For information about the parameters that the DTS job uses, see Configuring Data Transformation Services (DTS) (http://go.microsoft.com/fwlink/?LinkId=89390).

User accounts that access the SQL Server databases

During the installation of SQL Server 2005, you are asked for a user account that the selected SQL Server services use to run. Because all but two Client Security topologies locate the Client Security databases on remote computers running SQL Server, you will need to assign these services either a domain user account or the network service account.

The MOM components of Client Security require access to SQL Server and the databases placed on the computer running SQL Server. This is accomplished using the DAS account. This account is requested during Client Security server setup, on the Collection Server page. The Client Security Setup wizard automatically grants this account the appropriate permissions to access the collection database; it must have Db_owner permissions on the collection database.

Next, on the Collection Database page, the wizard requests the Reporting account. This account is created on the reporting database server and used by SQL Server Reporting Services to access the collection and reporting databases. This account requires Db_owner permissions on both the collection database and the reporting database.

The Client Security SQL Server DTS job uses an account specified in Client Security server setup as well, on the Reporting Database page. Setup grants this account read access to the collection database and write access to the reporting database.

The collection server periodically needs to run server-side scripts that require access to the collection database. Setup requests a user account for this on the Action Account page of server setup. This account, a domain user, requires Db_owner permissions to the collection database. This account also requires local administrator rights on the collection server.

The following diagram illustrates the links between the Client Security components and their respective user accounts.

User accounts that access the SQL Server databases

Finally, the user running the Client Security console requires permission to the Client Security SQL Server databases. For more information about assigning permissions for these tasks, see Working with user roles (http://go.microsoft.com/fwlink/?LinkID=86555).