Fortinet black logo

External Systems Configuration Guide

Microsoft SQL Server

Microsoft SQL Server

Supported Versions

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014

What is Discovered and Monitored

The following protocols are used to discover and monitor various aspects of Microsoft SQL server.

Protocol Information discovered Metrics collected Used for
SNMP Application type Process level CPU and memory utilization Performance Monitoring
WMI Application type, service mappings Process level metrics: uptime, CPU utilization, Memory utilization, Read I/O KBytes/sec, Write I/O KBytes/sec Performance Monitoring
WMI Windows application event logs - successful and failed login Security Monitoring
JDBC General database info: database name, database version, database size, database owner, database created date, database status, database compatibility level
Database configuration Info: Configure name, Configure value, Configure max and min value, Configure running value
Database backup Info: Database name, Last backup date, Days since last backup
Availability Monitoring
JDBC Database performance metrics (per-instance): Buffer cache hit ratio, Log cache hit ratio, Transactions /sec, Page reads/sec, Page writes/sec, Page splits/sec, Full scans/sec, Deadlocks/sec, Log flush waits/sec, Latch waits/sec, Data file(s) size, Log file(s) used, Log growths, Log shrinks, User connections, Target server memory, Total Server Memory, Active database users, Logged-in database users, Available buffer pool pages, Free buffer pool pages, Average wait time
Database performance metrics (per-instance, per-database): Database name, Data file size, Log file used, Log growths, Log shrinks, Log flush waits/sec, Transaction /sec, Log cache hit ratio
Performance Monitoring
JDBC Locking info: Database id, Database object id, Lock type, Locked resource, Lock mode, Lock status
Blocking info: Blocked Sp Id, Blocked Login User, Blocked Database, Blocked Command, Blocked Process Name, Blocking Sp Id, Blocking Login User, Blocking Database, Blocking Command, Blocking Process Name, Blocked duration
Performance Monitoring
JDBC Database error log
Database audit trail:Failed database logon is also collected through performance monitoring as logon failures cannot be collected via database triggers.
Availability / Performance Monitoring
JDBC None Database audit trail: Successful and failed database logon, Various database operation audit trail including CREATE/ALTER/DROP/TRUNCATE operations on tables, table spaces, databases, clusters, users, roles, views, table indices, triggers etc Security Monitoring and compliance

Event Types

In ADMIN > Device Support > Event, search for "sql server" in the Device Name and Description column to see the event types associated with this device.

Rules

In RESOURCE > Rules, search for " sql server" in the Name column to see the rules associated with this application or device.

Reports

In RESOURCE > Reports , search for "sql server" in the Name column to see the reports associated with this application or device.

Configuration

SNMP

See SNMP Configurations in the Microsoft Windows Server Configuration section.

WMI

See WMI Configurations in the Microsoft Windows Server Configuration section.

JDBC for Performance Monitoring

Creating an User for SQL Server Monitoring

A regular Windows account cannot be used for SQL Server monitoring. FortiSIEM runs on Linux and certain windows libraries needed to do so are not available on Linux. You have to create a separate user with read-only privileges.

Create a Read-Only User to Access System Tables
  1. Log in to your SQL Server with an sa account, and then create a read-only user to access system tables.
    EXEC SP_ADDLOGIN 'AOPerfLogin', 'ProspectHills!', 'master';
    EXEC SP_ADDROLE 'AOPerfRole';
    EXEC SP_ADDUSER 'AOPerfLogin', 'AOPerfUser', 'AOPerfRole';
    GRANT VIEW SERVER STATE TO AOPerfLogin;
    GRANT SELECT ON dbo.sysperfinfo TO AOPerfRole;
    GRANT EXEC on xp_readerrorlog to AOPerfRole
  2. Log in with your newly created read-only account and run these commands.
    Check to see if you get the same results with your read-only account as you do with your sa account.
    SP_WHO2 'active';
    SELECT * FROM sys.databases;
    SELECT * FROM dbo.sysperfinfo;
    SELECT COUNT(*) as count FROM sysprocesses GROUP BY loginame;
  3. The following additional configuration steps should be performed for the collection of Logon Failures.

JDBC for Database Audit Trail Collection

Creating a User for SQL Server Monitoring

A regular Windows account cannot be used for SQL Server monitoring. FortiSIEM runs on Linux and certain windows libraries needed to do so are not available on Linux. You have to create a separate user with read-only privileges.

Create a Read-Only User to Access System Tables

  1. Log in to your SQL Server with an sa account, and then create a read-only user to access system tables.
    EXEC SP_ADDLOGIN 'AOPerfLogin', 'ProspectHills!', 'master';
    EXEC SP_ADDROLE 'AOPerfRole';
    EXEC SP_ADDUSER 'AOPerfLogin', 'AOPerfUser', 'AOPerfRole';
    GRANT VIEW SERVER STATE TO AOPerfLogin;
    GRANT SELECT ON dbo.sysperfinfo TO AOPerfRole;
    GRANT EXEC on xp_readerrorlog to AOPerfRole
  2. Save the four SQL Server Scripts as separate files to My Documents > SQL Server Management Studio > Projects:
  3. Login to SQL Server Management Studio with an sa account.
  4. Browse to and execute the Database and Table Creation script to create the database and tables.
  5. Browse to and execute the Logon Trigger Creation script to create triggers.
    SQL Server introduced Logon Trigger in SQL Server 2005 SP2, so the database version must be greater than 2005 SP2 for logon trigger creation to succeed.
  6. Browse to and execute the DDL Server Level Trigger Creation script to create database events.

You can now configure FortiSIEM to communicate with your device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

Settings for Access Credentials

SNMP Access Credentials for All Devices

See Setting Access Credentials in the Microsoft Windows Server Configuration section.

Settings for SQL Server JDBC Access Credentials for Performance Monitoring

Use these Access Method Definition settings to allow FortiSIEM to communicate with your SQL Server over JDBC for performance monitoring:

Create a Separate Credential for Each Database Instance

If multiple database instances are running on the same server, then each instance must run on a separate port, and you must create a separate access credential for each instance. You must also remember to associate each instance with the server's IP number for the Device Credential Mapping Definition.

SettingValue
NameThe name of the database instance you're creating the credential for
Device TypeMicrosoft SQL Server
Access ProtocolJDBC
Used ForPerformance Monitoring
Pull Interval (minutes)5
Port1433
Database Name<leave this field blank>
User NameThe user you created in step 1 of the JDBC configuration
PasswordThe password associated with the user you created in step 1

Settings for SQL Server JDBC Access Credentials for Database Audit Trail Collection

Use these Access Method Definition settings to allow FortiSIEM to communicate with your SQL Server database instance over JDBC for database audit trail collection:

Create a Separate Credential for Each Database Instance

If multiple database instances are running on the same server, then each instance must run on a separate port, and you must create a separate access credential for each instance. You must also remember to associate each instance with the server's IP number for the Device Credential Mapping Definition.

SettingValue
NameThe name of the database instance you are creating the credential for
Device TypeMicrosoft SQL Server
Access ProtocolJDBC
Used ForAudit
Pull Interval (minutes)5
Port1433
Database Name<leave this field blank>
Logon Event TablePH_Events.dbo.LogOnEvents
DDL Event TablePH_Events.dbo.DDLEvents
User Name The user you created in step 1 of the JDBC configuration
Password The password associated with the user you created in step 1

Creating a Database Truncate Script

Since audit tables grow after time, it is often a good idea to create a database truncate script that can run as a maintenance task and keep the table size under control. it is often necessary to create a database truncate procedure as follows

  1. Log into Microsoft SQL Management Studio and connect to the DB instance.
  2. Under Management, go to Maintenance Plans, and create a new plan with the name RemoveOldLogs.
  3. For Subplan, enter TRUNCATE, and for Description, enter TRUNCATE TABLE.
  4. Click the Calendar icon to create a recurring, daily task starting at 12:00AM and running every 30 minutes until 11:59:59PM.
  5. Go to View > Tool Box > Execute T-SQL Statement.
    A T-SQL box will be added to the subplan.
  6. In the T-SQL box, enter this command
  7. use PH_Events;
    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?';
    
  8. Click OK.
  9. You will be able to see the history of this script's actions by right-clicking on the maintenance task, and then selecting View History.

Settings for MySQL Server JBDC Access Credentials for Synthetic Transaction Monitoring, Snort Audit, McAfee VulnMgr

Use these Access Method Definition settings to allow FortiSIEM to communicate with your Microsoft SQL Server over JDBC for Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr:

Setting Value
Name <name>
Device Type Microsoft SQL Server
Access Protocol JBDC
Used For Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr
Pull Interval (minutes) 5
Port 1433
Database Name <database name>
User Name The administrative user for the database server
Password The password associated with the administrative user

Sample Events

Per Instance Performance Metrics
<134>Apr 16 10:17:56 172.16.22.100 java: [PH_DEV_MON_PERF_MSSQL_SYS|PH_DEV_MON_PERF_MSSQL_SYS]:[eventSeverity]=PHL_INFO,[hostIpAddr]=172.16.22.100,[hostName]=wwwin.accelops.net,
[appGroupName]=Microsoft SQL Server,[dbDataFileSizeKB]=13149056,[dbLogFileUsedKB]=26326,[dbLogGrowthCount]=4,[dbLogShrinkCount]=0,[dbLogFlushPerSec]=1.69,[dbTransPerSec]=4.44,
[dbDeadLocksPerSec]=0,[dbLogCacheHitRatio]=60.01,[dbUserConn]=16,[dbTargetServerMemoryKB]=1543232,[dbTotalServerMemoryKB]=1464760,[dbPageSplitsPerSec]=0.45,
[dbPageWritesPerSec]=0.01,[dbLatchWaitsPerSec]=0.77,[dbPageReadsPerSec]=0.01,[dbFullScansPerSec]=1.83,[dbBufferCacheHitRatio]=100,[dbCount]=8,[dbUserCount]=25,
[dbLoggedinUserCount]=2,[dbPagesInBufferPool]=116850,[dbPagesFreeInBufferPool]=2336,[dbAverageWaitTimeMs]=239376,
[appVersion]=Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64),[serverName]=WIN-08-VCENTER,[instanceName]=MSSQLSERVER,[appPort]=1433
Per Instance, per Database Performance Metrics
[PH_DEV_MON_PERF_MSSQL_PERDB]:[eventSeverity]=PHL_INFO,[hostIpAddr]=172.16.22.100,[hostName]=wwwin.accelops.net,[dbName]=tempdb,[appGroupName]=Microsoft SQL Server,
[dbDataFileSizeKB]=109504,[dbLogFileUsedKB]=434,[dbLogGrowthCount]=4,[dbLogShrinkCount]=0,[dbTransPerSec]=0.96,[dbLogFlushPerSec]=0.01,[dbLogCacheHitRatio]=44.44,
[appVersion]=Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64),[serverName]=WIN-08-VCENTER,[instanceName]=MSSQLSERVER,[appPort]=1433
Generic Info
[PH_DEV_MON_PERF_MSSQL_GEN_INFO]:[eventSeverity]=PHL_INFO,[dbName]= tempdb,[dbSize]= 3.0,[dbowner]= sa,[dbId]= 2,[dbcreated]= 1321545600,
[dbstatus]= Status=ONLINE; Updateability=READ_WRITE; UserAccess=MULTI_USER; Recovery=SIMPLE; Version=655; Collation=SQL_Latin1_General_CP1_CI_AS; SQLSortOrder=52; IsAutoCreateStatistics; IsAutoUpdateStatistics,
[dbcompatibilityLevel]= 100,[spaceAvailable]= 0.9,[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Config Info
[PH_DEV_MON_PERF_MSSQL_CONFIG_INFO]:[eventSeverity]=PHL_INFO,[configureName]= user instances enabled,[configMinimum]= 0,[configMaximum]= 1,[dbConfigValue]= 1,
[configRunValue]= 1,[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Locking Info
[PH_DEV_MON_PERF_MSSQL_LOCK_INFO]:[eventSeverity]=PHL_INFO,[dbId]= 4,[objId]= 1792725439,[lockType]= PAG,[lockedResource]= 1:1256,[lockMode]= IX,
[lockStatus]= GRANT,[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Blocking Info
[PH_DEV_MON_PERF_MSSQL_BLOCKBY_INFO]:[eventSeverity]=PHL_INFO,[blockedSpId]= 51,[blockedLoginUser]= WIN03MSSQL\Administrator,[blockedDbName]= msdb,
[blockedCommand]= UPDATE,[blockedProcessName]= Microsoft SQL Server Management Studio - Query,[blockingSpId]= 54,[blockingLoginUser]= WIN03MSSQL\Administrator,
[blockingDbName]= msdb,[blockingCommand]= AWAITING COMMAND,[blockingProcessName]= Microsoft SQL Server Management Studio - Query,[blockedDuration]= 5180936,
[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Error Log
[PH_DEV_MON_PERF_MSSQL_ERROR_LOG_INFO]:[eventSeverity]=PHL_INFO,[logDate]= 1321585903,[processInfo]= spid52,[logText]= Starting up database 'ReportServer$SQLEXPRESSTempDB'.,
[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Logon Events
134>Feb 08 02:55:34 10.1.2.54 java: [MSSQL_Logon_Success]:[eventSeverity]=PHL_INFO, [eventTime]=2014-02-08 02:54:00.977, [rptIp]=10.1.2.54, [relayIp]=10.1.2.54, [srcName]=<local machine>, [user]=NT SERVICE\ReportServer$MSSQLSERVEJIANFA, [srcApp]=Report Server, [instanceName]=MSSQLSERVEJIANFA, [procId]=52, [loginType]=Windows (NT) Login, [securityId]=AQYAAAAAAAVQAAAALJAZf5XMbcLh8PUDY31LioZ3Uwo=, [isPooled]=1, [destName]=WIN-S2EDLFIUPQK, [destPort]=1437,
DDL Events - Create Database
<134>Sep 29 15:34:48 10.1.2.54 java: [MSSQL_Create_database]:[eventSeverity]=PHL_INFO, [eventTime]=2013-09-29 15:34:05.687, [rptIp]=10.1.2.54, [relayIp]=10.1.2.54, [user]=WIN-S2EDLFIUPQK\Administrator, [dbName]=JIANFA, [instanceName]=MSSQLSERVER, [objName]=, [procId]=59, [command]=CREATE DATABASE JIANFA, [destName]=WIN-S2EDLFIUPQK, [destPort]=1433,
DDL Events - Create index
<134>Sep 29 15:34:48 10.1.2.54 java: [MSSQL_Create_index]:[eventSeverity]=PHL_INFO, [eventTime]=2013-09-29 15:30:40.557, [rptIp]=10.1.2.54, [relayIp]=10.1.2.54, [user]=WIN-S2EDLFIUPQK\Administrator, [dbName]=master, [instanceName]=MSSQLSERVER, [objName]=IndexTest, [procId]=58, [command]=create index IndexTest on dbo.MSreplication_options(optname);, [schemaName]=dbo, [objType]=INDEX, [destName]=WIN-S2EDLFIUPQK, [destPort]=1433

Microsoft SQL Server

Supported Versions

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014

What is Discovered and Monitored

The following protocols are used to discover and monitor various aspects of Microsoft SQL server.

Protocol Information discovered Metrics collected Used for
SNMP Application type Process level CPU and memory utilization Performance Monitoring
WMI Application type, service mappings Process level metrics: uptime, CPU utilization, Memory utilization, Read I/O KBytes/sec, Write I/O KBytes/sec Performance Monitoring
WMI Windows application event logs - successful and failed login Security Monitoring
JDBC General database info: database name, database version, database size, database owner, database created date, database status, database compatibility level
Database configuration Info: Configure name, Configure value, Configure max and min value, Configure running value
Database backup Info: Database name, Last backup date, Days since last backup
Availability Monitoring
JDBC Database performance metrics (per-instance): Buffer cache hit ratio, Log cache hit ratio, Transactions /sec, Page reads/sec, Page writes/sec, Page splits/sec, Full scans/sec, Deadlocks/sec, Log flush waits/sec, Latch waits/sec, Data file(s) size, Log file(s) used, Log growths, Log shrinks, User connections, Target server memory, Total Server Memory, Active database users, Logged-in database users, Available buffer pool pages, Free buffer pool pages, Average wait time
Database performance metrics (per-instance, per-database): Database name, Data file size, Log file used, Log growths, Log shrinks, Log flush waits/sec, Transaction /sec, Log cache hit ratio
Performance Monitoring
JDBC Locking info: Database id, Database object id, Lock type, Locked resource, Lock mode, Lock status
Blocking info: Blocked Sp Id, Blocked Login User, Blocked Database, Blocked Command, Blocked Process Name, Blocking Sp Id, Blocking Login User, Blocking Database, Blocking Command, Blocking Process Name, Blocked duration
Performance Monitoring
JDBC Database error log
Database audit trail:Failed database logon is also collected through performance monitoring as logon failures cannot be collected via database triggers.
Availability / Performance Monitoring
JDBC None Database audit trail: Successful and failed database logon, Various database operation audit trail including CREATE/ALTER/DROP/TRUNCATE operations on tables, table spaces, databases, clusters, users, roles, views, table indices, triggers etc Security Monitoring and compliance

Event Types

In ADMIN > Device Support > Event, search for "sql server" in the Device Name and Description column to see the event types associated with this device.

Rules

In RESOURCE > Rules, search for " sql server" in the Name column to see the rules associated with this application or device.

Reports

In RESOURCE > Reports , search for "sql server" in the Name column to see the reports associated with this application or device.

Configuration

SNMP

See SNMP Configurations in the Microsoft Windows Server Configuration section.

WMI

See WMI Configurations in the Microsoft Windows Server Configuration section.

JDBC for Performance Monitoring

Creating an User for SQL Server Monitoring

A regular Windows account cannot be used for SQL Server monitoring. FortiSIEM runs on Linux and certain windows libraries needed to do so are not available on Linux. You have to create a separate user with read-only privileges.

Create a Read-Only User to Access System Tables
  1. Log in to your SQL Server with an sa account, and then create a read-only user to access system tables.
    EXEC SP_ADDLOGIN 'AOPerfLogin', 'ProspectHills!', 'master';
    EXEC SP_ADDROLE 'AOPerfRole';
    EXEC SP_ADDUSER 'AOPerfLogin', 'AOPerfUser', 'AOPerfRole';
    GRANT VIEW SERVER STATE TO AOPerfLogin;
    GRANT SELECT ON dbo.sysperfinfo TO AOPerfRole;
    GRANT EXEC on xp_readerrorlog to AOPerfRole
  2. Log in with your newly created read-only account and run these commands.
    Check to see if you get the same results with your read-only account as you do with your sa account.
    SP_WHO2 'active';
    SELECT * FROM sys.databases;
    SELECT * FROM dbo.sysperfinfo;
    SELECT COUNT(*) as count FROM sysprocesses GROUP BY loginame;
  3. The following additional configuration steps should be performed for the collection of Logon Failures.

JDBC for Database Audit Trail Collection

Creating a User for SQL Server Monitoring

A regular Windows account cannot be used for SQL Server monitoring. FortiSIEM runs on Linux and certain windows libraries needed to do so are not available on Linux. You have to create a separate user with read-only privileges.

Create a Read-Only User to Access System Tables

  1. Log in to your SQL Server with an sa account, and then create a read-only user to access system tables.
    EXEC SP_ADDLOGIN 'AOPerfLogin', 'ProspectHills!', 'master';
    EXEC SP_ADDROLE 'AOPerfRole';
    EXEC SP_ADDUSER 'AOPerfLogin', 'AOPerfUser', 'AOPerfRole';
    GRANT VIEW SERVER STATE TO AOPerfLogin;
    GRANT SELECT ON dbo.sysperfinfo TO AOPerfRole;
    GRANT EXEC on xp_readerrorlog to AOPerfRole
  2. Save the four SQL Server Scripts as separate files to My Documents > SQL Server Management Studio > Projects:
  3. Login to SQL Server Management Studio with an sa account.
  4. Browse to and execute the Database and Table Creation script to create the database and tables.
  5. Browse to and execute the Logon Trigger Creation script to create triggers.
    SQL Server introduced Logon Trigger in SQL Server 2005 SP2, so the database version must be greater than 2005 SP2 for logon trigger creation to succeed.
  6. Browse to and execute the DDL Server Level Trigger Creation script to create database events.

You can now configure FortiSIEM to communicate with your device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

Settings for Access Credentials

SNMP Access Credentials for All Devices

See Setting Access Credentials in the Microsoft Windows Server Configuration section.

Settings for SQL Server JDBC Access Credentials for Performance Monitoring

Use these Access Method Definition settings to allow FortiSIEM to communicate with your SQL Server over JDBC for performance monitoring:

Create a Separate Credential for Each Database Instance

If multiple database instances are running on the same server, then each instance must run on a separate port, and you must create a separate access credential for each instance. You must also remember to associate each instance with the server's IP number for the Device Credential Mapping Definition.

SettingValue
NameThe name of the database instance you're creating the credential for
Device TypeMicrosoft SQL Server
Access ProtocolJDBC
Used ForPerformance Monitoring
Pull Interval (minutes)5
Port1433
Database Name<leave this field blank>
User NameThe user you created in step 1 of the JDBC configuration
PasswordThe password associated with the user you created in step 1

Settings for SQL Server JDBC Access Credentials for Database Audit Trail Collection

Use these Access Method Definition settings to allow FortiSIEM to communicate with your SQL Server database instance over JDBC for database audit trail collection:

Create a Separate Credential for Each Database Instance

If multiple database instances are running on the same server, then each instance must run on a separate port, and you must create a separate access credential for each instance. You must also remember to associate each instance with the server's IP number for the Device Credential Mapping Definition.

SettingValue
NameThe name of the database instance you are creating the credential for
Device TypeMicrosoft SQL Server
Access ProtocolJDBC
Used ForAudit
Pull Interval (minutes)5
Port1433
Database Name<leave this field blank>
Logon Event TablePH_Events.dbo.LogOnEvents
DDL Event TablePH_Events.dbo.DDLEvents
User Name The user you created in step 1 of the JDBC configuration
Password The password associated with the user you created in step 1

Creating a Database Truncate Script

Since audit tables grow after time, it is often a good idea to create a database truncate script that can run as a maintenance task and keep the table size under control. it is often necessary to create a database truncate procedure as follows

  1. Log into Microsoft SQL Management Studio and connect to the DB instance.
  2. Under Management, go to Maintenance Plans, and create a new plan with the name RemoveOldLogs.
  3. For Subplan, enter TRUNCATE, and for Description, enter TRUNCATE TABLE.
  4. Click the Calendar icon to create a recurring, daily task starting at 12:00AM and running every 30 minutes until 11:59:59PM.
  5. Go to View > Tool Box > Execute T-SQL Statement.
    A T-SQL box will be added to the subplan.
  6. In the T-SQL box, enter this command
  7. use PH_Events;
    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?';
    
  8. Click OK.
  9. You will be able to see the history of this script's actions by right-clicking on the maintenance task, and then selecting View History.

Settings for MySQL Server JBDC Access Credentials for Synthetic Transaction Monitoring, Snort Audit, McAfee VulnMgr

Use these Access Method Definition settings to allow FortiSIEM to communicate with your Microsoft SQL Server over JDBC for Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr:

Setting Value
Name <name>
Device Type Microsoft SQL Server
Access Protocol JBDC
Used For Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr
Pull Interval (minutes) 5
Port 1433
Database Name <database name>
User Name The administrative user for the database server
Password The password associated with the administrative user

Sample Events

Per Instance Performance Metrics
<134>Apr 16 10:17:56 172.16.22.100 java: [PH_DEV_MON_PERF_MSSQL_SYS|PH_DEV_MON_PERF_MSSQL_SYS]:[eventSeverity]=PHL_INFO,[hostIpAddr]=172.16.22.100,[hostName]=wwwin.accelops.net,
[appGroupName]=Microsoft SQL Server,[dbDataFileSizeKB]=13149056,[dbLogFileUsedKB]=26326,[dbLogGrowthCount]=4,[dbLogShrinkCount]=0,[dbLogFlushPerSec]=1.69,[dbTransPerSec]=4.44,
[dbDeadLocksPerSec]=0,[dbLogCacheHitRatio]=60.01,[dbUserConn]=16,[dbTargetServerMemoryKB]=1543232,[dbTotalServerMemoryKB]=1464760,[dbPageSplitsPerSec]=0.45,
[dbPageWritesPerSec]=0.01,[dbLatchWaitsPerSec]=0.77,[dbPageReadsPerSec]=0.01,[dbFullScansPerSec]=1.83,[dbBufferCacheHitRatio]=100,[dbCount]=8,[dbUserCount]=25,
[dbLoggedinUserCount]=2,[dbPagesInBufferPool]=116850,[dbPagesFreeInBufferPool]=2336,[dbAverageWaitTimeMs]=239376,
[appVersion]=Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64),[serverName]=WIN-08-VCENTER,[instanceName]=MSSQLSERVER,[appPort]=1433
Per Instance, per Database Performance Metrics
[PH_DEV_MON_PERF_MSSQL_PERDB]:[eventSeverity]=PHL_INFO,[hostIpAddr]=172.16.22.100,[hostName]=wwwin.accelops.net,[dbName]=tempdb,[appGroupName]=Microsoft SQL Server,
[dbDataFileSizeKB]=109504,[dbLogFileUsedKB]=434,[dbLogGrowthCount]=4,[dbLogShrinkCount]=0,[dbTransPerSec]=0.96,[dbLogFlushPerSec]=0.01,[dbLogCacheHitRatio]=44.44,
[appVersion]=Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64),[serverName]=WIN-08-VCENTER,[instanceName]=MSSQLSERVER,[appPort]=1433
Generic Info
[PH_DEV_MON_PERF_MSSQL_GEN_INFO]:[eventSeverity]=PHL_INFO,[dbName]= tempdb,[dbSize]= 3.0,[dbowner]= sa,[dbId]= 2,[dbcreated]= 1321545600,
[dbstatus]= Status=ONLINE; Updateability=READ_WRITE; UserAccess=MULTI_USER; Recovery=SIMPLE; Version=655; Collation=SQL_Latin1_General_CP1_CI_AS; SQLSortOrder=52; IsAutoCreateStatistics; IsAutoUpdateStatistics,
[dbcompatibilityLevel]= 100,[spaceAvailable]= 0.9,[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Config Info
[PH_DEV_MON_PERF_MSSQL_CONFIG_INFO]:[eventSeverity]=PHL_INFO,[configureName]= user instances enabled,[configMinimum]= 0,[configMaximum]= 1,[dbConfigValue]= 1,
[configRunValue]= 1,[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Locking Info
[PH_DEV_MON_PERF_MSSQL_LOCK_INFO]:[eventSeverity]=PHL_INFO,[dbId]= 4,[objId]= 1792725439,[lockType]= PAG,[lockedResource]= 1:1256,[lockMode]= IX,
[lockStatus]= GRANT,[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Blocking Info
[PH_DEV_MON_PERF_MSSQL_BLOCKBY_INFO]:[eventSeverity]=PHL_INFO,[blockedSpId]= 51,[blockedLoginUser]= WIN03MSSQL\Administrator,[blockedDbName]= msdb,
[blockedCommand]= UPDATE,[blockedProcessName]= Microsoft SQL Server Management Studio - Query,[blockingSpId]= 54,[blockingLoginUser]= WIN03MSSQL\Administrator,
[blockingDbName]= msdb,[blockingCommand]= AWAITING COMMAND,[blockingProcessName]= Microsoft SQL Server Management Studio - Query,[blockedDuration]= 5180936,
[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Error Log
[PH_DEV_MON_PERF_MSSQL_ERROR_LOG_INFO]:[eventSeverity]=PHL_INFO,[logDate]= 1321585903,[processInfo]= spid52,[logText]= Starting up database 'ReportServer$SQLEXPRESSTempDB'.,
[appVersion]= Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86),[serverName]= WIN03MSSQL\SQLEXPRESS
Logon Events
134>Feb 08 02:55:34 10.1.2.54 java: [MSSQL_Logon_Success]:[eventSeverity]=PHL_INFO, [eventTime]=2014-02-08 02:54:00.977, [rptIp]=10.1.2.54, [relayIp]=10.1.2.54, [srcName]=<local machine>, [user]=NT SERVICE\ReportServer$MSSQLSERVEJIANFA, [srcApp]=Report Server, [instanceName]=MSSQLSERVEJIANFA, [procId]=52, [loginType]=Windows (NT) Login, [securityId]=AQYAAAAAAAVQAAAALJAZf5XMbcLh8PUDY31LioZ3Uwo=, [isPooled]=1, [destName]=WIN-S2EDLFIUPQK, [destPort]=1437,
DDL Events - Create Database
<134>Sep 29 15:34:48 10.1.2.54 java: [MSSQL_Create_database]:[eventSeverity]=PHL_INFO, [eventTime]=2013-09-29 15:34:05.687, [rptIp]=10.1.2.54, [relayIp]=10.1.2.54, [user]=WIN-S2EDLFIUPQK\Administrator, [dbName]=JIANFA, [instanceName]=MSSQLSERVER, [objName]=, [procId]=59, [command]=CREATE DATABASE JIANFA, [destName]=WIN-S2EDLFIUPQK, [destPort]=1433,
DDL Events - Create index
<134>Sep 29 15:34:48 10.1.2.54 java: [MSSQL_Create_index]:[eventSeverity]=PHL_INFO, [eventTime]=2013-09-29 15:30:40.557, [rptIp]=10.1.2.54, [relayIp]=10.1.2.54, [user]=WIN-S2EDLFIUPQK\Administrator, [dbName]=master, [instanceName]=MSSQLSERVER, [objName]=IndexTest, [procId]=58, [command]=create index IndexTest on dbo.MSreplication_options(optname);, [schemaName]=dbo, [objType]=INDEX, [destName]=WIN-S2EDLFIUPQK, [destPort]=1433