OpenTelemetry Collector SQLServerReceiver权限及默认指标缺失问题

配置OpenTelemetry Collector的SQL Server接收器后，仅能采集到sqlserver.lock.wait.rate这一个默认指标，手动启用的非默认指标可正常采集，但其余默认指标无法在Prometheus中显示。已创建otel_collector_readonly用户并授予部分权限，需补充哪些权限才能获取全部默认指标？

已配置的Collector文件 #

receivers:
  sqlserver:
    collection_interval: 30s
    server: "your-sqlserver-host"       # 使用'server'而非'endpoint'
    port: 1433                          # 端口单独配置
    username: "${SQLSERVER_USER}"
    password: "${SQLSERVER_PASSWORD}"
    # enable_query_sampling: true         # 可选，默认false
    # max_rows_per_query: 50              # 可选，默认100
  # sqlserver/1: # 单Windows实例中有多个MSSQL服务器时启用
  hostmetrics:
    collection_interval: 30s
    scrapers:
      cpu:
      memory:
  # windowseventlog: # 仅当需要Windows日志时取消注释并配置
  #       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: # 仅当需要Windows日志时取消注释并配置
    #   receivers: [ windowseventlog ]
    #   exporters: [ otlphttp/loki ]

已授予的SQL Server权限 #

-- 允许用户连接SQL Server
GRANT CONNECT SQL TO <username>;
-- 允许用户查看所有数据库
GRANT VIEW ANY DATABASE TO <username>;
GRANT VIEW ANY DEFINITION TO <username>;
GRANT VIEW ANY PERFORMANCE TO <username>;
-- 必要权限：允许访问系统级DMV和性能计数器
GRANT VIEW SERVER STATE TO <username>;

手动启用的非默认指标（可正常采集） #

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

核心权限补充 #

你已授予的VIEW SERVER STATE是大部分默认指标的基础，但部分数据库级默认指标还需要目标数据库的VIEW DATABASE STATE权限。可通过以下SQL批量授予所有在线数据库的该权限：

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'GRANT VIEW DATABASE STATE TO ' + QUOTENAME('otel_collector_readonly') + N';' + CHAR(13) + CHAR(10)
FROM sys.databases
WHERE state = 0; -- 仅针对在线数据库
EXEC sp_executesql @sql;

各默认指标权限明细 #

验证步骤 #

1. 执行权限授予语句后，重启OpenTelemetry Collector
2. 等待1-2个采集周期（约1分钟），检查Prometheus指标是否齐全
3. 若仍有缺失，查看Collector日志，定位被拒绝访问的DMV，针对性补充权限

内容的提问来源于stack exchange，提问作者Divyank