Monitoring Azure SQL db with Telegraf and Influx

Question

We are moving into better mechanisms to monitor our environment and we started setting up Influx + Telegraf + Grafana.

We have Azure Monitor connected to Grafana and we can see a lot of details there, but we would like to go one level deeper and get a better understanding of how our SQL is used.

I've followed the steps described here - https://github.com/influxdata/telegraf/blob/release-1.14/plugins/inputs/sqlserver/README.md.

But script is failing with next result:

Securable class 'server' not supported in this version of SQL Server.

Tried to connect to SQL server with our admin account, that has all permissions and logstash is showing next results:

E! [inputs.sqlserver] Error in plugin: Script VolumeSpace failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script AzureDBPerformanceCounters failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script DatabaseIO failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script AzureDBPerformanceCounters failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script ServerProperties failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script WaitStatsCategorized failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script Cpu failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script PerformanceCounters failed: Login error: mssql: Login failed for user 'xxx_user'.
E! [inputs.sqlserver] Error in plugin: Script MemoryClerk failed: Login error: mssql: Login failed for user 'xxx_user'.

Could not find any issues in GitHub related to that. Is there something that I'm missing?

Did anyone succeed to connect these 2 services?

Answer 1

I was able to implement monitoring via installing a template dashboard for Influx Azure SQL monitoring - https://github.com/influxdata/community-templates/tree/master/azure_sql_db.

After installing the template, I went to the Influx UI Data menu and Telegraf tab. Over there, I've copied configurations and with a proper formatting connection string, everything started working. YOUR_USER - admin user YOUR_PASSWORd - admin user password

Here is my connection string template:

Server={YOUR_DB_LINK};Port=1433;Database={YOUR_DATABASE};User Id={YOUR_USER};Password={YOUR_PASSWORD};app name=telegraf;log=1;Initial Catalog=demo;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30

My Telegraf configuration:

[[outputs.influxdb_v2]]
  urls = ["${INFLUX_URI}"]
  token = "${INFLUX_TOKEN}"
  organization = "{YOUR_ORG}"
  bucket = "AzureSQLDB"
  namepass = ["sql*"]
[[inputs.sqlserver]]
  servers = ["$AZURE_SQL_CONNECTION_STRING",]
  query_version = 2
  database_type = "AzureSQLDB"
  include_query = ["AzureSQLDBResourceStats","AzureSQLDBWaitStats","AzureSQLDBDatabaseIO","AzureSQLDBMemoryClerks","AzureSQLDBPerformanceCounters","SQLServerPerformanceCounters"]