---
title: Install & configure NRDOT for MSSQL on Windows self-hosted: SQL auth
source: https://docs.newrelic.com/docs/opentelemetry/db360/mssql/windows-hosted-sql
---

Set up Microsoft SQL Server monitoring using the NRDOT Collector on Windows self-hosted environments including physical servers, virtual machines, and standalone Windows installations, using SQL Server authentication. To monitor in a Windows RDS environment, refer to [Windows RDS instrumentation](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/windows-rds-sql).

To install this through the New Relic UI instead, go to  **[one.newrelic.com](https://one.newrelic.com) > Integrations & Agents > MSSQL (OpenTelemetry)** . Follow the on-screen instructions to set up the integration. This page covers manual installation only.

This page covers SQL Server authentication. If your environment uses a different authentication method, see [Windows Domain Authentication](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/windows-hosted-domain) or [gMSA](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/windows-hosted-gmsa).

## Prerequisites [#prerequisites]

Before monitoring your Microsoft SQL Server with NRDOT, make sure your environment meets these requirements:

-   A New Relic account with a valid [license key](https://docs.newrelic.com/docs/apis/intro-apis/new-relic-api-keys/#ingest-license-key).
-   Network connectivity to the [New Relic OTLP endpoint](https://docs.newrelic.com/docs/opentelemetry/best-practices/opentelemetry-otlp) for your region.
-   SQL Server requirements:
    -   SQL Server 2017 or later.
    -   Administrative access to SQL Server (`sysadmin` role or equivalent).
    -   Network connectivity between the collector and SQL Server on port `1433` or a custom port.
    -   SQL Server Management Studio (SSMS) or the `sqlcmd` utility.
    -   Windows domain or SQL Server authentication.

## Install NRDOT Collector [#setup-sql]

Download and install the NRDOT Collector using PowerShell:

```bash
[Net.ServicePointManager]::SecurityProtocol = 'tls12, tls'; $NRDOT_VERSION = (Invoke-RestMethod -Uri "https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest").tag_name; $WebClient = New-Object System.Net.WebClient; $WebClient.Headers.Add("User-Agent", "Mozilla/5.0"); $WebClient.DownloadFile("https://github.com/newrelic/nrdot-collector-releases/releases/download/$NRDOT_VERSION/nrdot-collector_${NRDOT_VERSION}_windows_x64.msi", "$env:TEMP\nrdot-collector.msi"); Start-Process msiexec.exe -ArgumentList "/i `"$env:TEMP\nrdot-collector.msi`" /qn /norestart /L*V `"$env:TEMP\nrdot_install.log`"" -Wait; Get-Service nrdot-collector -ErrorAction SilentlyContinue
```

## Create monitoring user [#user-sql]

Run this script as root `user/sysadmin` user to create the `newrelic` monitoring user and grant the necessary permissions for collecting SQL Server metrics.

In your SQL Server Management Studio (SSMS), run the following script to create the `newrelic` monitoring user. Replace `<YOUR_PASSWORD>` with your desired password:

```sql
USE [master];
GO
CREATE LOGIN [newrelic] WITH PASSWORD = '<YOUR_PASSWORD>';
GO

GRANT VIEW SERVER STATE TO [newrelic];
GRANT VIEW ANY DEFINITION TO [newrelic];
GRANT VIEW ANY DATABASE TO [newrelic];
GO

DECLARE @name SYSNAME;
DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution')
AND [state] = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    EXEC('USE [' + @name + '];
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''newrelic'')
    BEGIN
      CREATE USER [newrelic] FOR LOGIN [newrelic];
    END;
    GRANT VIEW DATABASE STATE TO [newrelic];');
  END TRY
  BEGIN CATCH
    PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
  END CATCH
  FETCH NEXT FROM db_cursor INTO @name;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
```

> #### 💡 TIP
>
> To securely manage sensitive information, such as database credentials, store them in [secret management](https://docs.newrelic.com/docs/opentelemetry/db360/capabilities/db-apm/#secret-management) tools.

## Configure NRDOT Collector [#configure-sql]

1.  Create a configuration file named as `mssql-config.yaml` in PowerShell as an Administrator:

    ```bash
    New-Item -Path "C:\Program Files\nrdot-collector\mssql-config.yaml" -ItemType File
    ```

2.  Add your environment-specific values to the following configuration and copy it to the `mssql-config.yaml` file created in the previous step:

    > #### 💡 FULL CONFIGURATION
    >
    > This configuration focuses on essential database monitoring for your SQL Server environment. To enable comprehensive monitoring with all available metrics, refer to the [configuration reference](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/config-reference/#hosted-sql).

> #### 💡 TIP
>
> -   The `logs` pipeline doesn't carry collector debug or internal agent logs. It transmits SQL Server query telemetry, including query text, execution plans, and sample data. The `nrsqlserver` receiver captures this telemetry through `db.server.query_sample`, `db.server.top_query`, `db.server.query_plan`, and `db.server.top_procedure` events emitted as OpenTelemetry log records.
>
> -   The `db.server.top_procedure` event requires SQL Server 2017 CU3 or later, which introduced support for `sys.dm_exec_procedure_stats.total_spills`. On earlier versions, this event doesn't emit data and logs an error. Other events remain unaffected.
>     Accessing `sys.dm_exec_procedure_stats` requires `VIEW SERVER STATE` (or `VIEW SERVER PERFORMANCE STATE`, depending on the SQL Server version). Additionally, `VIEW ANY DEFINITION` is required for `OBJECT_NAME` and `OBJECT_SCHEMA_NAME` to resolve correctly across all user databases, and `CONNECT SQL` is needed to set up initial sessions. The monitoring user created in [Create monitoring user](#user-sql) includes all 3 permissions.

## Update Windows Service Registry [#update-sql]

1.  Update the Windows Service Registry to use the new `mssql-config.yaml` file using one of the following methods:

    -   **For PowerShell:** Run the following command as Administrator:

        ```bash
        Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\nrdot-collector" -Name "ImagePath" -Value '"C:\Program Files\nrdot-collector\nrdot-collector.exe" --config "C:\Program Files\nrdot-collector\mssql-config.yaml"'
        ```

    -   **For Command Prompt:** If PowerShell isn't available, use Command Prompt instead and run the following command as Administrator:

        ```bash
        sc config "nrdot-collector" binPath= "\"C:\Program Files\nrdot-collector\nrdot-collector.exe\" --config \"C:\Program Files\nrdot-collector\mssql-config.yaml\""
        ```

2.  Validate the NRDOT Collector configuration to ensure it's correctly formatted and will work properly.

    ```bash
    & "C:\Program Files\nrdot-collector\nrdot-collector.exe" validate --config="C:\Program Files\nrdot-collector\mssql-config.yaml"
    ```

    > #### 💡 TIP
    >
    > To correlate your application performance with database operations, you can set up database service identification. For more information, refer to [set up database service identification](https://docs.newrelic.com/docs/opentelemetry/db360/capabilities/db-apm/).

## Restart NRDOT Collector [#restart-sql]

After updating your configuration, restart the NRDOT Collector service:

```bash
net stop nrdot-collector
net start nrdot-collector
```

> #### 💡 TIP
>
> Always restart the NRDOT Collector service after making configuration changes to ensure the new settings take effect.

## Find and use your data [#find]

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

To find your SQL Server database entity in New Relic:

1.  Go to **<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 Windows 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.