---
title: Install & configure NRDOT for MSSQL monitoring with Ansible
source: https://docs.newrelic.com/docs/opentelemetry/db360/mssql/ansible
---

You can install and configure the NRDOT Collector for SQL Server monitoring using the `newrelic.newrelic_install` Ansible role. The role installs the collector, creates or authorizes the monitoring identity, and configures the collector in a single play.

## Prerequisites [#prerequisites]

-   A New Relic account with a valid [license key](https://docs.newrelic.com/docs/apis/intro-apis/new-relic-api-keys/#overview-keys).
-   A New Relic [account ID](https://docs.newrelic.com/docs/accounts/accounts-billing/account-structure/account-id).
-   Microsoft SQL Server 2017 or later.
-   Ansible Core 2.13 or 2.14, Python 3.10, and the `ansible.windows` and `ansible.utils` collections.
-   Self-hosted SQL Server:
    -   SQL Server Authentication: A Linux or Windows collector host, and a login in the `sysadmin` server role (or equivalent) with its password.
    -   Windows Authentication or gMSA: A Windows collector host, an administrator PowerShell session, and a Windows account or Group Managed Service Account (`gMSA`) with the required permissions.
-   SQL Server on AWS RDS:
    -   A Linux or Windows collector host with network connectivity to the database endpoint (inbound access granted on the SQL Server port within the security group), alongside the database master username and password.
    -   Windows Domain Authentication or gMSA: A Windows collector host and an instance joined to an AWS Managed Microsoft Active Directory domain.
-   Linux collector host: The `curl` utility, `systemd`, and the `sqlcmd` utility installed and available on `PATH` (or located under `/opt/mssql-tools18/bin` or `/opt/mssql-tools/bin`).
-   Windows collector host: The `sqlcmd.exe` utility installed and available on `PATH`.
-   Network connectivity to the [New Relic OTLP endpoint](https://docs.newrelic.com/docs/opentelemetry/best-practices/opentelemetry-otlp) corresponding to the target region.

## Install the role [#ansible-install-role]

```bash
ansible-galaxy install newrelic.newrelic_install
```

Make sure the required collections are also installed:

```bash
ansible-galaxy collection install ansible.windows ansible.utils
```

## Configure the playbook [#ansible-configure]

**Self-hosted - SQL Server Authentication**

This role monitors one or more SQL Server instances from a single collector. Instead of a single host/port/credential set, it reads two files that must already exist on the target host: an instances file (YAML) listing every instance to monitor, and a secrets file (`KEY=VALUE`) with an admin login and password for each, indexed to match.

1.  Create the instances file on the target host. Add one entry per SQL Server instance you want this collector to monitor, or just one entry if you're only running a single instance:

    ```yaml
    instances:
      - host: localhost
        port: 1433
        login_name: newrelic
    ```

2.  Create the secrets file on the target host with an admin login and password for each instance, indexed to match the instances file's order. The admin login must be a member of the `sysadmin` role (or equivalent):

    ```bash
    NR_CLI_MSSQL_ADMIN_USER_1=<YOUR_ADMIN_USERNAME>
    NR_CLI_MSSQL_ADMIN_PASSWORD_1=<YOUR_ADMIN_PASSWORD>
    ```

Then create a file named `playbook.yml` and copy the following content into it. Set `targets` to `mssql-otel` and replace the placeholder values with your own:

```yaml
- name: Install New Relic NRDOT for SQL Server
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host>
    NR_CLI_MSSQL_SECRETS_FILE: <path to the secrets file (KEY=VALUE per instance) on the target host>
```

> #### 💡 TIP
>
> To enable debug logging, add `verbosity: "debug"` under `vars` in the playbook.

| Variable                      | Description                                                                                                                       | Default |
| ----------------------------- | --------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `NR_CLI_MSSQL_CONFIG_PRESET`  | NRDOT configuration: 1 for Basic 2 for Advanced                                                                                   | `1`     |
| `NR_CLI_MSSQL_INSTANCES_FILE` | Required. Path to the instances YAML file, already present on the target host.                                                    | None    |
| `NR_CLI_MSSQL_SECRETS_FILE`   | Required. Path to the secrets file (`KEY=VALUE`, one admin login/password pair per instance), already present on the target host. | None    |

> #### 💡 TIP
>
> To monitor more than one SQL Server instance from this collector, add more entries to the instances file and matching numbered credentials (`_2`, `_3`, ...) to the secrets file. An instance that fails its checks is skipped with a logged reason rather than aborting the whole install.

**Self-hosted - Windows Authentication or gMSA**

This target is only supported on a Windows collector host. It uses Windows authentication (`integrated security=true`) instead of SQL logins, and monitors one or more SQL Server instances from a single collector using an **instances file** (YAML, `host`/`port` per instance, no secrets file: one Windows identity monitors every instance).

Create the instances file on the target host, with just one entry if you're only running a single instance. Only `host` and `port` are needed (no `login_name`):

```yaml
instances:
  - host: sql01.contoso.com
    port: 1433
  - host: sql02.contoso.com
    port: 1433
```

For a named instance, use `host` plus its static TCP port rather than `host\INSTANCE`.

Then create a file named `playbook.yml` and copy the following content into it. Set `targets` to `mssql-otel-winauth` and replace the placeholder values with your own:

```yaml
- name: Install New Relic NRDOT for SQL Server (Windows Auth)
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel-winauth
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_AUTH_MODE: <1 for Windows Domain Auth, 2 for gMSA, default 1>
    NR_CLI_MSSQL_WINAUTH_LOCATION: <1 for same host as SQL Server, 2 for a different host, default 1; Windows Domain Auth only>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host>
    NR_CLI_MSSQL_WIN_ACCOUNT: <Windows domain account, DOMAIN\username; required only for Windows Domain Auth on a different host>
    NR_CLI_MSSQL_WIN_PASSWORD: <password for that account; required only for Windows Domain Auth on a different host>
    NR_CLI_MSSQL_GMSA_ACCOUNT: <gMSA account, DOMAIN\gMSAName$; gMSA only>
```

> #### 💡 TIP
>
> To enable debug logging, add `verbosity: "debug"` under `vars` in the playbook.

| Variable                                                 | Description                                                                                                                                                                                                                                                     | Default |
| -------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `NR_CLI_MSSQL_CONFIG_PRESET`                             | NRDOT configuration: 1 for Basic 2 for Advanced                                                                                                                                                                                                                 | `1`     |
| `NR_CLI_MSSQL_AUTH_MODE`                                 | SQL Server authentication: 1 for Windows Domain Auth 2 for gMSA                                                                                                                                                                                                 | `1`     |
| `NR_CLI_MSSQL_WINAUTH_LOCATION`                          | Windows Domain Auth only: 1 if the collector runs on the same host as SQL Server (the installing user's identity is used and the service stays LocalSystem, so no domain account is needed) 2 if it runs on a different host                                    | `1`     |
| `NR_CLI_MSSQL_INSTANCES_FILE`                            | Required. Path to the instances YAML file (`host`/`port` per instance), already present on the target host.                                                                                                                                                     | None    |
| `NR_CLI_MSSQL_WIN_ACCOUNT` / `NR_CLI_MSSQL_WIN_PASSWORD` | Required only when `NR_CLI_MSSQL_AUTH_MODE` is `1` and `NR_CLI_MSSQL_WINAUTH_LOCATION` is `2`. Windows domain account (`DOMAIN\username`) and password to grant permissions to and run the collector service as. Not needed for the same-host flow or for gMSA. | None    |
| `NR_CLI_MSSQL_GMSA_ACCOUNT`                              | Required if `NR_CLI_MSSQL_AUTH_MODE` is `2`. gMSA account (`DOMAIN\gMSAName$`) to grant permissions to.                                                                                                                                                         | None    |

> #### 💡 TIP
>
> To monitor more than one SQL Server instance from this collector, add more entries to the instances file. One auth mode and one Windows identity are used for **every** instance in the file. An instance that fails its checks is skipped with a logged reason rather than aborting the whole install. The same-host flow (`NR_CLI_MSSQL_WINAUTH_LOCATION` `1`) only works for instances on the target host itself. Use the different-host flow or gMSA for remote or clustered instances.

**AWS RDS - SQL Server Authentication**

This role monitors one or more RDS SQL Server endpoints from a single collector, using the same instances file / secrets file pattern as self-hosted (both files must already exist on the target host).

1.  Create the instances file on the target host, using the RDS endpoint as `host`, with just one entry if you're only monitoring a single RDS instance:

    ```yaml
    instances:
      - host: mydb1.abcdefg12345.us-east-1.rds.amazonaws.com
        port: 1433
        login_name: newrelic
    ```

2.  Create the secrets file on the target host with the RDS master username and password for each instance, indexed to match the instances file's order:

    ```bash
    NR_CLI_MSSQL_ADMIN_USER_1=admin
    NR_CLI_MSSQL_ADMIN_PASSWORD_1=YourMasterPassword1
    ```

Then create a file named `playbook.yml` and copy the following content into it. Set `targets` to `mssql-otel-rds` and replace the placeholder values with your own:

```yaml
- name: Install New Relic NRDOT for SQL Server on RDS
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel-rds
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host, using RDS endpoints as host>
    NR_CLI_MSSQL_SECRETS_FILE: <path to the secrets file (KEY=VALUE per instance) on the target host>
```

> #### 💡 TIP
>
> To enable debug logging, add `verbosity: "debug"` under `vars` in the playbook.

| Variable                      | Description                                                                                                                               | Default |
| ----------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `NR_CLI_MSSQL_CONFIG_PRESET`  | NRDOT configuration: 1 for Basic 2 for Advanced                                                                                           | `1`     |
| `NR_CLI_MSSQL_INSTANCES_FILE` | Required. Path to the instances YAML file, using RDS endpoints as `host`, already present on the target host.                             | None    |
| `NR_CLI_MSSQL_SECRETS_FILE`   | Required. Path to the secrets file (`KEY=VALUE`, one RDS master username/password pair per instance), already present on the target host. | None    |

> #### 💡 TIP
>
> To monitor more than one RDS SQL Server endpoint from this collector, add more entries to the instances file and matching numbered credentials to the secrets file. An instance that fails its checks is skipped with a logged reason rather than aborting the whole install.

**AWS RDS - Windows Domain Authentication or gMSA**

This target is only supported on a Windows collector host, and requires your RDS instances to already be joined to an AWS Managed Microsoft AD domain. It monitors one or more RDS SQL Server endpoints from a single collector using an **instances file** (YAML, `host`/`port` per instance, using RDS endpoints as `host`). Unlike the self-hosted target above, there's no "same host" option here: a domain account (or gMSA) is always required.

Create the instances file on the target host, using each RDS endpoint as `host`, with just one entry if you're only monitoring a single RDS instance:

```yaml
instances:
  - host: mydb1.xxxxxxxxxx.us-east-1.rds.amazonaws.com
    port: 1433
  - host: mydb2.xxxxxxxxxx.us-east-1.rds.amazonaws.com
    port: 1433
```

Then create a file named `playbook.yml` and copy the following content into it. Set `targets` to `mssql-otel-rds-winauth` and replace the placeholder values with your own:

```yaml
- name: Install New Relic NRDOT for SQL Server on RDS (Windows Auth)
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel-rds-winauth
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_AUTH_MODE: <1 for Windows Domain Auth, 2 for gMSA, default 1>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host, using RDS endpoints as host>
    NR_CLI_MSSQL_WIN_ACCOUNT: <Windows domain account, DOMAIN\username; Windows Domain Auth only>
    NR_CLI_MSSQL_WIN_PASSWORD: <password for that account; Windows Domain Auth only>
    NR_CLI_MSSQL_GMSA_ACCOUNT: <gMSA account, DOMAIN\gMSAName$; gMSA only>
```

> #### 💡 TIP
>
> To enable debug logging, add `verbosity: "debug"` under `vars` in the playbook.

| Variable                                                 | Description                                                                                                                                                    | Default |
| -------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `NR_CLI_MSSQL_CONFIG_PRESET`                             | NRDOT configuration: 1 for Basic 2 for Advanced                                                                                                                | `1`     |
| `NR_CLI_MSSQL_AUTH_MODE`                                 | SQL Server authentication: 1 for Windows Domain Auth 2 for gMSA                                                                                                | `1`     |
| `NR_CLI_MSSQL_INSTANCES_FILE`                            | Required. Path to the instances YAML file, using RDS endpoints as `host`, already present on the target host.                                                  | None    |
| `NR_CLI_MSSQL_WIN_ACCOUNT` / `NR_CLI_MSSQL_WIN_PASSWORD` | Required if `NR_CLI_MSSQL_AUTH_MODE` is `1`. Windows domain account (`DOMAIN\username`) and password to grant permissions to and run the collector service as. | None    |
| `NR_CLI_MSSQL_GMSA_ACCOUNT`                              | Required if `NR_CLI_MSSQL_AUTH_MODE` is `2`. gMSA account (`DOMAIN\gMSAName$`) to grant permissions to.                                                        | None    |

> #### 💡 TIP
>
> To monitor more than one RDS SQL Server endpoint from this collector, add more entries to the instances file. One auth mode and one Windows identity are used for **every** endpoint in the file. An endpoint that fails its checks is skipped with a logged reason rather than aborting the whole install.

## Run the playbook [#ansible-run]

```bash
ansible-playbook -i <inventory_file> <playbook_file>.yml
```

## Find and use your data [#find]

Once your data is being collected, you can access comprehensive SQL Server database monitoring through the New Relic UI.

To find your SQL Server database entity in New Relic:

1.  Go to **[one.newrelic.com](https://one.newrelic.com) > All capabilities > Databases**.
2.  From the **Entity type** dropdown, select **MSSQL instance**, then click **Apply**.
3.  Select your SQL Server database from the list of entities.

    After setting up SQL Server monitoring with NRDOT, you can:

    -   [Create custom dashboards](https://docs.newrelic.com/docs/query-your-data/explore-query-data/dashboards/introduction-dashboards/) to visualize your database metrics
    -   [Set up alerts](https://docs.newrelic.com/docs/alerts/create-alert/create-alert-condition/alert-conditions/) for critical database performance thresholds
    -   [Explore your data](https://docs.newrelic.com/docs/query-your-data/explore-query-data/browse-data/introduction-data-explorer/) using New Relic query capabilities

## Related documentation [#related-docs]

[Set up APM-database correlation](https://docs.newrelic.com/docs/opentelemetry/db360/capabilities/db-apm)

Learn how to correlate your application performance with database operations in New Relic.

[Troubleshooting](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/troubleshooting)

Learn how to troubleshoot your MSSQL monitoring setup in New Relic.

[Metrics reference](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/metrics-reference)

Learn about the available metrics collected by the NRDOT Collector.