Elastic Microsoft SQL connector reference

This connector is available as a managed connector in Elastic versions 8.8.0 and later. To use this connector natively in Elastic Cloud, satisfy all managed connector requirements.

To create a new Microsoft SQL connector:

For additional operations, see Connectors UI in Kibana.

You can use the Elasticsearch Create connector API to create a new native Microsoft SQL connector.

For example:

resp = client.connector.put(
    connector_id="my-{service-name-stub}-connector",
    index_name="my-elasticsearch-index",
    name="Content synced from {service-name}",
    service_type="{service-name-stub}",
    is_native=True,
)
print(resp)

const response = await client.connector.put({
  connector_id: "my-{service-name-stub}-connector",
  index_name: "my-elasticsearch-index",
  name: "Content synced from {service-name}",
  service_type: "{service-name-stub}",
  is_native: true,
});
console.log(response);

PUT _connector/my-mssql-connector
{
  "index_name": "my-elasticsearch-index",
  "name": "Content synced from Microsoft SQL",
  "service_type": "mssql",
  "is_native": true
}

Refer to the Elasticsearch API documentation for details of all available Connector APIs.

To use this connector as a managed connector, use the Connector workflow. See Elastic managed connectors.

Users require the sysadmin SQL Server role. Note that SQL Server Authentication is required. Windows Authentication is not supported.

For additional operations, see Connectors UI in Kibana.

The following are compatible with Elastic connector frameworks:

The following configuration fields are required to set up the connector:

Basic sync rules are identical for all connectors and are available by default. For more information read sync rules.

This connector supports advanced sync rules for remote filtering. These rules cover complex query-and-filter scenarios that cannot be expressed with basic sync rules. Advanced sync rules are defined through a source-specific DSL JSON snippet.

Here are a few examples of advanced sync rules for this connector.

Example: Two queries

These rules fetch all records from both the employee and customer tables. The data from these tables will be synced separately to Elasticsearch.

[
  {
    "tables": [
      "employee"
    ],
    "query": "SELECT * FROM employee"
  },
  {
    "tables": [
      "customer"
    ],
    "query": "SELECT * FROM customer"
  }
]

Example: One WHERE query

This rule fetches only the records from the employee table where the emp_id is greater than 5. Only these filtered records will be synced to Elasticsearch.

[
  {
    "tables": ["employee"],
    "query": "SELECT * FROM employee WHERE emp_id > 5"
  }
]

Example: One JOIN query

This rule fetches records by performing an INNER JOIN between the employee and customer tables on the condition that the emp_id in employee matches the c_id in customer. The result of this combined data will be synced to Elasticsearch.

[
  {
    "tables": ["employee", "customer"],
    "query": "SELECT * FROM employee INNER JOIN customer ON employee.emp_id = customer.c_id"
  }
]

There are no known issues for this connector. See Known issues for any issues affecting all connectors.

See Troubleshooting.

See Security.

This connector uses the generic database connector source code (branch 8.x, compatible with Elastic 8.17).

View additional code specific to this data source (branch 8.x, compatible with Elastic 8.17).

This connector is available as a self-managed self-managed connector. To use this connector, satisfy all self-managed connector requirements.

To create a new Microsoft SQL connector:

You can use the Elasticsearch Create connector API to create a new self-managed Microsoft SQL self-managed connector.

For example:

resp = client.connector.put(
    connector_id="my-{service-name-stub}-connector",
    index_name="my-elasticsearch-index",
    name="Content synced from {service-name}",
    service_type="{service-name-stub}",
)
print(resp)

const response = await client.connector.put({
  connector_id: "my-{service-name-stub}-connector",
  index_name: "my-elasticsearch-index",
  name: "Content synced from {service-name}",
  service_type: "{service-name-stub}",
});
console.log(response);

PUT _connector/my-mssql-connector
{
  "index_name": "my-elasticsearch-index",
  "name": "Content synced from Microsoft SQL",
  "service_type": "mssql"
}

Refer to the Elasticsearch API documentation for details of all available Connector APIs.

Users require the sysadmin server role. Note that SQL Server Authentication is required. Windows Authentication is not supported.

To use this connector as a self-managed connector, see Self-managed connectors For additional usage operations, see Connectors UI in Kibana.

The following are compatible with Elastic connector frameworks:

The following configuration fields are required to set up the connector:

You can deploy the Microsoft SQL connector as a self-managed connector using Docker. Follow these instructions.

curl https://raw.githubusercontent.com/elastic/connectors/main/config.yml.example --output ~/connectors-config/config.yml

# When connecting to your cloud deployment you should edit the host value
elasticsearch.host: http://host.docker.internal:9200
elasticsearch.api_key: <ELASTICSEARCH_API_KEY>

connectors:
  -
    connector_id: <CONNECTOR_ID_FROM_KIBANA>
    service_type: mssql
    api_key: <CONNECTOR_API_KEY_FROM_KIBANA> # Optional. If not provided, the connector will use the elasticsearch.api_key instead

docker run \
-v ~/connectors-config:/config \
--network "elastic" \
--tty \
--rm \
docker.elastic.co/enterprise-search/elastic-connectors:8.17.0.0 \
/app/bin/elastic-ingest \
-c /config/config.yml

Refer to DOCKER.md in the elastic/connectors repo for more details.

Find all available Docker images in the official registry.

Basic sync rules are identical for all connectors and are available by default. For more information read sync rules.

This connector supports advanced sync rules for remote filtering. These rules cover complex query-and-filter scenarios that cannot be expressed with basic sync rules. Advanced sync rules are defined through a source-specific DSL JSON snippet.

Here are a few examples of advanced sync rules for this connector.

Example: Two queries

These rules fetch all records from both the employee and customer tables. The data from these tables will be synced separately to Elasticsearch.

[
  {
    "tables": [
      "employee"
    ],
    "query": "SELECT * FROM employee"
  },
  {
    "tables": [
      "customer"
    ],
    "query": "SELECT * FROM customer"
  }
]

Example: One WHERE query

This rule fetches only the records from the employee table where the emp_id is greater than 5. Only these filtered records will be synced to Elasticsearch.

[
  {
    "tables": ["employee"],
    "query": "SELECT * FROM employee WHERE emp_id > 5"
  }
]

Example: One JOIN query

This rule fetches records by performing an INNER JOIN between the employee and customer tables on the condition that the emp_id in employee matches the c_id in customer. The result of this combined data will be synced to Elasticsearch.

[
  {
    "tables": ["employee", "customer"],
    "query": "SELECT * FROM employee INNER JOIN customer ON employee.emp_id = customer.c_id"
  }
]

The connector framework enables operators to run functional tests against a real data source. Refer to Connector testing for more details.

To perform E2E testing for the Microsoft SQL connector, run the following command:

make ftest NAME=mssql

For faster tests, add the DATA_SIZE=small flag:

make ftest NAME=mssql DATA_SIZE=small

There are no known issues for this connector. See Known issues for any issues affecting all connectors.

See Troubleshooting.

See Security.

This connector uses the generic database connector source code (branch 8.x, compatible with Elastic 8.17).

View additional code specific to this data source (branch 8.x, compatible with Elastic 8.17).