Configuring SQL Server destinations

Before your connections can successfully send data to a destination, your Tanium Cloud instance, CMP network egress allow list, and network allow list must be configured. Note the following:

For more information, see Tanium Cloud Deployment Guide: Network egress. For assistance, contact Tanium Support.

You can use SQL Server destinations to store historical results for analysis and integration with third-party systems. You can forward the results from saved questions and other data sources to a database table.

System requirements

  • Microsoft SQL Server 2008 or later, Microsoft SQL Server 2008 Express or later
  • Database application server must be accessible from the Tanium Module Server
  • User credentials with db_ddladmin and db_datawriter role membership to an account on the Microsoft SQL Server that can insert data, create tables (optional), and run stored procedures (optional)

Set up the database

Decide whether to use an existing database or create a new database.

  1. Get the name of the database that you want to use for Connect. When you set up the destination, you can have Connect create a new table for your data or use an existing table.
  2. The user account that Connect uses for the database must have log in permissions to that database and the appropriate permissions to tables and stored procedures (optional) within that database.

    Both SQL Server Authentication and Windows Authentication are supported. Set up the following privileges:

    • For each user, create a User Mapping for the database.
    • For each database, grant the CREATE TABLE permission.
    • For each schema, grant the ALTER and INSERT permissions.
    • (Optional) For stored procedures, grant at least the EXECUTE permission.
  3. (Optional) Create stored procedures.
    You can create procedures that run before the first database table row is inserted or after the last row is inserted. Before you create the connection, these procedures and any associated parameters must exist in your database. The SQL user account must also have permission to run these procedures.

ClosedSample SQL Script for creating stored procedures

You can use the following parameters for pre-run stored procedures:

  • Connection Name: @connectionName
  • Connection ID: @connectionId
  • Connection Run ID: @connectionRunId
  • Source Name: @source
  • Database Table: @databaseTable
  • Start Time: @startTime
  • Expected Row Count: @expectedRowCount

You can use the following parameters for post-run stored procedures:

  • Connection Name: @connectionName
  • Connection ID: @connectionId
  • Connection Run ID: @connectionRunId
  • Source Name: @source
  • Database Table: @databaseTable
  • Start Time: @startTime
  • Expected Row Count: @expectedRowCount
  • Run Status: @runStatus
  • Source Row Count: @sourceRowCount
  • Destination Row Count: @destinationRowCount
  • Finish Time: @endTime

Specify general connection information

  1. On the Connect Overview page, scroll to the Connections section and click Create Connection.
  2. Enter a name and description for the connection.
  3. (Optional) In the General Information section, expand Advanced to configure the following settings:

    Log Level

    By default, Log Level is set to Information. To reduce the amount of logging, you can set Log Level to Warning, Error, or Fatal.

    Override Log Level

    If you are debugging the connection, select Override Log Level to set a Temporary Log Level (such as Trace or Debug) on this connection for a selected Number of Runs (up to 24). A scheduled or manual connection run, once started, counts towards the number of runs, regardless of the connection status. After the number of runs elapse, the logging for this connection returns to the Log Level you selected to prevent finer-grained logging from consuming additional resources for an indefinite number of runs.

    Minimum Pass Percentage

    Minimum percentage of the expected rows that must be processed for the connection to succeed.

    Memory Ceiling (GB)

    Maximum memory for the node process to run the connection. This defaults to 1 GB per connection, and cannot exceed the global maximum sum of memory for all running connections (by default, 8 GB). Increase this setting if a connection frequently exhibits out of memory errors while running.

    If the sum of simultaneously scheduled connection Memory Ceiling values exceed the global Memory Ceiling, connections run until the global Memory Ceiling is reached, then any remaining connections enter a waiting queue if you select the Queue Connections configuration setting, or fail if you clear the Queue Connections configuration setting.

Configure the connection source

The connection source determines what data you are sending to the destination. This data is usually information from Tanium, such as a saved question, question log, client status, or event. The settings vary depending on which source you choose.

Configure SQL Server destination

Specify information about how to connect to your database application server, test the database application server connection, and configure which data is added to the database tables and rows.

Each time that a connection to a SQL Server destination runs, if the configured destination table does not exist, then the connection attempts to create the table using the configured database user account. The user account must be a member of the db_ddladmin database role to create the table.

  1. In the Configuration section, select SQL Server for the destination.
  2. Specify information about your database application server, including server name, user name, and password.

    If you submit an external access request for traffic, your configured destination FQDN or IP address, port, and protocol must match the FQDN or IP address, port, and protocol submitted in the external access request.

    Enter a hostname (such as or IPv4 address (such as in the Server Name field. If you want to add a named instance, append two backslashes followed by the instance name in the format hostname\\instance_name (such as\\instance) or ip_address\\instance_name (such as\\instance). If you want to add a custom UDP port, append a comma followed by the port number in the format hostname, port_number (, 1434) or ip_address, port_number (such as, 1434).

    If you specify a named instance, the database server must be running the SQL Server Browser service and must have port 1434/UDP opened.

    If you are using Windows Authentication, specify the User Name in the following format: domain_name\login_name (such as domain_general\example_user).

  3. Specify the database application server properties or click Retrieve Properties to choose from those available on the server. If the connection to the database application server is successful, the fields for the database name, schema, and table appear.

    The Retrieve Properties button is disabled if there are no servers configured.

  4. (Optional) Click Retrieve Columns to populate the column values in the Columns section.

    You must be a member of the db_ddladmin database role to retrieve columns from the server.

  5. (Optional) If you want to use encryption, select Encrypt and Validate server's TLS certificate to validate that the certificate is signed by a well-known, public certificate authority. If you want to use a self-signed certificate (not recommended) for testing purposes, deselect Validate server's TLS certificate.

    Deselecting Validate server's TLS certificate is not recommended, as it allows any certificates to be accepted, including invalid or malicious certificates.

  6. (Optional) In the destination settings, expand Advanced to configure advanced settings.
    You can configure timeouts, batch size, packet size, and stored procedures.

    Stored procedure fields and parameters are available only if your database already has stored procedures configured.

Configure filters

(Optional) In the Configure Output > Filters section, you can specify filters to modify the data that you are getting from your connection source before it is sent to the destination.

For more information about the types of filters you can configure, see Reference: Filtering options.

Map database table columns

(Optional) You can view and change the mapping of the data from your source to the columns in your database table.

If the source is a saved question, the following columns are also available for writing to the SQL Server destination:

  • Band Number: The source row number. When flattened, this number is in each row that is associated with a row number that is received from Tanium.
  • Row Number: The destination row number. This value is a counter for the rows that are output per connection run. The value is unique for each connection run.

In the Configure Output > Columns section, you can change the Destination Label of each column and Value Type to force the column to be a String, Numeric, or Date/Time value.

If you choose Numeric for the value, you can specify a default value that is used if the data cannot be coerced into a numeric value. You can specify any negative or positive number.

If you choose Date/Time for the value, specify the format that you want to use for the column. For more information about using a variable, see Time stamp variables.

For more information about column customizations, see Reference: Column customizations.

You can export data in the MM/DD/YYYY, ISO 8601 with time zone, and ISO 8601 UTC (Zulu) date/time formats to database columns of type date, datetime2, datetimeoffset, nvarchar, or time. If you specify a destination column that does not exist in the destination database, it is created as type nvarchar.

When you edit a connection with the SQL Server destination, the columns that are in the database are not displayed by default. You can edit the text fields for the columns, but you must provide the User Name and Password and click Retrieve Columns in the Configuration section to view the columns from the database.

Schedule the connection

Connections can run at a highly configurable time interval, such as multiple times per hour, day, week, or month.

If you do not enable the schedule, the connection only runs when you manually run it, unless you configure an Event source. Connections with Event sources only run when a configured event is detected, and cannot be scheduled or manually run.

Use the Schedule section to update the schedule:

  • Select Enable schedule.
  • In the Schedule Type, select Basic to build a schedule with the provided controls.
  • To view or edit the Cron expression directly, select Advanced - Define as a Cron Expression, and use the Advanced field to edit the Cron expression.

If a user that owns a scheduled connection is deleted, future scheduled instances of that connection do not run. For more information, see Issue: Scheduled connection owned by a deleted user no longer runs.

For more information about Cron syntax, see Reference: Cron syntax.

Save and verify connection

  1. After you enter the details for the connection, click Save.

    To save the connection and immediately run the connection, click Run and Save.

    If needed, resolve any errors or missing information. After the connection creates successfully, the connection details display.

  2. To view details when the connection runs, click the Logs tab.
  3. To view an individual run log, expand the row table. For more information on resolving errors, see Troubleshooting.

Your connection is listed on the Connect Overview page and data is sent to the Microsoft SQL Server at the interval you specified. You might want to check your database table to verify that the data is being exported correctly.