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.

Trembly

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

Hyetography

That is true, but the application (Telegraf sqlserver plugin) has already been changed and should support Azure. There is a specific option for it (azuredb = true). Presumably that has been tested and works, but they have not documented how to setup the user. – Trembly

@ewramner, what is the current database tier in use? I've checked a source code of the tool and indeed, it supports Azure SQL, but special parameter to be used - "azuredb = true." It relies on a DMV sys.dm_db_resource_stats and such DMV requires "VIEW DATABASE STATE" permissions, which you already have tried – Hyetography

It is standard tier. You can see the Azure CLI commands (slightly edited) in the linked issue. The parameter is set to true. – Trembly

Since you have a standard tier, you can run that application using a "server admin" login. In your case required permissions cannot be granted to login [telegraf]. Next to that, you cannot create another Server admin account, or rename existing one – Hyetography

I see what you mean. Nasty considering the price difference. I'll see if I can test on premium. Still, this fails even with the server admin account, or at least it shows data only for master/tempdb and not for the application databases. – Trembly

Unfortunately premium doesn't help. See linked issue for details, but in essence it behaves more or less like standard. Same errors. – Trembly

With the latest telegraf release running as server admin works, though I really don't like using that account from a monitoring agent. – Trembly