Creating a SQL Server log channel

When you're dealing with a high volume of log events, a custom SQL log channel can be helpful. By setting one up in the Venafi Configuration Console, you can direct events to an additional database you've created. This way, depending on your notification and policy settings, Venafi Platform will log events to either its own database or your new custom database.

Initial database setup

  • Create Database: Open SQL Server Management Studio and create a new database.

  • Server Requirements: If you're using a separate SQL Server for your custom log database, make sure it meets Venafi's database server requirements. For details, see System requirements for Venafi components.

  • High-Availability (Optional): You can configure the SQL Server for high availability using Microsoft's Always On Availability Groups. For more information, see Always On Availability Groups (SQL Server).

  • Database Naming: Database names must follow standard Venafi requirements.

    The database name cannot contain any of the following characters:

    [ ] ( ) { } \ " ' , $ % * ?

Script Execution

  1. Open script.

    Navigate to [Venafi installation folder]\Database Scripts\MSSQL\Updates\Optional\mssql_log_structure_SP.sql.

  2. Run script.

    Run mssql_log_structure_SP.sql against the new database. This will generate additional stored procedures, tables, and types.

  3. Create Log Channel.

    On the new database, run DAL_LOG_SP_CREATE_LOG_CHANNEL 'table prefix'. For example, exec dbo.DAL_LOG_SP_CREATE_LOG_CHANNEL 'Log3'.

  4. Account Permissions. (Optional)

    If you're using an account other than sa, run the following set of grants listed below:

    • Open [Venafi installation folder]\Database Scripts\MSSQL\Updates\Optional\mssql_log_grant.sql.
    • Follow the instructions in the mssql_log_grant.sql file to grant permissions to an operational database account.

Configuration in Venafi Configuration Console (VCC)

  1. Create SQL Server channel.

    In Venafi Configuration Console, expand the Tools > Logging > Channels nodes, then click SQL Server in the Actions panel, and give the new channel a name.

  2. Configure the channel.

    • Specify the database table to use to store the logs.

    • Decide if you want to automatically delete records after a specified number of days.

      We strongly recommend you enable this option unless you have a specific operational requirement to maintain logs. If you don't purge log files regularly, your database can get very large, and if you log enough events, can even run out of storage space.

    • Choose Custom Connection Data.

    • Enter the database connection information.

Final steps

  1. Create notification rules.

    You'll need to create the notification rules that will be stored to this database, and you will need to point them to your new custom SQL Server channel. For more information see Working with notification rules.

  2. Restart log server.

    In VCC, go to the Product node, and click the Logging Windows service. In the Actions panel, click Restart.