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.

   Minimum Pass Percentage

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

   

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, system 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.

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 Date/Time 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.
  

Save and verify connection

1. After you enter the details for the connection, click Save. If needed, resolve any errors or missing information. When the connection gets created, the connection displays in the Connections section of the Connect Overview page.
2. To view details about when the connection is running, click the name of the connection. On the resulting connection details page, 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.