rudijager Absent Member.
Absent Member.
1646 views

Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi,

I'm creating an audit specification for Microsoft SQL Server 2008R2.

- create audit

- create server audit specification

- create database audit specification

Logging is to application or security event log.

I want to audit create, alter and drop tables, so I'm using this action type: database_object_change_group

According to Microsoft's documentation (http://technet.microsoft.com/en-us/library/cc280663.aspx) this action type setting should log both create, alter and drop. In my environment it only logs create, none of alter and drop actions are logged.

Any advice?

Regards,

Rudi

1 Solution

Accepted Solutions
mat863 Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

This is probably no longer relevant, but you might try using SCHEMA_OBJECT_CHANGE_GROUP.  That is supposed to capture what you're after for any database on the SQL Server instance.  In that way, you don't have to define a database audit spec for each database that exists in an instance.

View solution in original post

0 Likes
11 Replies
mat863 Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

This is probably no longer relevant, but you might try using SCHEMA_OBJECT_CHANGE_GROUP.  That is supposed to capture what you're after for any database on the SQL Server instance.  In that way, you don't have to define a database audit spec for each database that exists in an instance.

View solution in original post

0 Likes
Highlighted
Outstanding Contributor.. Pushpendra_Rathi Outstanding Contributor..
Outstanding Contributor..

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi Rudi,

Just for your information as it might be useful to you.

So, even if you managed to write your SQL audit trail to MS Windows Application or Security logs, the default supported connector (Microsoft SQL Server Audit Windows Event Log – Unified) will not parse 99.99% of SQL audit logs..

I wasted my efforts by configuring MS SQL auditing to write trail to Application/Security logs but connector did not parse the SQL events.

Please try to have a look in connector (Microsoft SQL Server Audit Windows Event Log – Unified) document, go to the last page which shows the mapping only for one event ID 33205 mentioned below.

I opened the ticket with HP ArcSight support and got stupid reply that it support only one event ID for MS SQL in this case. They suggested me to use the connector Microsoft SQL Server Multiple Instance Audit DB

I was really wondering why HP said that they have so called connector to support MS SQL audit trail going to windows application/security logs.

Microsoft SQL Server Audit Application Event Log Mappings

MSSQLSERVER

Event 33205

ArcSight Field

Vendor Field

Destination Host Name

server_instance_name

Destination NT Domain

One of (target_server_principal_name, server_principal_name) from NTDomain

Destination User ID

One of (server_principal_id, target_server_principal_id)

Destination User Name

One of (target_server_principal_name, server_principal_name) from NTUser

Device Action

action_id

Device Custom Number 1

session_id

Device Custom Number 2

database_principal_id

Device Custom Number 3

target_database_principal_id

Device Custom String 1

object_name

Device Custom String 2

Statement

Device Custom String 3

database_name

Device Custom String 4

Source Owner Name (database_principal_name)

Device Custom String 5

Destination Owner Name (one of (target_database_principal_name, database_principal_name))

Device Custom String 6

schema_name

Device Event Class ID

Both (class_type, action_id)

Event Outcome

succeeded

File ID

object_id

File Name

object_name

File Type

class_type

Message

Statement

Source NT Domain

server_principal_name from NTDomain

Source User ID

server_principal_id

Source User Name

server_principal_name from NTUser

Regards

Pushpendra

0 Likes
mallcop Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi guys,

Sorry for popping up into your discussion, but I've noticed that you are auditing SQL databases using SQL Server Audit. I'm also using SQL Server Audit on some databases, but I'm logging to files on a share on a remote server which on which sql server is not installed. So I set up Microsoft SQL Server Multiple Instance Audit DB connector to use an ODBC connection to connect to a sql server and pointed the connector to the share where the files are. The connector starts up but no events from the file are imported. I've tried using GENERAL_AUDIT and C2_AUDIT but none seems to work. I believe it's because the log files are in a different format than GENERAL_AUDIT and C2_AUDIT and if I want to read them I have to execute a SELECT statement from the SQL Management Studion console.


My question is: did you manage to pull the logs from file when using SQL Server Audit with the
Microsoft SQL Server Multiple Instance Audit DB connector(my version 6.0.1.6574)? or I have to configure a flex connector?


Many thanks,

0 Likes
Outstanding Contributor.. Pushpendra_Rathi Outstanding Contributor..
Outstanding Contributor..

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi Raul,

It should work fine.

Can you tell me that what path you gave for these locations?

Trace File Local Folder: ?

Connector Data Folder: ?


And what account are you using in MSSQL server? Windows account or SQL account? What permission did you give?


May be it's good idea to upload your agent.properties file here.


Regards

Pushpendra


0 Likes
mallcop Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi Pushpendra,


I have the following configuration:


- Trace File Local Folder - \\servername\sharename

- Connector Data Folder - \\servername\sharename

Both point to the same share.


I am using a Windows domain account which has "Login" permissions on the database engine and "Connect, Execute, Select and View database" permissions on the "master" database. Basicly I followed the intructions from page 32 to 34 in the Microsoft SQL Multiserver Audit Config Guide chapter Run the Connector with a Standard Domain User Account.

agent.properties looks like this:

agents.maxAgents=1

agents[0].AgentSequenceNumber=0

agents[0].JDBCDriver=sun.jdbc.odbc.JdbcOdbcDriver

agents[0].badsubfolder=bad

agents[0].database=Default

agents[0].databases.count=1

agents[0].databases[0].auditType=GENERAL_AUDIT or C2

agents[0].databases[0].configfolder=sqlserver_audit_db

agents[0].databases[0].datafileFolderForAgent=\\\\servername\\sharename - the sharename contains a "_"  do i have to put a "\" something like aaa\_bbb?

agents[0].databases[0].frequency=5

agents[0].databases[0].startatdate=01/01/1980 00\:00\:00  -do i have to format the start date as in the log files

agents[0].databases[0].startatid=-1

agents[0].databases[0].traceFileLocalFolder=\\\\servername\\sharename

agents[0].databases[0].url=jdbc\:odbc\:OdbcName

agents[0].dbcpcachestatements=false

agents[0].dbcpcheckouttimeout=600

agents[0].dbcpidletimeout=300

agents[0].dbcpmaxcheckout=-1

agents[0].dbcpmaxconn=5

agents[0].dbcpreap=300

agents[0].dbcprowprefetch=-1

agents[0].delay=-1

agents[0].destination.count=1

...

and so on

The connector starts-up but it never receives events from the log files. I also tried building a flex connector(id and time based), but it's the same story.

Thanks

0 Likes
Outstanding Contributor.. Pushpendra_Rathi Outstanding Contributor..
Outstanding Contributor..

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi

There r so many things which r not there in guide..

Please attach ur full agent.properties file

~ Kind Regards

~ Pushpendra Rathi

(Sent from short keyboard)

0 Likes
Outstanding Contributor.. Pushpendra_Rathi Outstanding Contributor..
Outstanding Contributor..

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi Raul

You have sensitized the agent.properties file too much so it's not useful for me.I need the exact agent.properties file, may be you can FIND/REPLACE your server IPs with some other IP/HOST/DB-NAME etc.

NOTE : Please do not edit or type anything in the file manually.

Also, wondering why are you using same location for below paths? One of this should be the trace files path and other should be the default path of MSSQL data directory.

- Trace File Local Folder - \\servername\sharename

- Connector Data Folder - \\servername\sharename

You do not have to build flex conn for this as this connector works perfectly.

Regards

Pushpendra

0 Likes
ambreen1 Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi,

i am also looking forward to the answers....

Regards,

Ambreen

0 Likes
mallcop Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

About the values that I entered in Trace File Local Folder and Connector Data Folder. I have the following set-up:

- the connector is installed on a server where sql is not installed

- audit files go to a server where sql is not installed

- odbc is configured,on the server where the connector is installed, to connect to the master database on test sql server not the one the audited database is at

So I thought I should point Trace File Local Folder and Connector Data Folder to the same share. Please correct me if my setup is wrong.


Agent.properties file:


agents.maxAgents=1

agents[0].AgentSequenceNumber=0

agents[0].JDBCDriver=sun.jdbc.odbc.JdbcOdbcDriver

agents[0].badsubfolder=bad

agents[0].database=Default

agents[0].databases.count=1

agents[0].databases[0].auditType=GENERAL_AUDIT

agents[0].databases[0].configfolder=sqlserver_audit_db

agents[0].databases[0].datafileFolderForAgent=\\\\auditsql\\audit

agents[0].databases[0].frequency=5

agents[0].databases[0].startatdate=01/01/1980 00\:00\:00

agents[0].databases[0].startatid=-1

agents[0].databases[0].traceFileLocalFolder=\\\\auditsql\\audit

agents[0].databases[0].url=jdbc\:odbc\:AuditSQL

agents[0].dbcpcachestatements=false

agents[0].dbcpcheckouttimeout=600

agents[0].dbcpidletimeout=300

agents[0].dbcpmaxcheckout=-1

agents[0].dbcpmaxconn=5

agents[0].dbcpreap=300

agents[0].dbcprowprefetch=-1

agents[0].delay=-1

agents[0].destination.count=1

agents[0].destination[0].agentid=3Dt2IHz0BABDwg-XRM7JjmA\=\=

agents[0].destination[0].failover.count=0

agents[0].destination[0].params=<?xml version\="1.0" encoding\="UTF-8"?>\n<ParameterValues>\n    <Parameter Name\="port" Value\="8443"/>\n    <Parameter Name\="host" Value\="arcsight"/>\n    <Parameter Name\="aupmaster" Value\="true"/>\n    <Parameter Name\="filterevents" Value\="false"/>\n    <Parameter Name\="fipsciphers" Value\="fipsDefault"/>\n</ParameterValues>\n

agents[0].destination[0].type=http

agents[0].deviceconnectionalertinterval=60000

agents[0].enabled=true

agents[0].entityid=GcUGIT0BABCAAtuRQaDAew\=\=

agents[0].fcp.version=0

agents[0].id=3Dt2IHz0BABDwg-XRM7JjmA\=\=

agents[0].initretrysleeptime=60000

agents[0].internalevent.filecount.duration=-1

agents[0].internalevent.filecount.enable=false

agents[0].internalevent.filecount.minfilecount=-1

agents[0].internalevent.filecount.timer.delay=60

agents[0].internalevent.fileend.enable=true

agents[0].internalevent.filestart.enable=true

agents[0].jdbcquerytimeout=-1

agents[0].jdbctimeout=240000

agents[0].loopingenabled=false

agents[0].maxretries=10

agents[0].mode=PersistFile

agents[0].modeoptions=processed

agents[0].passwordchangeingcharactersets=UPPERCASE\=ABCDEFGHIJKLMNOPQRSTUVWXYZ,LOWERCASE\=abcdefghijklmnopqrstuvwxyz,NUMBER\=01234567890,SPECIAL\=+-\!@\#$%&*()

agents[0].passwordchangingcharactersetdelimiter=,

agents[0].passwordchangingenabled=false

agents[0].passwordchanginginterval=86400

agents[0].passwordchanginglength=16

agents[0].passwordchangingtemplate=UPPERCASE,NUMBER,SPECIAL,UPPERCASE|LOWERCASE|NUMBER,UPPERCASE|LOWERCASE|NUMBER|SPECIAL

agents[0].persistenceinterval=0

agents[0].preservedstatecount=10

agents[0].preservedstateinterval=30000

agents[0].preservestate=false

agents[0].retryinterval=1000

agents[0].rotationtimeout=30000

agents[0].sleeptime=5000

agents[0].type=multisqlserver_audit_db

agents[0].useconnectionpool=true

remote.management.second.listener.port=10055

remote.management.ssl.organizational.unit=hL0JFz0BABCAAVEQ6uLadw

0 Likes
Outstanding Contributor.. Pushpendra_Rathi Outstanding Contributor..
Outstanding Contributor..

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

Hi Raul

Can you please give me the output of the following command by running on the MSSQL query analyser which you are trying to monitor?

SELECT * FROM :: fn_trace_getinfo(default)


If it fails then try to try it on master database by following:


use master

SELECT * FROM :: fn_trace_getinfo(default)



Regards

Pushpendra Rathi


0 Likes
olin9 Absent Member.
Absent Member.

Re: Microsoft SQL Server 2008R2 Auditing

Jump to solution

The connector should be setup as follows.



- Trace File Local Folder - The path the SQL server would use to get to the folder. A share on another server = \\servername\sharename. If on the SQL server then = Drive:\path (ie c:\trace)

- Connector Data Folder - \\servername\sharename


Hope this helps.


Rob

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.