SQL Server plugin only supports super user with AzureDB

[erik-wramner]

Feature Request

Support running telegraf with SQL Database with another user than the super-user

Proposal:

Make the SQL Server plugin fail gracefully on queries it does not have access to or add config options for turning off queries that require the super user account and make it possible to use it as a regular user. Even the database owner account for a given database would be better than the super user!

Current behavior:

If the SQL Server plugin tries to gather metrics from a SQL Database with a user that is not the super user (dbo is not enough; this is the user that created the server and that has full rights to the entire system, all databases, everything) it fails. The log contains errors like:

2019-05-06T13:27:01Z E! [inputs.sqlserver]: Error in plugin: mssql: The user does not have permission to perform this action.
There are some metrics that can be collected as another user, but as the plugin fails when it tries to access the restricted views they cannot be used either.

Desired behavior:

Fail gracefully on the queries that the user does not have access to and output metrics for the queries that do work. Alternatively add a configuration option that disables all the queries that require the super user and run only the ones that work for a regular user. Also document the grants that are needed in order to use the plugin with SQL Database. Some of the queries work with dbo, but it is better to create a telegraf user and grant only the bare minimum rights.

Use case:

We cannot use telegraf for instrumenting SQL Database because the risk of putting the super user id and password in the telegraf configuration files are simply too high. We would very much like to use this, but you don't do monitoring by connecting with root/sysdba/sa. It is simply too dangerous.

[danielnelson]

Would you be able to use the exclude_query option to run the queries one at a time and determine what permissions each query requires? We could then document the requirements and also we should log which query is running when an error occurs.

[erik-wramner]

Yes, but unfortunately it did not turn out well. I started by excluding all queries (as far as I could determine by looking at the source). Config:

query_version = 2
azuredb = true
exclude_query = [ 'PerformanceCounters', 'WaitStatsCategorized', 'DatabaseIO', 'ServerProperties', 'MemoryClerk']

Result:

PS C:\Programs\t2> .\telegraf -config taz2.conf -test
.\telegraf : 2019-05-08T16:33:30Z I! Starting Telegraf 1.10.3
At line:1 char:1
+ .\telegraf -config taz2.conf -test
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (2019-05-08T16:3...Telegraf 1.10.3:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
> sqlserver_azurestats,host=sql01,solution=test,sql_instance=sql01 avg_cpu_percent="0.12",avg_data_io_percent="0.00",avg_log_write_percent="0.16",avg_memory_usage_percent="38.47",dtu_limit=300i,
max_session_percent="0.09",max_worker_percent="0.33",xtp_storage_percent="0.00" 1557333211000000000

[erik-wramner]

Note that if I connect as the super user I do not get the exception.

[danielnelson]

There is one more query in your case: AzureDB. If you add this the plugin should do nothing, not even connect to the database.

This is going to reveal how clueless I am about Windows, but what does this mean? Is it just reporting that the program exited with a non-zero status code?

> At line:1 char:1
+ .\telegraf -config taz2.conf -test
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (2019-05-08T16:3...Telegraf 1.10.3:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

[erik-wramner]

Well, it is actually quite odd. PowerShell logs the error above. If I run the command from a normal command prompt there is no error logged. I'll do the additional tests from the command prompt. Note that the command prompt (and PowerShell) hangs, I expected them to run once and exit as I pass "-test"? Has that changed?

• AzureDB: seems to work (though error above generated when called from PowerShell)
• Memory clerk: access denied
• Server properties: nothing logged, but no output?
• DB IO: access denied
• Wait stats: access denied
• Perf counters: access denied

In summary it seems everything but the AzureDB query gives access denied. Too bad.

[danielnelson]

Not exiting is probably the issue I fixed in #5631, in the meantime I thit it will help if you run with --console.

These results are all when running under the dbo account?

[erik-wramner]

Good, I'll use --console next time. All tests were with the dbo account. It should be possible to grant the access for the single query that worked to other users, though.

[danielnelson]

I looked at the DatabaseIO query and it appears to select from the sys.dm_io_virtual_file_stats table. Searching through the SQL Server docs I found:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017#permissions

Following the link I see:

Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.

Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

Can you check to see if the dbo account has these permissions?

[erik-wramner]

As I recall VIEW SERVER STATE cannot be granted in Azure, it is only available to the super user. VIEW DATABASE STATE on the other hand can be granted and is granted to dbo.

[danielnelson]

@erik-wramner Can you try out these user setup instructions from #6111?

 CREATE USER [telegraf] WITH PASSWORD = N'mystrongpassword';
 GO
 GRANT VIEW DATABASE STATE TO [telegraf];
 GO
 ```

[erik-wramner]

I'll upgrade to the latest version and try it out, but it will take some time. I'm on vacation so I don't have access to the environments right now.

[erik-wramner]

I actually needed one additional command:

1. create login telegraf with password = 'mystrongpw' (serverwide)
2. create user telegraf for login telegraf (for specific db)
3. grant view database state to telegraf (for specific db)

With that and the latest nightly build (as of Today) it worked. I got some output and one error in the logs:

2019-08-12T15:24:02Z E! [inputs.sqlserver]: Error in plugin: mssql: The user does not have permission to perform this action

Not sure what caused the error, but there was no crash and some output with a normal user! Clearly a step forward.

[erik-wramner]

Actually I'm stupid. Excluding Schedulers (as was done in #6111) removes the error.

[erik-wramner]

Looking forward to implement this when it is released!

[bcboy84]

@erik-wramner Could you please advise if this issue is fixed? I am getting error message when I use a non super user for Azure SQL DB. I am using the template from here: https://github.com/influxdata/community-templates/tree/master/azure_sql_db
However, everything works when I use super user. Could you please advise how you were able to overcome this? I didn't quite get it what you mentioned in the end.

VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.

[erik-wramner]

Well, it was working in 2019. Unfortunately we are no longer using this, but run custom SQL scripts from bash. That way we can get application-specific metrics as well. Perhaps the permission you mention is new? I had to exclude schedulers:

exclude_query = [ 'Schedulers' ]
I think I had to connect to the individual databases as well and not to the server as a whole, but I'm not sure. Good luck!

[bcboy84]

@erik-wramner Thank you for your reply. There are some new permissions we can grant on Azure SQL DB logical server. Refer link1, link2

Once I granted the telegraf access to these server roles, the plugin started to work as expected with neat dashboards