Configure Audit Database for CNS

Prerequisites

Make sure that the following prerequisites are met:

  • SQL Server Management Studio (SSMS) is installed on your machine.

  • A SQL server instance is running and accessible.

  • Your database creation and table creation scripts are prepared based on the templates in this topic.

Create Audit Database on SQL Server

To create an audit database on the SQL server:

  1. Launch SQL Server Management Studio.

  2. In the Connect to Server dialog, enter the following details:

    • Server type: Database Engine

    • Server name: Your SQL Server instance name

    • Authentication: Choose your authentication method (Windows Authentication or SQL Server Authentication)

    • Login: Enter your username (if using SQL Server Authentication)

    • Password: Enter your password (if using SQL Server Authentication)

  3. Click Connect.

  4. Once connected, click New Query in the toolbar to open a new query window for creating the audit log database.

  5. Paste the database creation script into the query window. Use the following template, updating the database name as needed.

    Copy 
    USE [master]
    GO
     
    CREATE DATABASE [audit_log]
    GO

  6. Click Execute or press F5 to execute the script. This creates the audit log database.

  7. Click New Query to open another new query window for creating the audit log table.

  8. Paste the table creation script into the query window. Use the following template, updating the database name as needed.

    Copy 
    USE [audit_log]
    GO
     
    SET ANSI_NULLS ON
    GO
     
    SET QUOTED_IDENTIFIER ON
    GO
     
    CREATE TABLE [dbo].[AuditRecords](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Version] [nvarchar](max) NULL,
        [System] [nvarchar](max) NULL,
        [SystemType] [nvarchar](max) NULL,
        [Date] [datetime2](7) NOT NULL,
        [Time] [datetime2](7) NOT NULL,
        [User] [nvarchar](max) NULL,
        [Connection] [nvarchar](max) NULL,
        [MessageId] [nvarchar](max) NULL,
        [Type] [nvarchar](max) NULL,
     CONSTRAINT [PK_AuditRecords] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
     
     
    CREATE TABLE [dbo].[AuditDetailRecords](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Details] [nvarchar](max) NULL,
        [AuditRecordId] [int] NULL,
     CONSTRAINT [PK_AuditDetailRecords] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
     
    ALTER TABLE [dbo].[AuditDetailRecords]  WITH CHECK ADD  CONSTRAINT [FK_AuditDetailRecords_AuditRecords_AuditRecordId] FOREIGN KEY([AuditRecordId])
    REFERENCES [dbo].[AuditRecords] ([Id])
    GO
     
    ALTER TABLE [dbo].[AuditDetailRecords] CHECK CONSTRAINT [FK_AuditDetailRecords_AuditRecords_AuditRecordId]
    GO

  9. Click Execute or press F5 to execute the script. This creates the audit log table.

Add User with Read/Write Permissions

Once you have created the audit log database and table, create a SQL server login and add a user with read/write permissions in the audit log database:

  1. Launch SQL Server Management Studio, and connect to your SQL server.

  2. In the Object Explorer, expand the server instance.

  3. Right-click on the Security folder, open the New dropdown in the context menu, and select Login... to open the Login - New dialog box.

  4. In the General page of the dialog box that opens, input the following:

    • Login name: enter the login name you want to use.

    • Authentication: Select SQL Server Authentication.

    • Password: enter and confirm the password you want to use.

  5. Click OK to create the login.

  6. In the Object Explorer, expand the Databases folder, select the database where you want to create the user, and expand the Security folder in the selected database.

  7. Right-click on the Users folder, open the New dropdown in the context menu, and select User... to open the User - New dialog box.

  8. In the dialog box that opens, input the following:

    • User name: enter the user name you want to use.

    • Login name: enter the login name you created for the SQL server login.

    • Optional: set the Default schema.

  9. Click OK to create the user.

  10. In the Object Explorer, expand the Databases folder, select the database where you created the user, and expand the Security > Users folder in the selected database.

  11. Right-click on the user you created, and select Properties.

  12. In the Database User Properties dialog box, select the Membership page.

  13. Check the db_datareader and db_datawriter checkboxes to grant read and write permissions to the user.

  14. Click OK to apply the changes.

Add User with Read/Write Permissions Using T-SQL Commands

Alternatively, you can also create the SQL server login and the database user with T-SQL commands:

  1. Create the SQL server login:

    Copy 
    CREATE LOGIN [NewLoginName] WITH PASSWORD = 'YourStrongPassword';

  2. Create the database user:

    Copy 
    USE [YourDatabaseName];
    CREATE USER [NewUserName] FOR LOGIN [NewLoginName];

  3. Grant read/write permissions:

    Copy 
    USE [YourDatabaseName];
    ALTER ROLE db_datareader ADD MEMBER [NewUserName];
    ALTER ROLE db_datawriter ADD MEMBER [NewUserName];

Connect CNS to the Audit Log Database

  1. Log into the CNS web application with adminserver or equivalent credentials.

  2. In the Audit tab, click the Audit logging service configuration button.

  3. In the Configure audit service section of the dialog box, enable the Enable logging service toggle.

  4. In the Configure audit datastore section of the dialog box, disable the Use application datastore toggle.

  5. In the Remote datastore settings section of the dialog box, populate the following fields with the correct values:

    • Database Server IP: the IP address of the SQL server

    • Database Server Port: the SQL server port (default: 1433)

    • Database User: the name of the database user you created

    • Database Password: the password of the database user you created

    • Database Name: the name of the audit logging database you created

  6. Click Save Changes to apply the settings.

  7. Click Test Datastore Connection to verify the settings.

    Note: Make sure to save any changes before testing, as the test uses the latest saved settings.