D Audit Vault SQL Server Database (AVMSSQLDB) Reference

Audit Vault SQL Server Database (AVMSSQLDB) is a command-line utility that provides the ability to configure (add, alter, and drop) SQL Server audit sources and SQL Server collectors, verify source compatibility with the collectors, and set up SQL Server Database audit sources for audit data collection by establishing the connection to the source through the collector.

Note:

Be sure to set the LANG environment variable to the locale category for native language of choice when using the AVMSSQLDB command-line utility in the Audit Vault Server shell and in the Audit Vault Collection Agent shell. This ensures the locale language specified appears as expected in all translated information. The NLS_LANG environment variable is Oracle specific and while effective with the AVORCLDB command-line utility has no effect on the AVMSSQLDB command-line utility; while the LANG environment variable is the standard way of setting the locale category for native language.

Table D-1 describes the AVMSSQLDB commands and where each is used, whether on the Audit Vault Server, on the Audit Vault Collection Agent, or in both places.

Table D-1 AVMSSQLDB Commands

Command	Where Used?	Description
add_collector	Server	Adds a collector to Audit Vault
add_source	Server	Registers an audit source with Audit Vault
alter_collector	Server	Alters the attributes of a collector
alter_source	Server	Alters the attributes of a source
drop_collector	Server	Drops a collector from Audit Vault
drop_source	Server	Drops a source from Audit Vault
-help	Both	Displays Help for the AVMSSQLDB commands
setup	Collection Agent	Adds the source user credentials to the wallet, creates a database alias in the wallet for the source user, and verifies the connection to the source using the wallet
verify	Both	Verifies that the source is compatible with the collectors

avmssqldb

The AVMSSQLDB command-line utility.

Syntax

avmssqldb <command> -help

avmssqldb <command> [<options>] <arguments>

Arguments

Argument	Description
`<command>`	One of the following commands: `add_source`, `alter_source`, `drop_source`, `add_collector`, `alter_collector`, `drop_collector`, or `verify`
`[<options>]`	The optional AVMSSQLDB options
`<arguments>`	One or more of the AVMSSQLDB command arguments
`-help`	Displays Help for the AVMSSQLDB commands

Usage Notes

Issuing an AVMSSQLDB command generates the following log file: $ORACLE_HOME/av/log/mssqldb-%g.log. The %g is a generation number that starts from 0 (zero) and increases once the file size reaches the 100 MB limit.
The AVMSSQLDB command can be issued any number of times. The AVMSSQLDB command checks to see if a step has already been completed, and returns a warning in each such case, then skips that step and continues until it is completed.

Example

The following output is from the avmssqldb command executed in the Audit Vault Server home shell.

$ avmssqldb -help

  MSSQL DB Setup for Audit Vault Server
  -------------------------------------
 
  Usage :
    avmssqldb help
    avmssqldb <command> -help
    avmssqldb <command> <arguments>
 
Source setup commands
    verify
          -src <host:port>
 
    add_source
          -src <host:port> 
          -srcname <srcname> [-desc <desc>]
 
    alter_source
          -srcname <sourcename> [attrname=value]+ 
 
    drop_source
          -srcname <srcname>
 
Collector setup commands
 
    add_collector
          -srcname <srcname> -agentname <agentname>
          [-collname <collname>] [-desc <desc>] 
 
    alter_collector
          -srcname <srcname> -collname <collname> [attrname=value]+
 
    drop_collector
          -srcname <srcname> -collname <collname>

The following output is from the avmssqldb command executed in the Audit Vault Collection Agent home shell.

$ avmsqldb -help

  MSSQL DB Setup for Audit Vault Agent
  ------------------------------------
 
  Usage :
    avmssqldb help
    avmssqldb <command> -help
    avmssqldb <command> <arguments>
 
Agent Commands
 
    verify
          -src <host:port> 
 
    setup 
          -srcname <srcname>

add_collector

Adds a collector for the given source to Audit Vault. The source is verified for requirements of the collector. This command is run on the Audit Vault Server.

Syntax

avmssqldb add_collector -srcname <srcname> -agentname <agentname>
         [-collname <collname>] [-desc <desc>]

Arguments

Argument	Description
`-srcname <srcname>`	The source name for which the collector is to be added
`-agentname <agentname>`	Collection agent name
`[-collname <collname>]`	The collector name. This argument is optional. If this argument is not specified, MSSQLCollector will be used.
`[-desc <desc>]`	A brief description of the collector. This argument is optional.

Usage Notes

Run any collector-specific preparation scripts before you execute the AVMSSQLDB add_collector command.

Example

The following example shows how to add the MS SQL collector to Oracle Audit Vault.

avmssqldb add_collector -srcname mssqldb4 -agentname agent1 
Enter a username :<source user name>
Enter a password : *******
***** Collector Added Successfully*****

add_source

Registers an audit source with Audit Vault for audit data consolidation. This command is run on the Audit Vault Server.

Syntax

avmssqldb add_source -src <host:port> -srcname <srcname> [-desc <desc>]

Arguments

Argument	Description
`-src <host:port>`	Source database connection information: host name and port number, separated by a colon
`-srcname <srcname>`	Source name.
`[-desc <desc>]`	Optional description of the source

Usage Notes

When prompted enter the credentials for the source user name and password. The user name specified for the source user must exist on the source database. See the example.

Example

The following example shows how to register a source with Oracle Audit Vault.

avmssqldb add_source -src mssqlerver:4523 -srcname mssqldb4 -desc 'HR Database'
Enter a username :<source user name>
Enter a password : *******
***** Source Verified *****
***** Source Added Successfully *****

alter_collector

Modifies the attributes of a collector. This command is run on the Audit Vault Server.

Syntax

avmssqldb alter_collector -srcname <srcname> -collname <collname> 
      [<attrname>=<attrvalue>...<attrname>=<attrvalue>]

Arguments

Argument	Description
`-srcname <srcname>`	Specify the source (by source name) to which this collector belongs.
`-collname <collname>`	Specify the collector (by collector name) to be modified.
`[<attrname>=<attrvalue>]`	Specify the pair (attribute name, new attribute value) for mutable collector property and attributes for this collector type. This argument is optional. Separate multiple pairs by a space on the command line.

Usage Notes

You can modify the collector DESCRIPTION property and one or more attributes at a time. Table D-2 lists the collector attributes (parameters), whether the parameter is mutable, the default value, and a brief description of the attribute.

Table D-2 MSSQLDB Collector Attributes

Parameter	Mutable	Default Value	Description
DESCRIPTION	Yes	NULL	The description for this collector
dbconnection	No	1	Number of connections to the database.
AUDIT_C2_FLAG	Yes	1	Whether C2 logs can be collected by the MSSQLDB collector or not. Values can be 0 or 1.
AUDIT_SERVERSIDE_TRACES_FLAG	Yes	1	Whether server side trace logs can be collected by the MSSQLDB collector or not. Values can be 0 or 1.
AUDIT_EVENT_LOG_FLAG	Yes	1	Whether events logs can be collected by the MSSQLDB collector or not. Values can be 0 or 1.
C2_TRACE_FILEPATH	Yes	Null	The C2 trace file path. See the usage notes.
SERVERSIDE_TRACE_FILPATH	Yes	Null	The value for server-side trace file path. See the usage notes
DELAY_TIME	Yes	20000	The delay time (in milliseconds) of the collector.
NO_OF_RECORDS	Yes	1000	The maximum number of records to be fetched by the collector. This attribute is mutable.

For SQL Server 2000 source databases only, when the AUDIT_SERVERSIDE_TRACES_FLAG attribute is set to 1 or on, the trace file (.trc) audit trail is not released to the collector until either the file reaches its maximum file size and another trace file is created, or the source database is shutdown and started up again.
For the C2_TRACE_FILEPATH and the SERVERSIDE_TRACE_FILPATH parameters, the value for the path can be of the form Drive:\Directory....\File Prefix*.

Example

The following example shows how to alter the NO_OF_RECORDS attribute and the collector description for the MSSQLCollector collector in Audit Vault:

avmssqldb alter_collector -srcname mssqldb4 -collname MSSQLCollector NO_OF_RECORDS=1500 DESCRIPTION="MSSQLDB collector 45" SERVER_SIDE_FILPATH="c:\SQLAuditFile*
***** Collector Altered Successfully *****

alter_source

Modifies the attributes of the source. This command is run on the Audit Vault Server.

Syntax

avmssqldb alter_source -srcname <sourcename> 
          [<attrname>=<attrvalue>...<attrname>=<attrvalue>]

Arguments

Argument	Description
`-srcname` `<sourcename>`	Specify the source (by source name) to be modified.
`[<attrname>=<attrvalue>]`	Specify the pair (attribute name, new attribute value) for mutable source properties and attributes for this source type. This argument is optional. Separate multiple pairs by a space on the command line.

Usage Notes

Table D-3 lists the source attributes, a brief description of the attribute, whether the attribute is mutable, and the default value. You can modify one or more source attributes at a time.

Table D-3 Source Attributes

Attribute	Description	Mutable	Default Value
SOURCETYPE	The source type name for this source. The default name is MSSQLDB	No	NULL
NAME	The name for this source	No	NULL
HOST	The source host name	No	NULL
HOSTIP	The source host IP address	No	NULL
VERSION	The source version	Yes	NULL
DESCRIPTION	The description for this source	Yes	NULL
PORT	A new port number for this system where the source audit data resides	Yes	None

Example

The following example shows how to alter the DESCRIPTION attribute for the source named mssqldb4 in Oracle Audit Vault:

avmssqldb alter_source -srcname mssqldb4 DESCRIPTION="HR Database" 
***** Source Altered Successfully *****

drop_collector

Drops a collector from Oracle Audit Vault. This command is run from the Audit Vault Server.

Syntax

avmssqldb drop_collector -srcname <srcname> -collname <collname>

Arguments

Argument	Description
`-srcname <srcname>`	Specify the name of the source to which the collector (specified in the `-collname` argument) belongs.
`-collname <collname>`	Specify the collector (by collector name) to be dropped from Oracle Audit Vault.

Usage Notes

The drop_collector command will not delete the collector from Oracle Audit Vault; it actually disables the collector. The user can neither add the same collector name again nor enable the old name.

Example

The following example shows how to drop the collector named 'MSSQLCollector' from Oracle Audit Vault:

avmssqldb drop_collector -srcname mssqldb4 -collname MSSQLCollector
***** Collector Dropped Successfully *****

drop_source

Drops a source from Oracle Audit Vault. This command is run on the Audit Vault Server.

Syntax

avmssqldb drop_source -srcname <srcname>

Arguments

Argument	Description
`-srcname <srcname>`	Specify the source (by source name) to be dropped from Oracle Audit Vault.

Usage Notes

The drop_source command does not delete the source from Oracle Audit Vault; it disables the source. The user can neither add the same source name again nor enable the old source. Audit data from this source is no longer collected once the source has been dropped, but the information of this source is maintained in Oracle Audit Vault with a status as dropped (inactive) for future reporting purposes.
A source cannot be dropped or deleted if there are any active collectors for this source. All collectors must be inactive (dropped) to successfully drop a source from Oracle Audit Vault.

Example

The following example shows how to drop the source named mssqldb4 from Oracle Audit Vault:

avmssqldb drop_source -srcname mssqldb4
***** Drop Source Successfully *****

-help

Displays Help for the AVMSSQLDB commands. This command is run on both the Audit Vault Server and the Audit Vault Collection Agent.

Syntax

avmssqldb -help

avmssqldb <command> -help

Arguments

Argument	Description
`<command>`	The name of an AVMSSQLDB command for which you want Help to appear

Usage Notes

None

Example

The following example shows how to display general AVMSSQLDB utility Help in Audit Vault:

avmssqldb -help

The following example shows how to display specific AVMSSQLDB Help for the add_source command in the Audit Vault Server home shell.

$ avmssqldb add_source -help
  avmssqldb add_source command
 
    add_source
          -src <host:port>
          -srcname <srcname> [-desc <desc>] 
 
  Purpose: The source is added to Audit Vault.
 
  Arguments:
       -src        : Source DB connection information to coolect audit data.
       -srcname    : Name of a source
       -desc       : Optional description of the source
 
  Examples:
     avmssqldb add_source -src 10.105.118.91:1433 
        -desc 'source for admin databases' -srcname mssource

setup

Adds the source user credentials to the wallet, creates a database alias in the wallet for the source user, and verifies the connection to the source using the wallet. This command is run on the Audit Vault Collection Agent. This command can also be used to change the source user credentials in the wallet when the credentials are changed on the source.

Syntax

avmssqldb setup -srcname <srcname>

Arguments

Argument	Description
`-srcname <srcname>`	The name of the source database

Usage Notes

When prompted enter the credentials for the source user name and password. The user name specified for the source user must exist on the source database. See the example.
The credentials of the source user are added to the wallet.
If you happen to enter an incorrect user name or password or both when issuing the setup command and receive an error message that the verification of the credentials to make the connection to the source database using the wallet was not successful, reissue the setup command again using the correct credentials.

Example

The following example sets up the MSSQLDB collector.

avmssqldb setup -srcname mssqldb4
Enter a username :<source user name>
Enter a password : *******
***** Credentials Successfully added *****

verify

Verifies that the source is compatible for setting up the specified collector. This command can be run on both the Audit Vault Server and the Audit Vault Collection Agent.

Syntax

avmssqldb verify -src <host:port>

Arguments

Argument	Description
`-src <host:port>`	Source database connection information: host name and port number, separated by a colon

Usage Notes

When prompted enter the credentials for the source user name and password. The user name specified for the source user must exist on the source database. See the example.
The verify command checks the following:
- Whether the version of the database is supported: SQL Server 2000 or SQL Server 2005
- Whether the source user has the required privileges in the source database that is to be registered with Audit Vault
- Whether auditing (C2 auditing and server-side trace auditing) is enabled or not in the source database

Example

The following example verifies that the source is compatible with the MSSQLDB, collector on Windows.

avmssqldb verify -src mssqlserver:4523
Enter a username :<source user name>
Enter a password : *******
***** Source Verified *****