[Metricbeat][MSSQL] Docs clarifications for credentials and permissions

[kostasb]

Docs enhancement requests for the MSSQL module.

1. The default connection string in mssql.yml is
   hosts: ["sqlserver://sa@localhost"]
   The module's documentation could clarify that credentials can be passed along in the form of
   sqlserver://adminusername:adminpassword@hostname

2. It would be useful to provide some SQL-side recommendations for the minimal permissions required for the configured database user to query the endpoints necessary for extracting the metricsets.

[rbudiraharjo]

You could also use:

hosts: ["sqlserver://localhost"]
username: user
password: pass

[andresrc]

cf. #12267

[alekniknik]

Hi,

I was investigating permissions and you need next privileges:

use master
go
GRANT VIEW SERVER STATE TO [databaseuser]
GO

and public permision for all user database on server you can run this script or manuel exec this for all database create user [databaseuser] for login [databaseuser];
use master
go
DECLARE @dbname VARCHAR(50)
DECLARE @Statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Statement = 'use '+@dbname +';'+ 'CREATE USER [databaseuser]
FOR LOGIN [databaseuser];'

exec sp_executesql @Statement

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

This mssql module work on MSSQL 2017 in previous version some part is not working (sys.dm_db_log_stats not exist in prev version)

Best regards;

[botelastic]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.