Creating a custom SQL log channel

To manage a large volume of log events, you can create an additional database. Then, from Trust Protection Platform, you can create a custom log SQL channel. The custom SQL log channel provides connectivity and directs the events to the new database. Based on notification and policy settings, Trust Protection Platform logs events to either the Trust Protection Platform database or the new database.

To create a custom SQL log channel

  1. From SQL Server Management Studio, create a new database.

    • If you are using a separate SQL Server for the custom log database, be sure the SQL Server meets the database server requirements. For more information, see System requirements for Venafi components.
    • (Optional) You can configure the SQL Server to run Microsoft SQL high-availability. For more information, see Always On Availability Groups (SQL Server).

    • The database name cannot contain any of the following characters:

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

  2. Open [Venafi installation folder]\Database Scripts\MSSQL\Updates\Optional\mssql_log_structure_SP.sql.

  3. Execute mssql_log_structure_SP.sql against the new database.

    The stored procedure generates additional stored procedures, tables, and types.

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

  5. (Optional) If you want the custom SQL log channel to use an account, other than sa, execute 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.

  6. From Policy Tree, navigate to the Logging tree and create a new MSSQL channel for the new log database. Use the following settings and for more information, see Creating Channel objects.

    • Select Use Custom Connection.
    • Use an account and credentials that you set up in the new database.
    • Specify the SQL server host name, port, and database name.

    • In the Table Prefix box, type the same table prefix that you specified from the DAL_LOG_SP_CREATE_LOG_CHANNEL stored procedure.

    • If you want the custom SQL channel to use Windows Authentication, be sure to confirm that all Trust Protection Platform servers, Log servers, and Policy Tree also use Windows authentication.

      Custom log channel

  7. Create a new notification and point it to the new custom SQL channel. For more information, see Creating notification rule objects.
  8. Restart the Log server.