Microsoft SQL Server
- Supported Versions
- What is Discovered and Monitored
- Configuration
- Settings for Access Credentials
- Sample Events
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
-
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
-
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;
- The following additional configuration steps should be performed for the collection of Logon Failures.
- For Server 2012 - https://technet.microsoft.com/en-us/library/ms175850(v=sql.110).aspx
- For Server 2014 - https://technet.microsoft.com/sr-latn-rs/library/ms175850(v=sql.120)
- For Server 2016 - https://msdn.microsoft.com/en-us/library/ms175850.aspx
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
-
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
- Save the four SQL Server Scripts as separate files to My Documents > SQL Server Management Studio > Projects:
- Login to SQL Server Management Studio with an sa account.
- Browse to and execute the Database and Table Creation script to create the database and tables.
- 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. - 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.
Setting | Value |
---|---|
Name | The name of the database instance you're creating the credential for |
Device Type | Microsoft SQL Server |
Access Protocol | JDBC |
Used For | Performance Monitoring |
Pull Interval (minutes) | 5 |
Port | 1433 |
Database Name | <leave this field blank> |
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 |
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.
Setting | Value |
---|---|
Name | The name of the database instance you are creating the credential for |
Device Type | Microsoft SQL Server |
Access Protocol | JDBC |
Used For | Audit |
Pull Interval (minutes) | 5 |
Port | 1433 |
Database Name | <leave this field blank> |
Logon Event Table | PH_Events.dbo.LogOnEvents |
DDL Event Table | PH_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
- Log into Microsoft SQL Management Studio and connect to the DB instance.
- Under Management, go to Maintenance Plans, and create a new plan with the name RemoveOldLogs.
- For Subplan, enter TRUNCATE, and for Description, enter TRUNCATE TABLE.
- Click the Calendar icon to create a recurring, daily task starting at 12:00AM and running every 30 minutes until 11:59:59PM.
- Go to View > Tool Box > Execute T-SQL Statement.
A T-SQL box will be added to the subplan. - In the T-SQL box, enter this command
- Click OK.
- 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.
use PH_Events; EXEC sp_MSForEachTable 'TRUNCATE TABLE ?';
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