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, the logging is set to Information. Set the log level to Trace or Debug if you are debugging the connection. To reduce the amount of logging, you can set the log level to Warning, Error, or Fatal.
   

   By default, the logging is set to Information. Set the log level to Trace or Debug if you are debugging the connection. To reduce the amount of logging, you can set the log level to Warning, Error, or Fatal.
   
   
   

   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.

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.

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™ Network Quarantine, 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 Network Quarantine User Guide: Configuring notifications, Tanium Integrity Monitor User Guide: Sending events from basic monitors, and Tanium Threat Response User Guide: Exporting audit data.

Palo Alto Networks WildFire

Integration between Tanium and WildFire takes a list of confirmed malware from a Palo Alto Networks firewall and requests a full report from the WildFire system. The full malware report is then converted into a standard indicator of compromise (IOC) and passed to the Threat Response system for multiple endpoint compromise detection. For more information, see Configuring Palo Alto Networks WildFire and Tanium Threat Response.

Palo Alto Networks WildFire is available as a source after Threat Response is installed.

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.

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.
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. Use the Tanium Comply (Assessments) source to export all vulnerability assessments. For more information, see Tanium Comply User Guide: Exporting findings and assessments.

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 Console User Guide: Manage sensor results collection.

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™ 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™ 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.

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 are using Windows Authentication, specify the user name in the following format: domain_name\login_name.
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:

• Connection Run ID: The ID of the connection run.
• 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.

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.

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.

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.