Microsoft SQL Server

The Microsoft SQL Server import agent for Elimity Insights queries your on-premise Microsoft SQL Server instances and uploads the results to your Elimity Insights server. You configure the database connections, the queries to execute, the expected result schema and the connection with your Elimity Insights server. The import agent will then connect to your databases, execute the configured queries, transform the results to a format compatible with Elimity Insights and send the transformed data to your Elimity Insights server.

Generic usage

The import agent is available as a standalone executable for Linux, MacOS and Windows. Contact your Elimity representative to get access to these binaries. Running the import agent with the `-help` option prints a generic usage message:

$ elimity-insights-client-mssql -help
Usage of elimity-insights-client-mssql:
  -config-file string
    path to HJSON file containing import configuration (default "config.hjson")
  -print-config-schema
    print a JSON schema definition for the configuration file and exit
  -print-data-model-export
    print a data model export file for creating a custom source in Elimity Insights and exit
  -print-example-config
    print an example HJSON configuration file and exit
  -print-version
    print version information and exit

Step-by-step deployment guide

The following sections will explain the different steps you'll need to take to deploy the Microsoft SQL Server agent for Elimity Insights.

1. Configuring the agent

You can get an example configuration file by running the import agent with the `-print-example-config` flag and writing its output to a `config.hjson` file:

$ elimity-insights-client-mssql -print-example-config > config.hjson

This example configuration will simply import all principals and permissions from your databases. Leave the `entityTypes` and `relationshipTypes` configuration properties untouched if you want to try this standard import. Edit the following properties in this file to configure the import agent to your needs:

• `entityTypes`: a list of configuration objects describing how to import entities of a specific type into Elimity Insights
  • `attributes`: a list of configuration objects describing how to import attributes of this entity type's entities
    • `id`: unique identifier of the attribute type for which the agent should import assignments
    • `name`: human-readable name of the attribute type (*)
    • `type`: data type of the attribute type, one of `boolean`, `date`, `dateTime`, `number`, `string` or `time`
  • `icon`: icon of the entity type (*) 
  • `id`: unique identifier of the entity type for which the agent should import entities
  • `plural`: plural form of the entity type's name (*)
  • `query`: query that the import agent should send to the configured Microsoft SQL Server database, each resulting row corresponds to an entity of this type
  • `singular`: singular form of the entity type's name (*)
• `insightsSourceId`, `insightsSourceToken`: you can leave these properties untouched for now, we'll come back to them later
• `insightsUrl`: URL of your Elimity Insights server
• `mssqlDsns`: list of connection strings that the import agent should use to connect with your Microsoft SQL Server instance (we use Microsoft's official Go MSSQL driver behind the scenes, refer to its documentation on supported connection formats); we recommend connecting with a dedicated read-only user (e.g. by only assigning the `db_datareader` role)
• `relationshipTypes`: a list of configuration objects describing how to import relationships between entities of two specific types into Elimity Insights
  • `attributes`: a list of configuration objects describing how to import attributes of this relationship type's relationships
    • `id`: unique identifier of the attribute type for which the agent should import assignments
    • `name`: human-readable name of the attribute type (*)
    • `type`: data type of the attribute type, one of `boolean`, `date`, `dateTime`, `number`, `string` or `time`
  • `from`: unique identifier of the entity type from which the relationships start
  • `query`: query that the import agent should send to the configured Microsoft SQL Server database, each resulting row corresponds to a relationship between two entities of the configured types
  • `to`: unique identifier of the entity type where the relationships end
• `transactionIsolationLevel`: a number representing which isolation level the agent should use for the transaction in which it combines all queries; should be one of the following:
  
  • 1: read uncommitted
  • 2: read committed
  • 4: repeatable read
  • 5: snapshot (this is used when you omit the `transactionIsolationLevel` field)
  • 6: serializable

Properties marked with a (*) do not affect the importing functionality, they are only necessary to generate a data model export file when running the import agent with the `-print-data-model-export` flag.

2. Creating a custom source in Elimity Insights

The import agent also includes some functionality to help you with creating a source in Elimity Insights. Assuming you correctly configured the properties marked with a (*) in the previous section, the following command will write a data model export file to `data-model-export.json`:

$ elimity-insights-client-mssql -print-data-model-export > data-model-export.json

You can now upload this file to Elimity Insights when creating a new custom source. Copy the resulting source's identifier and token to the `insightsSourceId` and `insightsSourceToken` properties in the agent's configuration file.

3. Triggering an import

Having configured the agent and having created a source in Elimity Insights, you can now run the effective import to query data from your Microsoft SQL Server instance and upload it to Elimity Insights. Run the following command to trigger a single import:

$ elimity-insights-client-mssql

The import agent should start outputting some logs to indicate its progress.

SQL query format

When writing queries to configure the import agent, you should make sure they adhere to the following rules:

• for entity queries:
  • the number of output columns should equal the number of configured attributes plus two
  • the first output column should have a textual type and represents the entity's id
  • the second output column should have a textual type and represents the entity's name
  • the following output columns should type-match with the entity type's configured attributes (so order of configured attributes is important)
• for relationship queries:
  • the number of output columns should equal the number of configured attributes plus two
  • the first output column should have a textual type and represents the source entity's id
  • the second output column should have a textual type and represents the target entity's id
  • the following output columns should type-match with the relationship type's configured attributes (so order of configured attributes is important)
• the import agent will not generate attribute assignments for output columns that have a `NULL` value