What grants are needed for the SQL Server Telegraf plugin in Azure SQL Database

Question

I'm using the Telegraf input plugin for SQL Server (https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver) to gather metrics and report to InfluxDB. It works well for SQL Server, but though it supports Azure SQL Database the documentation is a bit sparse.

The database user should be created like this:

CREATE LOGIN [telegraf] WITH PASSWORD = N'password';
GRANT VIEW SERVER STATE TO [telegraf];
GRANT VIEW ANY DEFINITION TO [telegraf];

That works on SQL Server, but in Azure it fails:

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

I wonder what I need to grant instead in order to solve this in the best possible way. We have a large number of databases running on the same server in an elastic pool, so if it is possible I would like to use a single user that logs in to the master and collects metrics for all the databases at once (the way it works with SQL Server). If that is impossible I can configure multiple logins and process one database at a time.

Perhaps I can grant VIEW DEFINITION at the database level, but VIEW SERVER STATE does not seem to be supported at all.

So, how should I configure the SQL Database login(s) for Telegraf with the SQL Server plugin to make it work?

EDIT:

• Running as the super user for the server works without errors, but only produces metrics for master and tempdb. I need metrics for the many application databases and they are missing. Plus running as the super user is less than ideal.
• Running as the super user for the server but connecting to a specific application database (add database in connection string) crashes with a nil pointer dereference and the log complains about VIEW DATABASE STATE permission denied in database master (the super user has access, but apparently not when connecting to a spefic database).
• Granting VIEW DATABASE and VIEW DEFINITION to telegraf in an application database and connecting directly to that database as telegraf crashes with a nil pointer dereference and the log says the connection was closed.

EDIT 2:

Created bug report https://github.com/influxdata/telegraf/issues/4222.

EDIT 3:

As of the latest release the plugin works if the server admin account is used, so the issue has been solved. There is still no way to run with a less privileged account in Azure DB.

Answer 1

The answer: GRANT VIEW SERVER STATE is not supported in Azure SQL Database.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. Permissions can not be granted in Master, but the views can be queried in user databases. On SQL Database Standard and Basic Tiers requires the SQL Database server admin account due to security requirements following from multi tenancy of those tiers.

Reason: SQL Azure SQL is PaaS solution, therefore the most "server" specific features, DMVs, settings are blocked by purpose

References:

Grant View Server State - is it possible for a none SA user to have in Azure SQL?

SQL Azure VIEW DATABASE STATE permission denied in database 'master'

Possible workaround: (which is, anyway does not work in ewramner case)

CREATE LOGIN [telegraf] WITH PASSWORD = N'password';

USE [yourDB]
GRANT VIEW DEFINITION TO [telegraf];
GRANT VIEW DATABASE STATE TO [telegraf];

Therefore, (IMHO), there is no way to make such application working in SQL Azure without changing application code