Configuring the ODBC audit message database method

To manually set up an ODBC audit message database, you must first set up an ODBC database. For more information about the ODBC databases supported by OneSpan Authentication Server, see Supported database features.

Manually configuring an ODBC audit message database includes the following steps:

1. Create/modify the database tables with the required auditing database schema.
2. Apply the required permissions for OneSpan Authentication Server and Audit Viewer.
3. Create a data source name (DSN) for the ODBC audit message database.
4. Configure OneSpan Authentication Server to use the ODBC audit message database.
5. Configure Audit Viewer to use the ODBC audit message database.

If a database other than the embedded MariaDB is used with OneSpan Authentication Server for auditing or you did not select database and connection encryption during installation, your organization will need to ensure that GDPR is met by encrypting the database and connections to it, if database auditing is used.

For more information about GDPR, refer to the OneSpan Authentication Server General Data Protection Regulation Compliance Guide.

Applying the auditing database schema

Auditing to an ODBC database requires the following tables:

• vdsAuditMsg. Contains the basic audit messages, including mandatory audit message fields. It contains one record per audit message generated, with additional information stored in the vdsAuditMsgField table.
• vdsAuditMsgField. Contains extra (non-mandatory) audit message fields that may be included in an audit message. It may contain several records for a single audit message.

The audit tables can be created either manually or with the ODBC database command-line utility (dpdbadmin) via the following command:

dpdbadmin addschema ‑dsn dsn ‑noserver

For more information about dpdbadmin, see dpdbadmin addschema. For more information about the auditing database table schema, refer to the OneSpan Authentication Server Administrator Reference, Section "ODBC schema".

Applying the required ODBC audit database permissions

After setting up the required auditing tables, create at least one database account with the permissions that are required for OneSpan Authentication Server and Audit Viewer (see Table: Required database permissions for the ODBC audit database).

Creating a data source name (DSN) for the ODBC audit database

Create a data source name (DSN) for the ODBC audit database on the computer where OneSpan Authentication Server is installed. If Audit Viewer is installed on a different computer, create a DSN for the audit database on that machine as well.

Configuring OneSpan Authentication Server and Audit Viewer to use the ODBC audit database

Once the ODBC audit database is properly configured with the required account permissions and the DSN has been created, configure OneSpan Authentication Server to use the database.

To configure OneSpan Authentication Server to use the ODBC Audit Database method

1. Start the Configuration Utility.
2. Click the Auditing icon.
3. Click Add.
4. Select ODBC Database from the list box.

5. Click OK.

   The Add ODBC Audit Method dialog appears.

   1. Enter a name in the Display Name field. This name will only be used for display purposes.

      If this audit method must succeed, select the Reject audit message if this method fails box. An error will be returned by OneSpan Authentication Server if an audit message cannot be written with this method.

   2. If required, select the Record audit message if no other audit method has recorded it box.

   3. Select one or more audit message types to be logged by this plug-in:

      • Error
      • Warning
      • Information
      • Success
      • Failure
6. Enter the DSN for the database.
7. If required, enter the user name and password of the database account to be used by OneSpan Authentication Server.
8. Click OK.
9. Click Apply.

You can also edit the settings for DSN, user name, and password in the ODBC Audit Settings tab in the Reporting and Audit scenarios and the Auditing section of the OneSpan Authentication Server Configuration Utility.

If your organization is impacted by the General Data Protection Regulation (GDPR), note that for being GDPR-compliant, you must ensure that the ODBC connection is secured with SSL.

For more information about GDPR, refer to the OneSpan Authentication Server General Data Protection Regulation Compliance Guide.

After configuring OneSpan Authentication Server to use the ODBC audit database, configure Audit Viewer as required.

To configure Audit Viewer to use the ODBC audit database

1. Start Audit Viewer.
2. Select to File > New Audit Source -> ODBC Database.
3. Enter a display name to be used for the database within Audit Viewer.
4. Enter the Data Source Name for the database.
5. Enter the user ID and password of a database administrator account.
6. Select the Store User ID and Password box to save login details in Audit Viewer.
7. Click OK.

Using audit database table partitions

If you use an ODBC audit message database, you can use table partitioning to improve audit performance.

Instead of having all audit data in one big table, it is split up into smaller subsets (partitions). Each partition contains the data for one day. This can improve database performance for queries and delete operations.

If you want to use partitioning for audit tables, consider the following requirements and limitations:

• Enabling partitioning can take some time to complete if you already have a lot of audit data. We recommend to do this during or right after a fresh installation, after audit data has been cleared and optimized, or during a database maintenance window to ensure sufficient downtime.
• You cannot use partitioning and secure auditing at the same time. If you choose to use secure auditing, the OneSpan Authentication Server Installation Wizard will not give you the choice to enable audit table partitioning (for MariaDB).

MariaDB

You can enable partitioning during initial configuration via the OneSpan Authentication Server Installation Wizard. Otherwise, you can use the ODBC Database Command-Line Utility (dpdbadmin) to enable and disable partitioning at any time (see dpdbadmin partitionaudittables).

Microsoft SQL Server

You can set up table partitioning for your audit message database on Microsoft SQL Server with the SQL Server database tools. This section provides a basic overview for such a setup. For more information, refer to the SQL Server documentation about table partitioning, available at https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16 (last accessed on December 12, 2024).

The data of partitioned tables and indexes is divided into units that may be spread across more than one filegroup in a database or stored in a single filegroup. Data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table is treated as a single logical entity when queries or updates are performed on the data.

To create a new table with partitioning with Microsoft SQL Server (Overview)

1. (OPTIONAL) Create one or more filegroups and corresponding data files that will hold the partitions specified by the partition scheme.

   The main benefit of placing partitions into multiple filegroups is that you can independently perform backup and restore operations on filegroups. If this isn't required, you can assign all partitions to a single filegroup, either an existing filegroup, such as PRIMARY, or a new one with related data files.

2. Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column. You can use a single partition function to partition multiple objects.
3. Create a partition schema that maps the partitions of a partitioned table or index to one filegroup or to multiple filegroups. You can use a single partition scheme to partition multiple objects.
4. Create or alter a table or index and specify the partition scheme as the storage location, along with the column that will serve as the partitioning column.

To set up audit table partitioning in Microsoft SQL Server 2022

1. Start Microsoft SQL Server Management Studio 19.
2. Connect to the target database, e.g. Identikey Server.

3. Select the table that is to be partitioned, i.e. vdsAuditMsg, and select Storage > Create Partition from the context menu.

   This opens the Create Partition wizard.

4. On the Select a Partitioning Column page, select a partitioning column on which you want to partition your table in the Available partitioning columns grid, i.e. vdsTimeStamp.
5. On the Select a Partition Function page, select New partition function and type a name for the function, e.g. pfAuditDate.
6. On the Select a Partition Scheme page, select New partition scheme and type a name for the partition scheme, e.g. psAuditDate.

7. On the Map Partitions page, map your partitions to filegroups and specify the range values:

   1. Specify the boundary range type, that means how boundary values are put into the resulting partitions:

      • Left boundary. Include the highest bounding value in each partition.
      • Right boundary. Include the lowest bounding value in each partition.
   2. Use the Select filegroups and specify boundary values grid to set up the filegroups into which you want to partition your data. Click Set boundaries to select the start and end date for the boundaries.

8. On the Select an Output Option page, specify how to complete the modifications to your partitions:

   • Create script. This allows you to create a SQL script that will create the partitions based on your input and that you can run manually at any time.
   • Run immediately. This option will create the partitions based on your input when you complete the wizard.
   • Schedule. This option allows you to create a scheduled job that will create the partitions based on your input at a specified time.
9. On the Review Summary page, verify your setup. Click Finish to apply your changes.

The following is a sample SQL script that creates four filegroups, a partitioning function with three boundary values, a corresponding partitioning scheme, and creates the partition based on the vdsTimeStamp column.

--
-- Create filegroups and the corresponding data files
--
ALTER DATABASE [Identikey Server]
ADD FILEGROUP FILEGROUP_NAME_1;

ALTER DATABASE [Identikey Server]
ADD FILEGROUP FILEGROUP_NAME_2;

ALTER DATABASE [Identikey Server]
ADD FILEGROUP FILEGROUP_NAME_3;

ALTER DATABASE [Identikey Server]
ADD FILEGROUP FILEGROUP_NAME_4;

ALTER DATABASE [Identikey Server]
ADD FILE
(
  NAME = pfg1,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\pfg1.ndf',
  SIZE = 5MB,
  FILEGROWTH = 5MB
)
TO FILEGROUP FILEGROUP_NAME_1;

ALTER DATABASE [Identikey Server]
ADD FILE
(
  NAME = pfg2,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\pfg2.ndf',
  SIZE = 5MB,
  FILEGROWTH = 5MB
)
TO FILEGROUP FILEGROUP_NAME_2;

ALTER DATABASE [Identikey Server]
ADD FILE
(
  NAME = pfg3,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\pfg3.ndf',
  SIZE = 5MB,
  FILEGROWTH = 5MB
)
TO FILEGROUP FILEGROUP_NAME_3;

ALTER DATABASE [Identikey Server]
ADD FILE
(
  NAME = pfg4,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\pfg4.ndf',
  SIZE = 5MB,
  FILEGROWTH = 5MB
)
TO FILEGROUP FILEGROUP_NAME_4;

--
-- Create partitioning function with three boundary values using
-- the DATETIME type. Three boundary values will result in a table
-- with four partitions.
--
CREATE PARTITION FUNCTION pfAuditDate (DATETIME)
  AS RANGE RIGHT FOR VALUES ('DateTime1', 'DateTime2', 'DateTime3');

--
-- Create a partitioning scheme
-- Assigns all partitions in the pfAuditDate partition function
-- to the appropriate filegroup
--
CREATE PARTITION SCHEME psAuditDate
  AS PARTITION pfAuditDate
  TO (FILEGROUP_NAME_1, FILEGROUP_NAME_2, FILEGROUP_NAME_3, FILEGROUP_NAME_4);

--
-- Create a partitioned table based on the 'vdsTimeStamp' column
--
USE [Identikey Server]
GO

BEGIN TRANSACTION

ALTER TABLE [dbo].[vdsAuditMsgField]
  DROP CONSTRAINT [vdsauditmsgfld_timestamp_fk];

ALTER TABLE [dbo].[vdsAuditMsg]
  DROP CONSTRAINT [pk_vdsAuditMsg] WITH ( ONLINE = OFF );

SET ANSI_PADDING ON;

ALTER TABLE [dbo].[vdsAuditMsg] ADD CONSTRAINT [pk_vdsAuditMsg] PRIMARY KEY CLUSTERED
(
  [vdsTimeStamp] ASC,
  [vdsAMID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
  ON [psAuditDate]([vdsTimeStamp]);

ALTER TABLE [dbo].[vdsAuditMsgField] WITH CHECK
  ADD CONSTRAINT [vdsauditmsgfld_timestamp_fk]
  FOREIGN KEY([vdsTimeStamp], [vdsAMID])
  REFERENCES [dbo].[vdsAuditMsg] ([vdsTimeStamp], [vdsAMID])
  ON DELETE CASCADE;

ALTER TABLE [dbo].[vdsAuditMsgField]
  CHECK CONSTRAINT [vdsauditmsgfld_timestamp_fk];

COMMIT TRANSACTION