---
title: Elastic Microsoft SQL connector reference
description: The Elastic Microsoft SQL connector is a connector for Microsoft SQL databases. This connector is written in Python using the Elastic connector framework...
url: https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors/es-connectors-ms-sql
products:
  - Elasticsearch
---

# Elastic Microsoft SQL connector reference
The *Elastic Microsoft SQL connector* is a [connector](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors) for [Microsoft SQL](https://learn.microsoft.com/en-us/sql/) databases. This connector is written in Python using the [Elastic connector framework](https://github.com/elastic/connectors/tree/main).
View the [**source code** for this connector](https://github.com/elastic/connectors/tree/main/app/connectors_service/connectors/sources/mssql) (branch *main*, compatible with Elastic *9.0*).

## **Self-managed connector**


### Availability and prerequisites

This connector is available as a self-managed connector. To use this connector, satisfy all [self-managed connector requirements](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors/self-managed-connectors).

### Create a Microsoft SQL connector


#### Use the UI

To create a new Microsoft SQL connector:
1. In the Kibana UI, search for "connectors" using the [global search field](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3016/explore-analyze/query-filter/filtering#_finding_your_apps_and_objects) and choose the "Elasticsearch" connectors.
2. Follow the instructions to create a new  **Microsoft SQL** self-managed connector.


#### Use the API

You can use the Elasticsearch [Create connector API](https://www.elastic.co/docs/api/doc/elasticsearch/group/endpoint-connector) to create a new self-managed Microsoft SQL self-managed connector.
For example:
```json

{
  "index_name": "my-elasticsearch-index",
  "name": "Content synced from Microsoft SQL",
  "service_type": "mssql"
}
```

<dropdown title="You’ll also need to create an API key for the connector to use.">
  <note>
    The user needs the cluster privileges `manage_api_key`, `manage_connector` and `write_connector_secrets` to generate API keys programmatically.
  </note>
  To create an API key for the connector:
  1. Run the following command, replacing values where indicated. Note the `encoded` return values from the response:
     ```json

     {
       "name": "connector_name-connector-api-key",
       "role_descriptors": {
         "connector_name-connector-role": {
           "cluster": [
             "monitor",
             "manage_connector"
           ],
           "indices": [
             {
               "names": [
                 "index_name",
                 ".search-acl-filter-index_name",
                 ".elastic-connectors*"
               ],
               "privileges": [
                 "all"
               ],
               "allow_restricted_indices": false
             }
           ]
         }
       }
     }
     ```
  2. Update your `config.yml` file with the API key `encoded` value.
</dropdown>

Refer to the [Elasticsearch API documentation](https://www.elastic.co/docs/api/doc/elasticsearch/group/endpoint-connector) for details of all available Connector APIs.

### Usage

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*](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors/self-managed-connectors) For additional usage operations, see [*Connectors UI in Kibana*](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors/connectors-ui-in-kibana).

### Compatibility

The following are compatible with Elastic connector frameworks:
- Microsoft SQL Server versions 2017, 2019
- Azure SQL
- Amazon RDS for SQL Server


### Configuration

The following configuration fields are required to set up the connector:
<definitions>
  <definition term="host">
    The server host address where the Microsoft SQL Server is hosted. Default value is `127.0.0.1`. Examples:
    - `192.158.1.38`
    - `demo.instance.demo-region.demo.service.com`
  </definition>
  <definition term="port">
    The port where the Microsoft SQL Server is hosted. Default value is `9090`.
  </definition>
  <definition term="username">
    The username of the account for Microsoft SQL Server. (SQL Server Authentication only)
  </definition>
  <definition term="password">
    The password of the account to be used for the Microsoft SQL Server. (SQL Server Authentication only)
  </definition>
  <definition term="database">
    Name of the Microsoft SQL Server database. Examples:
    - `employee_database`
    - `customer_database`
  </definition>
  <definition term="tables">
    Comma-separated list of tables. The Microsoft SQL connector will fetch data from all tables present in the configured database, if the value is `*` . Default value is `*`. Examples:
    - `table_1, table_2`
    - `*`
    This field can be bypassed by advanced sync rules.
  </definition>
  <definition term="fetch_size">
    Rows fetched per request.
  </definition>
  <definition term="retry_count">
    The number of retry attempts per failed request.
  </definition>
  <definition term="schema">
    Name of the Microsoft SQL Server schema. Default value is `dbo`.
    Examples:
    - `dbo`
    - `custom_schema`
  </definition>
  <definition term="ssl_enabled">
    SSL verification enablement. Default value is `False`.
  </definition>
  <definition term="ssl_ca">
    Content of SSL certificate. If SSL is disabled, the `ssl_ca` value will be ignored.
    <dropdown title="Expand to see an example certificate">
      ```
      -----BEGIN CERTIFICATE-----
      MIID+jCCAuKgAwIBAgIGAJJMzlxLMA0GCSqGSIb3DQEBCwUAMHoxCzAJBgNVBAYT
      ...
      7RhLQyWn2u00L7/9Omw=
      -----END CERTIFICATE-----
      ```
    </dropdown>
  </definition>
  <definition term="validate_host">
    Host validation enablement. Default value is `False`.
  </definition>
</definitions>


### Deployment using Docker

You can deploy the Microsoft SQL connector as a self-managed connector using Docker. Follow these instructions.
<dropdown title="Step 1: Download sample configuration file">
  Download the sample configuration file. You can either download it manually or run the following command:
  ```sh
  curl https://raw.githubusercontent.com/elastic/connectors/main/app/connectors_service/config.yml.example --output ~/connectors-config/config.yml
  ```
  Remember to update the `--output` argument value if your directory name is different, or you want to use a different config file name.
</dropdown>

<dropdown title="Step 2: Update the configuration file for your self-managed connector">
  Update the configuration file with the following settings to match your environment:
  - `elasticsearch.host`
  - `elasticsearch.api_key`
  - `connectors`
  If you’re running the connector service against a Dockerized version of Elasticsearch and Kibana, your config file will look like this:
  ```yaml
  # 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>
  ```
  Using the `elasticsearch.api_key` is the recommended authentication method. However, you can also use `elasticsearch.username` and `elasticsearch.password` to authenticate with your Elasticsearch instance.Note: You can change other default configurations by simply uncommenting specific settings in the configuration file and modifying their values.
</dropdown>

<dropdown title="Step 3: Run the Docker image">
  Run the Docker image with the Connector Service using the following command:
  ```sh
  docker run \
  -v ~/connectors-config:/config \
  --network "elastic" \
  --tty \
  --rm \
  docker.elastic.co/integrations/elastic-connectors:9.3.2 \
  /app/bin/elastic-ingest \
  -c /config/config.yml
  ```
</dropdown>

Refer to [`DOCKER.md`](https://github.com/elastic/connectors/tree/main/docs/DOCKER.md) in the `elastic/connectors` repo for more details.
Find all available Docker images in the [official registry](https://www.docker.elastic.co/r/integrations/elastic-connectors).
<tip>
  We also have a quickstart self-managed option using Docker Compose, so you can spin up all required services at once: Elasticsearch, Kibana, and the connectors service. Refer to this [README](https://github.com/elastic/connectors/tree/main/scripts/stack#readme) in the `elastic/connectors` repo for more information.
</tip>


### Documents and syncs

- Tables with no primary key defined are skipped.
- If the `last_user_update` of `sys.dm_db_index_usage_stats` table is not available for a specific table and database then all data in that table will be synced.

<note>
  - Files bigger than 10 MB won’t be extracted.
  - Permissions are not synced. **All documents** indexed to an Elastic deployment will be visible to **all users with access** to that Elastic Deployment.
</note>


### Sync rules

[Basic sync rules](/elastic/docs-builder/docs/3016/reference/search-connectors/es-sync-rules#es-sync-rules-basic) are identical for all connectors and are available by default. For more information read [sync rules](/elastic/docs-builder/docs/3016/reference/search-connectors/es-sync-rules#es-sync-rules-types).

#### Advanced sync rules

This connector supports [advanced sync rules](/elastic/docs-builder/docs/3016/reference/search-connectors/es-sync-rules#es-sync-rules-advanced) 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.
<note>
  A [full sync](/elastic/docs-builder/docs/3016/reference/search-connectors/content-syncs#es-connectors-sync-types-full) is required for advanced sync rules to take effect.
</note>

Here are a few examples of advanced sync rules for this connector.
<dropdown title="Expand to see example data">
  **`employee` table**

  | emp_id | name | age |
  |--------|------|-----|
  | 3      | John | 28  |
  | 10     | Jane | 35  |
  | 14     | Alex | 22  |

  - **`customer` table**


  | c_id | name | age |
  |------|------|-----|
  | 2    | Elm  | 24  |
  | 6    | Pine | 30  |
  | 9    | Oak  | 34  |
</dropdown>


**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.
```js
[
  {
    "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.
```js
[
  {
    "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.
```js
[
  {
    "tables": ["employee", "customer"],
    "query": "SELECT * FROM employee INNER JOIN customer ON employee.emp_id = customer.c_id"
  }
]
```

<warning>
  When using advanced rules, a query can bypass the configuration field `tables`. This will happen if the query specifies a table that doesn’t appear in the configuration. This can also happen if the configuration specifies `*` to fetch all tables while the advanced sync rule requests for only a subset of tables.
</warning>


### End-to-end testing

The connector framework enables operators to run functional tests against a real data source. Refer to [Connector testing](/elastic/docs-builder/docs/3016/reference/search-connectors/self-managed-connectors#es-build-connector-testing) for more details.
To perform E2E testing for the Microsoft SQL connector, run the following command:
```shell
make ftest NAME=mssql
```

For faster tests, add the `DATA_SIZE=small` flag:
```shell
make ftest NAME=mssql DATA_SIZE=small
```


### Known issues

There are no known issues for this connector. See [Known issues](https://www.elastic.co/elastic/docs-builder/docs/3016/release-notes/elasticsearch/known-issues) for any issues affecting all connectors.

### Troubleshooting

See [Troubleshooting](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors/es-connectors-troubleshooting).

### Security

See [Security](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/search-connectors/es-connectors-security).
This connector uses the [generic database connector source code](https://github.com/elastic/connectors-python/blob/master/connectors/sources/generic_database.py) (branch *main*, compatible with Elastic *9.0*).
View [additional code specific to this data source](https://github.com/elastic/connectors/tree/main/app/connectors_service/connectors/sources/mssql) (branch *main*, compatible with Elastic *9.0*).