Monitoring Azure SQL db with Telegraf and Influx

0
votes

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?

2
sql-serverazureazure-sql-databaseinfluxdbtelegraf

2 Answers

0
votes

If not already, you will need to grant view definition at the database level. (without the ANY keyword)

GRANT VIEW DEFINITION TO user_name

Refer: SQL Server Permissions

As per Microsoft SQL Server Plugin ID: inputs.sqlserver Telegraf 0.10.1+ AAD based auth is only supported for Azure SQL Database and Azure SQL Managed Instance (but not for SQL Server), as described here.

 servers = [
    "Server=<Azure_SQL_Server_Name>.database.windows.net;Port=1433;Database=<Azure_SQL_Database_Name>;app name=telegraf;log=1;",
  ]
  auth_method = "AAD"

Additional Azure AD authentication options available are Active Directory Universal Authentication for SQL Server Management Studio connections including Multi-Factor Authentication and Conditional Access.

Note: we have telegraf v1.19.1 [2021-07-07] released this 8 hours ago and among its other bugfixes, this seems to be addressed: #9388 inputs.sqlserver Require authentication method to be specified

But from earlier discussion on github for telegraf release 1.10.3, this works without crashing when connected as the super user (the one who created the whole server). It still fails when connecting as a less privileged user.

Other issues reported in GitHub:

0
votes

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"]