SQL Server user permissions needed for OpenTelemetry collector sqlserverreceiver to get default metrics

[divyankm]

Component(s)

receiver/sqlserver

Describe the issue you're reporting

I have an otel collector v. 1.3 and file like this:

  receivers:
    sqlserver:
      collection_interval: 30s
      server: "your-sqlserver-host"       # Use 'server' instead of 'endpoint'
      port: 1433                          # Port is separate
      username: "${SQLSERVER_USER}"
      password: "${SQLSERVER_PASSWORD}"
      # enable_query_sampling: true         # Optional, defaults to false
      # max_rows_per_query: 50              # Optional, default is 100
    # sqlserver/1: # If multiple MSSQL Servers in Single Windows Instance
    hostmetrics:
      collection_interval: 30s
      scrapers:
        cpu:
        memory:
    # windowseventlog: #  Uncomment Only If > Configure if Windows Logs are required
    #       channel: application,system,security
exporters:
  otlphttp/mimir-ms: #mimir-microservice
    endpoint: < >
    headers:
      "X-Scope-OrgID": "primary"
    tls:
      insecure: true

service:
  pipelines:
    metrics:
      receivers: [ sqlserver,hostmetrics ]
      exporters: [ otlphttp/mimir-ms ]
    # logs: #  Uncomment Only If > Configure if Windows Logs are required
    #   receivers: [ windowseventlog ]
    #   exporters: [ otlphttp/loki ]

I have created SQL Server user otel_collector_readonly and granted these permissions:

-- Let the user connect to SQL Server
GRANT CONNECT SQL TO <username>;
-- Let the user see all databases
GRANT VIEW ANY DATABASE TO <username>;
GRANT VIEW ANY DEFINITION TO <username>;
GRANT VIEW ANY PERFORMANCE TO <username>;
-- Required: Allow access to system-level DMVs and performance counters
GRANT VIEW SERVER STATE TO <username>;

Currently I'm seeing only one metric sqlserver.lock.wait.rate appearing in SQL Server.

Which permissions are required to get all metrics as per link: https://github.com/open-telemetry/opentelemetry-collector-contrib/blob/main/receiver/sqlserverreceiver/documentation.md#default-metrics

While I add non default metrics in otel file as enabled, these metrics coming in prometheus.

    metrics:
      sqlserver.database.count:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.resource_pool.disk.throttled.read.rate:
        enabled: true
      sqlserver.resource_pool.disk.throttled.write.rate:
        enabled: true

but default metrics not coming in prometheus.

https://stackoverflow.com/questions/78906680/what-sql-server-user-permissions-are-required-for-opentelemetry-collector-sqlser

[github-actions]

Pinging code owners:

• receiver/sqlserver: @StefanKurek @sincejune @crobert-1

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[crobert-1]

I tested using SQL Server 2022, and it worked as expected. I created a new user, and did GRANT VIEW SERVER PERFORMANCE STATE TO <USER>, then used the basic receiver configuration with the debug exporter:

  sqlserver:
    password: <password>
    port: 1433
    server: 0.0.0.0
    username: <username>

I'm getting the following metrics:

     -> Name: sqlserver.batch.request.rate
     -> Name: sqlserver.batch.sql_compilation.rate
     -> Name: sqlserver.batch.sql_recompilation.rate
     -> Name: sqlserver.lock.wait.rate
     -> Name: sqlserver.page.buffer_cache.hit_ratio
     -> Name: sqlserver.page.life_expectancy
     -> Name: sqlserver.user.connection.count

That is the full list of default metrics that are available by direct connection.

Can you test using the debug exporter?

[divyankm]

Thanks @crobert-1 for quick response.

I was using 2019 may be due to which default metrics did not came.

Now I tested on 2022 SQLEXPRESS, I am seeing some metrics are failing may be because I'm using SQLEXPRESS instead of SQLSERVER Edition.

2025-07-01T21:40:29.674+0530 warn sqlserverreceiver@v0.127.0/scraper_windows.go:64 failed to create perf counter with path \SQLServer:General Statistics\User Connections: The specified object was not found on the computer.
{"resource": {}, "otelcol.component.id": "sqlserver", "otelcol.component.kind": "receiver", "otelcol.signal": "metrics"}

metrics list mssql default.txt

I feel we need to add commands to run for reciever/mssqlserver to make users aware about the privileges to grant to mssql DB user in this github doc

 GRANT VIEW SERVER PERFORMANCE STATE TO <USERNAME>