RSA Security Analytics

Event Source Log Configuration Guide

Microsoft SQL Server

Last Modified: Thursday, July 30, 2015

Event Source Product Information:

Vendor: 

Event Source: SQL Server

Versions: 2000, 2005, 2008, 2012, 2014, and MS SQL Express

Additional Downloads:

sqlServerAudit2000.sql

sqlServerAudit2005.sql

sqlServerAudit2008.sql

sqlServerAudit2012.sql

sqlServerAudit2014.sql

uninstallSqlServerAudit.sql

RSA_MSSQLAuditStoredProcedures.dll

sftpagent.conf.mssql

RSA Product Information:

Supported On: Security Analytics 10.0 and later

Event Source Log Parser: mssql

Microsoft SQL Collection Overview

RSA Security Analytics supports several different collection methods for Microsoft SQL Server, depending on the version of SQL Server and Microsoft Windows that you are using. The following table describes the various combinations of Windows version, MS SQL version, and the collection methods used for each.

MS SQL Version Platform Collection Methods

2000 Windows 2000, 2003 ODBC, Windows Legacy, File 2005, 2008 Standard Windows 2003, 2008 ODBC, Windows Eventing, File 2008 Enterprise and later Windows 2008 and later Windows Eventing

Note the following:

l If you are running SQL Server 2000, RSA recommends that you configure

collection for all three methods: the File Service, the ODBC Service, and the Windows Service.

l For SQL Server 2008, 2012, or 2014, running on Windows Server 2008 or above,

you set up and configure the Windows Eventing Collector only: the Windows event logs contain all the messages you need.

l If you are using SQL Server 2008, then it must be SQL 2008 Enterprise Edition.

SQL Server 2008 Standard Edition does not do SQL Auditing.

ODBC Service

The ODBC Service collects database traces stored in a local trace file, which includes important auditing features like logon, security, configuration, and database changes.

For all supported versions of Microsoft SQL Server, you can collect messages through the ODBC Service. Note the following:

l You must configure collection using SQL Server administrator credentials. l You collect database traces stored in a local trace file, which includes important

auditing features like logon, security, configuration, and database changes.

l The ODBC Service is a very stable method of collecting messages.

Windows and Windows Eventing Services

l For SQL Server 2005, 2008, 2012, and 2014, you can collect audit level

messages.

l For SQL Server 2000 or 2005, running on Windows Server 2003, you set up the

Windows Legacy Collector.

File Service

The File Service collects system level messages stored in a local error log file.

Configure ODBC Collection

You must complete these tasks to configure Microsoft SQL Server collection:

l Set up SQL Server Auditing on SQL Server (2008 and later) on Windows Server

(2008 and later)

l Set up the ODBC Service on the Microsoft SQL Server event source l Set up the ODBC Service on Security Analytics

Set up SQL Server Auditing on SQL Server (SQL Server 2008, 2012, and 2014)

To capture Microsoft SQL Server Auditing messages, you must configure both SQL Server and RSA Security Analytics.

To set up SQL Server Auditing on SQL Server:

1. On the SQL Server platform, open SQL Server Management Studio. 2. Log onto the server using administrator credentials.

3. Navigate to Security > Audits and create a new audit.

4. Depending on your system, set the Audit Destination to Application Log or Security Log, and set the values of all other fields with appropriate values for your organization.

Note:If you want to use security logs, you must set up administrative privileges on the SQL Server. To set up the appropriate privileges, follow the instructions from theMicrosoft MSDNpage.

5. Click OK to create the audit.

6. Create Server Audit Specifications and Database Audit Specifications, and point them to the audit you created.

Set up the ODBC Service on the Microsoft SQL Server event source

Important: The ODBC Service only needs to be set up per SQL Server instance, not for every database on the SQL Server.

Configure the SQL Server and any custom events, as well.

Configure the SQL server

To configure the SQL server:

1. Go to RSA Secure Care Online, and from the RSA Security Analytics Device Configurations page, download the appropriate files:

Note:The files must be accessible by the SQL Server host.

l For SQL Server 2000, sqlServerAudit2000.sql script. l For SQL Server 2005, sqlServerAudit2005.sql script. l For SQL Server 2008, sqlServerAudit2008.sql script. l For SQL Server 2012, sqlServerAudit2012.sql script. l For SQL Server 2014, sqlServerAudit2014.sql script. l For SQL Server 2005, 2008, 2012 or 2014 RSA_

MSSQLAuditStoredProcedures.dll.

Create a directory, C:\MyTraceFiles, with enough space to hold the SQL Server trace files, and grant delete permissions to this directory for the account running the SQL server process "SQL Server

(MSSQLSERVER)."

Note:You will need this directory to set up the ODBC type on the RSA Security Analytics.

Configure Custom Events for the SQL Server

To configure custom events for SQL Server 2000:

1.

1. On the SQL Server, start the SQL Server Profile tool. 2. Select File > New > Trace.

3. On the Events tab, select the events to trace. 4. On the Data Columns tab, select all the columns.

Note:The ODBC service works only if you select all the columns. You can also select a filter.

6. Select File > Script Trace > For SQL Server 2000 or 7.0.

7. Save the script file to a temporary location on your computer. You will need to open this file in step 9.

8. Close the SQL Server Profile tool.

9. Open the script file that you created, and follow these steps:

a. Copy the blocks of code marked by -- Set the events that are similar to the following example:

exec sp_trace_setevent @TraceID, 10, 1, @on

Do not include the following:

declare @on bit set @on = 1

b. Copy the blocks of code marked by -- Set the filters that are similar to the following example:

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

Or

set @intfilter = 100

exec sp_trace_setfilter @TraceID, 22, 0, 4, intfilter

Do not include the following:

declare @intfilter int declare @bigintfilter bigint

Open sqlServerAudit2000.sql and make the following changes: a. Find the nic_aud_set_events procedure.

b. Paste the code that you copied for events in step 9 between the following comments, replacing any existing code within the comments:

-- *************

-- *** Custom events should be pasted below this line!!! -- *************

<YOUR CUSTOM EVENTS HERE> -- *************

c. Paste the code that you copied for filters in step 9 between the following comments, replacing any existing code within the comments:

-- *************

-- *** Custom events should be pasted below this line!!! -- *************

<YOUR CUSTOM EVENTS HERE> -- *************

-- *** Custom events should be pasted above this line!!! -- *************

Save the changes to sqlServerAudit2000.sql.

To configure custom events for SQL Server 2005 or later:

1. Open the sqlServerAudit2005.sql ,sqlServerAudit2008.sql,

sqlServerAudit2012.sql or sqlServerAudit2014.sql file. These files include all possible events.

2. To enable or disable events, follow these steps:

Note:For the script to function, you must enable or disable all lines for an event.

a. To enable events, delete - - from the beginning of each exec statement for a given event.

b. To disable events, add - - to the beginning of each exec statement for a given event.

Install the Audit Procedures

Note:You must be the database administrator to install the audit procedures.

To install the audit procedures:

1. The following steps are only for Microsoft SQL Server 2005 and later. If you are using Microsoft SQL Server 2000, proceed to step 2:

a. Create a directory C:\MyDBApp, and place the RSA_ MSSQLAuditStoredProcedures.dll file inside. b. Launch the SQL Server Management Studio.

Note:The CLR is disabled by default in SQL Server 2005 and later. You must enable CLR on a server-wide basis. You only need to enable CLR once for each server.

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure; go

EXEC sp_configure 'clr enabled' , '1' go

reconfigure; go

d. Click Execute.

e. To add a certificate to the database and Grant Load permissions, click New Query, and type:

USE master GO

CREATE CERTIFICATE SQLCLRTestCert FROM EXECUTABLE FILE =

'C:\MyDBApp\RSA_

MSSQLAuditStoredProcedures.dll' CREATE LOGIN SQLCLRTestLogin FROM CERTIFICATE SQLCLRTestCert

GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin

GO

f. Click Execute.

g. To load the .NET Assembly in the SQL server, click New Query, and type:

CREATE ASSEMBLY RSA_ MSSQLAuditStoredProcedures FROM 'C:\MyDBApp\RSA_

MSSQLAuditStoredProcedures.dll' WITH PERMISSION_SET = EXTERNAL_ ACCESS;

GO

2. Run the appropriate script using the SQL Server Query Analyzer (2000) or SQL Server Management Studio (2005, and later) utility against the master database:

Note:These scripts may have changed when you configured custom events.

l For SQL Server 2000, run the sqlServerAudit2000.sql script. l For SQL Server 2005, run the sqlServerAudit2005.sql script. l For SQL Server 2008, run the sqlServerAudit2008.sql script. l For SQL Server 2012, run the sqlServerAudit2012.sql script. l For SQL Server 2014, run the sqlServerAudit2014.sql script.

If you are using SQL Server 2000, you only need to create a user, audit_reader, with sysadmin privileges. If you are using SQL Server 2005 or later, you must create a SQL Server logon. To create a SQL Server logon, follow these steps:

a. Open the SQL Server Management Studio with administrative credentials, and access the Database Engine.

b. To create a new login, follow these steps:

i. From the Object Explorer navigation menu, expand your database server, which is the top item in the navigation pane. ii. Expand Security.

iii. Right-click Logins and select New Login.

iv. From the Select a page navigation menu, select General. v. From the Login name field, type audit_reader.

vi. Select SQL Server authentication. vii. Create and confirm a password.

viii. Ensure that Enforce Password Expiration is not selected. ix. Click OK.

x. Click Security > Login, and right-click audit_reader. xi. Select Properties, and from the Select a page navigation

menu, select User Mapping.

xii. Ensure that Map is selected for the master database. xiii. Click OK.

i. From the Object Explorer navigation menu, right-click your database server, and select Properties.

ii. From the Select a page navigation menu, select Permissions. iii. From the Login or roles section, select audit_reader. iv. From the Explicit permissions section, select the Grant

column for Alter trace and Connect SQL. v. Click OK.

d. To set the database access permission, follow these steps: i. From the Object Explorer navigation menu, expand your

database server.

ii. Expand Databases > System Databases. iii. Right-click master and select Properties.

iv. From the Select a page navigation menu, select Permissions. v. From the Login or roles section, select audit_reader. vi. From the Explicit permissions section, select the Grant

column for Connect and Execute. vii. Click OK.

Set Up ODBC in RSA Security Analytics

To configure ODBC collection in Security Analytics 10.4, perform the following procedures:

I. Ensure the required parser is enabled II. Configure a DSN

III. Add the Event Source Type

IV. Restart the ODBC Collection Service

Ensure the Required Parser is Enabled

If you do not see your parser in the list while performing this procedure, you need to download it in Security Analytics Live.

Ensure that the parser for your event source is enabled:

1. In the Security Analytics menu, select Administration > Services.

2. In the Services grid, select a Log Decoder, and from the Actions menu, choose View > Config.

3. In the Service Parsers Configuration panel, search for your event source, and ensure that the Config Value field for your event source is selected.

Configure a DSN

Configure a DSN (Data Source Name):

1. In the Security Analytics menu, select Administration > Services. 2. In the Services grid, select a Log Collector service.

3. Click under Actions and select View > Config.

4. In the Log Collector Event Sources tab, select ODBC/DSNs from the drop-down menu.

5. The DSNs panel is displayed with the existing DSNs, if any. 6. Click

+

Note:If you need to add a DSN template, seeConfigure DSNsin the Security Analytics User Guide.

7. Choose a DSN Template from the drop down menu and enter a name for the DSN. (You use the name when you set up the ODBC event source type.) 8. Fill in the parameters and click Save.

Database=<Specify the database used by MSSQL>

PortNumber=<Port for MSSQL instance, default is 1433> HostName=<HostIP of MSSQL Database Server>

Driver=/opt/netwitness/odbc/lib/R3sqls26.so

Note:The Driver field refers to the complete path to your ODBC driver.

Add the Event Source Type

In step 6 below, select mssql from the Available Event Source Types dialog.

Add the ODBC Event Source Type:

1. In the Security Analytics menu, select Administration > Services. 2. In the Services grid, select a Log Collector service.

3. Click under Actions and select View > Config.

4. In the Log Collector Event Sources tab, select ODBC/Config from the drop-down menu.

5. Click

+

6. Choose the log collector configuration type for your event source type and click OK.

7. Fill in the parameters and click Save.

10. Enter the DSN you configured during the Configure a DSN procedure. For the other parameters, see ODBC Event Source Configuration Parametersin the SA User Guide.

Restart the ODBC Collection Service

Restart the ODBC collection service:

1. In the Security Analytics menu, select Administration > Services. 2. In the Services grid, select a Log Collector service.

3. Click under Actions and select View > System. 4. Click Collection > ODBC.

l If the available choice is Start, click Start to start ODBC collection. l If the available choices are Stop and Pause, click Stop, wait a few

moments, and then click Start.

Configure Windows Collection

There are two parts to configuring Windows collection: I. Configure WinRM on the Windows Host

II. Configure Security Analytics for Windows Collection.

Configure WinRM on a Windows Host

l useradd: sets up a user account with the necessary permissions.

l RSA_SA_winevent_config.vbs: sets up the WinRM listener

To set up and run the useradd script:

1. Open useradd.vbs for editing.

2. You need to enter your values for the following two parameters:

l User account: in the Name field, enter the name for the RSA user account. l Domain: in the compname parameter, enter your domain name.

Note:For the remainder of this document, we are using example values: rsalog for the user account, and dsnetworking.com for the domain name.

3. On the Windows host, open a Command Prompt, and run useradd:

c:\Program Files\scripts>useradd.vbs

Note:You need to run the script as an administrator.

The script prompts you to open the file. Click Yes to run the script and set up your user.

To run the script to set up the WinRM listener:

1. On the Windows host, open a Command Prompt.

2. Navigate to the folder where the script is stored, and run it as follows:

rsa_SA_winevent_config.vbs http

The script prompts you with a series of information and verification screens: accept them as they appear, in order to have the script succeed.

This completes your set up on the Windows host. Next, you configure Security Analytics.

Configure Security Analytics for Windows Collection

In Security Analytics, you need to configure the Kerberos Realm, and then add the Windows Event Source type.

To configure the Kerberos Realm for Windows collection:

1. In the Security Analytics menu, select Administration > Services.

2. In the Services grid, select a Log Collector, and from the Actions menu, choose View > Config > Event Sources.

3. Select Windows/Kerberos Realm from the drop-down menu.

5. Fill in the parameters, using the guidelines below.

Parameter Details Kerberos

Realm Name

Enter the realm name, in all caps. For example,

DSNETWORKING.COM. Note that the Mappings parameter is automatically filled with variations on the realm name.

KDC Host Name

Enter the name of the Domain Controller. Do not use a fully qualified name here: just the host name for the DC.

Note:Make sure that the log collector is configured as a DNS client for the corporate DNS server. Otherwise, the LC will not know how to find the Kerberos Realm.

Admin Server

(Optional) The name of the Kerberos Administration Server in FQDN format.

6. Click Save to add the Kerberos domain.

Next, continue from the current screen to add a Windows Event Category and type.

To configure the Windows Event Type:

1. Select Windows/Config from the drop-down menu.

3. Fill in the parameters, using the guidelines below.

Parameter Details

Alias Enter a descriptive name.

Authorization Method

Choose Negotiate.

Channel For most event sources that use Windows collection, you want to collect from the Security, System, and Application channels.

User Name Enter the account name for the Windows user account that you set up earlier for communicating with Security Analytics. Note that you need to enter the full account name, which includes the domain. For example, [email protected].

Password Enter the correct password for the user account.

Max Events Per Cycle

(Optional). RSA recommends that you set this value to 0, which collects everything.

Polling Interval

(Optional). For most users, a value of 60 should work well.

4. Click OK to add the source.

The newly added Windows event source is displayed in the Event Categories panel.

5. Select the new event source in the Event Categories panel. The Hosts panel is activated.

6. Click

+

7. Fill in the parameters, using the guidelines below.

Parameter Details

Event Source Address Enter the IP address for the Windows host.

Port Accept the default value, 5985.

Transport Mode Enter http.

Enabled Ensure the box is checked.

Note:In Security Analytics versions prior to 10.4 patch 2, the Windows service had to be running in order for the test connection to work. In later versions, you should be able to successfully test the connection, even if the Windows service is not running.

For more information on any of the previous steps, see the following Help topics in the Security Analytics User Guide:

l Add Windows Event Source in Security Analytics l Configure Kerberos Realm

l Kerberos Realm Configuration Parameters l Windows Event Source Configuration Parameters

Configure File Collection

To configure File collection for Microsoft SQL Server, set up the SFTP agent and configure the Log Collector for file collection.

Set Up the SFTP Agent

To set up the SFTP Agent Collector, visit the appropriate page in theSecurity Analytics (SA) help:

l To set up the SFTP agent on Windows, seeInstall and Update SFTP Agent l To set up the SFTP agent on Linux, seeConfigure SA SFTP Agent shell script

Configure the Log Collector for File Collection

Perform the following steps to configure the Log Collector for File collection.

To configure the Log Collector for file collection:

1. In the Security Analytics menu, select Administration > Services.

2. In the Services grid, select a Log Collector, and from the Actions menu, choose View > Config > Event Sources.

3. Select File/Config from the drop-down menu.

The Event Categories panel displays the File event sources that are configured, if any.

4. In the Event Categories panel toolbar, click

+

5. Select the correct type from the list, and click OK.

The newly added event source type is displayed in the Event Categories panel.

Select mssql from the Available Event Source Types dialog.

6. Select the new type in the Event Categories panel and click

+

7. Add a File Directory name, modify any other parameters that require changes, and click OK.

8. Configure the Security Analytics Upload Directories. After you have added and configured the event source using the Security Analytics GUI, you must

configure the upload directories correctly.

a. Change to the /var/netwitness/logcollector directory. b. Change the owner of the upload directory to the sftp user:

chown sftp /var/netwitness/logcollector/upload

c. Change the group for the upload directory to the sftp user:

chgrp -R sftp /var/netwitness/logcollector/upload

d. Ensure the /upload directory has the correct permissions:

chmod -R 775 /var/netwitness/logcollector/upload

e. Optional: Set up a cron job to run the script at the time intervals that you wish. If you set up a cron job, make sure to run it as that sftp user. 9. Stop and Restart File Collection. After you add a new event source that uses

Copyright © 2015 EMC Corporation. All Rights Reserved.

Trademarks