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 Home page, click Create Connection > Create.
2. Enter a name and description for your connection.
3. Enable the connection to run on a schedule.
   Select Enable. You can set up the schedule when you configure the rest of the connection. If the schedule is not enabled, the connection only runs when you manually run it.
4. (Optional) Set the logging 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.
5. (Optional) Expand Advanced Settings to configure the following settings:

   Minimum Pass Percent

   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, 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 Destination section, choose SQL Server.
2. Specify information about your database application server, including server name, user ID, 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.
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, included invalid or malicious certificates.

6. (Optional) Configure advanced settings. Expand 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) You can use 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.

You can change the Destination name of each column and Value Type to force the column to be a String, Numeric, or DateTime 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 DateTime 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 Source and Destination 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.

Update the schedule: 

• Use the Generate Cron tab to build a schedule based on some common time intervals. This tab generates a Cron expression.
  
• To view or edit the Cron expression directly, click the Edit Cron Expression tab.
  

Save and verify connection

1. Click Create Connection > Create. When the connection gets created, your new connection displays in the list on the Connections 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 Runs tab.
3. To view individual run logs, click the link in the Status column in the Runs table.

Your connection is listed on the Connections 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.