SQL Server user permissions needed for OpenTelemetry collector sqlserverreceiver

Question

I have an otel collector 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.

Divyank · Accepted Answer · 2025-07-01 16:28:39Z

0

for Otel Collector, `receiver/sqlserver`:

Pls make sure to Use MSSQL V: 2022, I was using MSSQL Server Version 2019.

Pls Use SQLSERVER, instead of SQLEXPRESS Edition.

Pls provide MSSQL User Privileges as below:

 GRANT VIEW SERVER PERFORMANCE STATE TO <USERNAME>

For reference check this Github Issue: https://github.com/open-telemetry/opentelemetry-collector-contrib/issues/40937

answered Jul 1 at 16:28

Divyank

1,0772 gold badges16 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server user permissions needed for OpenTelemetry collector sqlserverreceiver

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related