Configuring SQL Server destinations

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.

Sample SQL Script for creating stored procedures

GO
/****** Object: Table [dbo].[connection_log]  Script Date: 4/25/2017 8:02:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[connection_log](
  [connectionRunId] [bigint] NOT NULL,
  [connectionId] [bigint] NOT NULL,
  [connectionName] [nvarchar](200) NOT NULL,
  [source] [nvarchar](200) NOT NULL,
  [databaseTable] [nvarchar](100) NULL,
  [startTime] [datetime] NOT NULL,
  [endTime] [datetime] NULL,
  [runStatus] [nvarchar](20) NOT NULL,
  [expectedRowCount] [bigint] NOT NULL,
  [sourceRowCount] [bigint] NOT NULL,
  [destinationRowCount] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [connectionRunId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
	ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[sp_onConnectionFinish]  Script Date: 4/25/2017 8:02:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_onConnectionFinish]
  @connectionRunId bigint,
  @runStatus nvarchar(20),
  @sourceRowCount bigint,
  @destinationRowCount bigint,
  @finishTime datetime
as
BEGIN
  update connection_log set runStatus = @runStatus, sourceRowCount = @sourceRowCount, 
	destinationRowCount = @destinationRowCount, endTime = @finishTime
  where connectionRunId = @connectionRunId
END
GO
/****** Object: StoredProcedure [dbo].[sp_onConnectionStart]  Script Date: 4/25/2017 8:02:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_onConnectionStart]
  @connectionName nvarchar(200),
  @connectionId bigint,
  @connectionRunId bigint,
  @source nvarchar(200),
  @databaseTable nvarchar(100),
  @startTime datetime,
  @expectedRowCount bigint
AS
BEGIN
  if exists (select * from connection_log where ConnectionRunId = @connectionRunId) BEGIN
    delete connection_log where ConnectionRunId = @connectionRunId
  END
  insert into connection_log (connectionRunId, connectionId, connectionName, source, databaseTable, 
	startTime, endTime, runStatus, expectedRowCount, sourceRowCount, destinationRowCount)
  values (@connectionRunId, @connectionId, @connectionName, @source, @databaseTable, @startTime, null, 
	'Running', isnull(@expectedRowCount, 0), 0, 0)
END
GO

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

The @runStatus parameter always returns Finished because the post-run stored procedure runs only after a successful connection run. The @endTime parameter is the time the stored procedure is executed, and is UTC formatted as ISO8601. The @databaseTable parameter is in the format of database schema.database table (for example, dbo.connection_log). The @expectedRowCount parameter could return NULL for sources other than saved questions.

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.

After you create a connection, you cannot update the connection source type, only the source configuration. If you want to change the source type, create a new connection.

Connection sources

Action History

The action history is a record of all actions issued by console operators. To view this record in Tanium, go to Administration > Actions > Action History. For more information, see Tanium Console User Guide: Manage actions that are completed or in progress.

Client Status

Client Status, previously named System Status, includes the state of all the endpoints, including some useful information about the endpoint like IP Address, position in the network, and the last time it registered with the Tanium Server. For more information about the Client Status data, see Tanium Console User Guide: View the status of Tanium Client registration and communication.

Event

Tanium solutions, like Tanium™ Discover, Tanium™ Integrity Monitor, and Tanium™ Threat Response, can forward events to Connect as a data source. These events can then be used as a connection source in a connection and sent to any of the available connection destinations. For more information, see Tanium Discover User Guide: Configure event notifications, Tanium Integrity Monitor User Guide: Sending events from basic monitors, and Tanium Threat Response User Guide: Exporting audit data.

Question History

The question history log is a history of every question that has been asked. When you are using the question log as a data source in Connect, you can filter the log in several ways to reduce the total volume of data being sent. For more information, see Tanium Console User Guide: Question history.

Saved Question

A saved question is a Tanium question that you want to ask on a repeated basis. For more information about saved questions, see Tanium Interact User Guide: Managing saved questions.

When you select a saved question as a source, the Computer Group drop-down defaults to No Filter, which does not filter the saved question with a computer group. Select No Filter if you want to send recent saved question results from all endpoints to the destination. The endpoint results are subject to the computer group management rights of the user configuring the connection, and might not match the endpoint membership of the All Computers computer group. Select the All Computers computer group if you want to explicitly filter the saved question on the All Computers computer group.

You can use the following settings for saved question sources:

Setting	Description
Flatten Results	You might want to enable the Flatten Results setting to process results as individual records. For example, you might want to get notified when you see a new MD5 hash on a machine. Without the Flatten Results setting enabled, the entire data set that is retrieved by the saved question from a machine, such as all MD5 hashes, is considered to be a single record. Any change that is made to this data set shows up in the destination. By enabling the Flatten Results setting, Connect processes the new hashes on an individual basis (one MD5 hash from one machine) instead of all hashes from a machine as a single record.
Hide Errors	If the saved question returns an error, you can use the Hide Errors setting to prevent the error results from getting sent to the destination.
Hide No Results	If the saved question returns [No results], you can use the Hide No Results setting to prevent this result from being sent to the destination.
Include Recent Answers	If you want to include results from machines that are offline, select Include Recent Answers, which returns the most recent answer to the saved question for the offline endpoint. If you select Include Recent Answers, for the best results, you should also select No Filter from the Computer Group drop-down.
Answer Complete Percent	Results are returned when the saved question returns the configured complete percent value. Any results that come in after the configured percent value has passed are not sent to the destination. If you are finding that the data returned from the saved question is incomplete in your destination, you can disable this setting by setting it to 0. If disabled, all data is returned after the timeout passes.
Timeout	Minutes to wait for clients to reply before returning processed results when Answer Complete Percent is set to 0. If the Answer Complete Percent value is not met at the end of the time limit, then the connection run is marked as a failure. The maximum timeout is 10 minutes.
Batchsize	Number of rows that are returned for the saved question results at one time. This setting might vary depending on your destination.

Server Information Source

Use the server information in the following location as a connection source: https://<tanium_server>/info.json.

Tanium™ Asset

Tanium Asset comes with a set of predefined reports to help you prepare for audit and inventory activities. You can also create your own custom reports and views. For each report or view, you can create a connection that specifies a report or view as a data source. Currently supported destinations include Email, File, HTTP, Socket Receiver, Splunk, and SQL Server. For more information, see Tanium Asset User Guide: Asset overview.

Tanium™ Audit Source

Tanium Server keeps detailed audit logs for server configuration and settings changes. However, accessing these logs requires direct access to the Tanium database. To access the audit logs, you can set them up as a data source in Connect. For more information, see Tanium Security Recommendations Guide: Enable and forward Tanium logs.

For information on data available with the Tanium Audit Source, see Reference: Tanium Audit Source data.

Tanium™ Comply

Tanium Comply enables you to export compliance and vulnerability findings to help support enterprise compliance goals. Use the Tanium Comply (Findings) source to export all compliance and all vulnerability findings. For more information, see Tanium Comply User Guide: Exporting findings and assessments.

Exporting vulnerability assessments through Tanium Connect is a deprecated feature, which means it is available but is no longer receiving updates, or active development. Instead, use Tanium Comply (Findings) as a source.

Tanium™ Data Service

The Tanium Data Service enables you to see stored sensor results for endpoints that are offline at the moment you issue a saved question. For more information, see Tanium Interact User Guide: Managing Tanium Data Service.

Tanium™ Direct Connect

Tanium Direct Connect enables other Tanium modules to establish sessions with endpoints. You can create a connection that generates an audit report of Direct Connect sessions and actions that users performed on endpoints during Direct Connect sessions. For more information, see Tanium Direct Connect User Guide: Exporting an audit log.

Tanium™ Discover

Tanium Discover contains reports that maintain an inventory of interfaces in your environment. For each report, you can create a connection that specifies a report as a data source. For more information, see Tanium Discover User Guide: Discover overview.

Tanium™ Endpoint Configuration

Tanium Endpoint Configuration enables you to deliver configuration information to endpoints consistently for all Tanium solutions that are available in an environment. You can create a connection that generates an audit report of all Endpoint Configuration management actions, manifest actions, and configuration changes. For more information, see Tanium Endpoint Configuration User Guide: Exporting an audit log.

Tanium™ Impact

Tanium Impact identifies the users, groups, and endpoints that have the highest potential impact in your organization if compromised, based on the impact rating. You can create a connection that specifies all users, groups, or endpoints with the highest impact as a data source. For more information, see Tanium Impact User Guide: Impact overview.

Tanium™ Integrity Monitor

Tanium Integrity Monitor enables you to define watchlists of files, directories, and Windows registry paths that you want to monitor for changes. Use the Tanium Integrity Monitor source to export watchlist data. For more information, see Tanium Integrity Monitor User Guide: Integrity Monitor overview.

Tanium™ Reporting

Use Tanium Reporting to create custom reports and dashboards from data that is collected by the Tanium Data Service. You can create a connection to export report data or a dashboard. For more information, see Tanium Reporting User Guide: Export reports through Tanium Connect and Tanium Reporting User Guide: Export dashboards through Tanium Connect.

Tanium™ Reputation

Tanium Reputation is an aggregated repository of reputation data from various sources, including Palo Alto Networks WildFire, ReversingLabs, and VirusTotal. You can choose which type of status to include, such as only malicious or suspicious content. You can choose to include the full report, which includes the detailed information from the reputation source, not just the status of the reputation item. You must have one or more reputation sources configured to get information from this connection source. For more information, see Tanium Reputation User Guide: Reputation overview.

Tanium™ Threat Response

Tanium Threat Response contains audit reports for actions that were performed in Threat Response. For each report, you can create a connection that specifies a report as a data source. For more information, see Tanium Threat Response User Guide: Threat Response overview.

Tanium™ Trends

Tanium Trends provides data visualization panels from saved question or module sources. You can create boards that organize one or more panels. For each board, you can create a connection that specifies a board as a data source in HTML format. Valid destinations are AWS S3, Email, or File. For more information, see Tanium Trends User Guide: Trends overview.

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.

After you create a connection, you cannot update the connection destination type, only the destination configuration. If you want to change the destination, create a new connection.

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 sqlserver.domain.com) or IPv4 address (such as 192.0.2.10) in the Server Name field.

   If you want to add a named instance, append a backslash followed by the instance name in the format hostname\instance_name (such as sqlserver.domain.com\instance) or ip_address\instance_name (such as 192.0.2.10\instance).

   If you want to add a custom port, append a comma followed by the port number in the format hostname, port_number (sqlserver.domain.com, 1434) or ip_address, port_number (such as 192.0.2.10, 1434).

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

   You cannot configure a named instance directly because Tanium Cloud does not support UDP traffic. If you want to use a named instance as a destination, configure the named instance to use a static port number, then configure the connection with the hostname and static port number. In addition, the database server must be running the SQL Server Browser service and must have port 1434/UDP opened.

   SQL Server traffic does not use SNI, and is limited to one destination per port in Tanium Cloud. If you deploy multiple SQL Server instances on different hosts listening on the same port, you can only configure a destination for one of the SQL Server instances listening on that port. Instead, configure a Microsoft Azure SQL Database instance that can replicate to other SQL Server instances, then configure a destination for your Microsoft Azure SQL Database instance. Alternatively, configure your SQL Server instances to listen on different custom ports, then configure a destination for each instance and custom port.

   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. Retrieving columns for a stored procedure is not supported for Microsoft Azure SQL Database.

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.

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

   Advanced setting	Description
   Connect Timeout	The maximum number of seconds to establish a network connection between the connection source and the SQL Server destination. A connection run fails if a network connection is not established within this timeout period.
   Request Timeout	The maximum number of seconds to complete a batch request from the connection source to the SQL Server destination, after establishing a network connection. Each batch request contains a batch of rows from the connection source. A connection run fails if a batch request does not complete within this timeout period. A single connection run might send more than one batch request to the SQL Server depending on the Batch Size and the number of rows available.
   Batch Size	The maximum number of rows sent per batch from the connection source to the SQL Server destination. If the total number of rows exceeds the defined Batch Size for a single connection run, the connection run sends multiple batch requests, waiting for one batch request to complete before sending the next batch request. This has no effect if one row or fewer is available to send in a connection run.
   Packet Size	The Tabular Data Stream protocol (TDS protocol) packet size, used for communication between the connection source and the SQL Server destination. If set to 0, TDS protocol packet size is defined by the SQL Server setting (generally 4096 bytes).
   Multi-Subnet Failover	For Always On cluster deployments only, better resolve the active server DNS name and improve performance in failover situations.

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.

Connections scheduled to run during a Tanium Cloud maintenance window might be interrupted or fail. Schedule your connections to run outside of the Tanium Cloud maintenance window. For information on configuring a custom maintenance window start time, see Tanium Cloud Deployment Guide: Configure custom maintenance window.

Connections scheduled to run during an upgrade of Connect, or any Tanium solution configured as a connection source, might be interrupted or fail. Schedule your connections to run at a different time than your Tanium upgrades.

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.
  
  
  
  
  

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.