Security

DBA Dash service folder & Config file #

Please ensure that you secure access to the folder where the DBA Dash service runs. Limit who can access the computer where the service account runs and also secure access to the folder using NTFS permissions.

Why? If someone has permissions to the service folder it’s possible they could elevate their permissions by replacing the service executable. It also provides a layer of protection for the ServiceConfig.json file.

Note: The service account should have read/write access to the folder.

ServiceConfig.json #

The application configuration is stored in a ServiceConfig.json file. This file can contain sensitive data such as passwords and you also don’t want unauthorized users to be able to manipulate this file.

• Enable config file encryption (Starting 2.41.0).
• Use a strong password to encrypt the config.
• Avoid storing sensitive information in the config file if possible.

e.g. Use Windows authentication where possible. Use IAM roles to provide access to S3 buckets instead of access keys

• Limit access to the config file.
• Use the principal of least privilege.

Service Account #

The recommended approach is to grant a minimal set of permissions to the service account. The optional WMI collections require membership of the local administrators group on the monitored instances. The only way this works without local admin access is to run a copy of the DBA Dash service locally on each monitored instance. Please review what’s provided through the WMI collections and decide if you want to use it in your environment. If you don’t want to use WMI, click the option to disable WMI collections when adding the instance to avoid WMI related errors in the log.

Alternatively, you can grant membership of the sysadmin role which will allow some additional information to be collected over the SQL connection. See the why sysadmin section for more info.

An Active Directory user account is recommended as it will allow you to use Windows authentication, avoiding the need to store passwords in the config. Ideally a managed service account should be used which will automatically set and rotate passwords for the service account.

Install-Module dbatools
Install-WindowsFeature RSAT-AD-PowerShell

Get-KdsRootKey
<# Run if needed:
# This command will take time to replicate.  See MS docs for more info.
Add-KdsRootKey -EffectiveImmediately
#>

## Replace the path as required.  e.g. Domain is mydomain.com - Path =  CN=Users,DC=mydomain,DC=com or OU=MyOU,DC=mydomain,DC=com
New-ADGroup -Name "DBADashGrp" -SamAccountName DBADashGrp -GroupCategory Security -GroupScope Global -DisplayName "DBADashGrp" -Path "CN=Users,DC=UPDATE_PATH_AS_REQUIRED,DC=local" -Description "Managed Service Account Group for DBA Dash"

Add-ADGroupMember -Identity "DBADashGrp" -Members "MACHINE_NAME_WHERE_DBADASH_RUNS$"

## Replace the DNSHostName as required.  e.g. Domain is mydomain.com - use DBADash.mydomain.com
New-ADServiceAccount -name DBADash -DNSHostName DBADash.UPDATE_PATH_AS_REQUIRED.local -PrincipalsAllowedToRetrieveManagedPassword DBADashGrp

## Option to add new service account to local admins to get WMI calls to work
Invoke-Command -ComputerName SQL1,SQL2 -ScriptBlock {Add-LocalGroupMember -Group "Administrators" -Member "DBADash$" }

## Create login for service account on monitored instances
New-DbaLogin -SqlInstance SQL1,SQL2 -Login "YOURDOMAINNAME\DBADash$"

## Add user to sysadmin group - or provision the minimum permissions as described in the security doc
Add-DbaServerRoleMember -SqlInstance SQL1,SQL2 -ServerRole sysadmin -Login "YOURDOMAINNAME\DBADash$"

Install-ADServiceAccount DBADash

ALTER SERVER ROLE [sysadmin] ADD MEMBER [{LoginName}]

Firewall #

DBA Dash collects most of it’s data via a SQL connection (Typically port 1433). If you check the “No WMI” box when adding a connection then ALL data will be collected via the SQL connection and no additional firewall configuration would be required.

Starting with version 2.24.2, DBA Dash uses WSMan/WinRM protocol for WMI data collections which uses port 5985. On servers WinRM should be enabled by default. If you need to enable it manually, run this on the monitored instance:

Enable-PSRemoting -SkipNetworkProfileCheck -Force

This does a number of tasks including creating the firewall exception (port 5985). See here for more information on WMI collections.

Running with Minimal Permissions #

If you don’t want to grant sysadmin access, you can assign the permissions listed below instead

Server Level Permissions:

• View Server State
• View Any Database
• Connect Any Database
• Alter Event Session (For Slow Query trace if used)
• View Any Definition

MSDB Database:

• Add user to the db_datareader role.
• Add user to the SQLAgentReaderRole role

This script can be used to provision the required permissions:

/*
	Use this script to configure permissions for the DBA Dash service account if you don't want to use the sysadmin server role.
	DBA Dash can collect more data when running as sysadmin but most features and functionallity will work with a more limited account
	See here for details: https://github.com/trimble-oss/dba-dash/edit/main/Docs/Security.md

	On the destination connection the service will need to be a member of db_owner role on the repository database
	To allow the service to create the repository database you can use:
	GRANT CREATE ANY DATABASE TO {LoginName}
*/
DECLARE @LoginName SYSNAME = 'DBADashService' /* !!!! Replace with your own service login !!!! */
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
GRANT VIEW SERVER STATE TO ' + QUOTENAME(@LoginName) + '
GRANT VIEW ANY DATABASE TO ' + QUOTENAME(@LoginName) + '
GRANT CONNECT ANY DATABASE TO ' + QUOTENAME(@LoginName) + '
GRANT VIEW ANY DEFINITION TO ' + QUOTENAME(@LoginName) + '
GRANT ALTER ANY EVENT SESSION TO ' + QUOTENAME(@LoginName) + ' /* Required if you want to use slow query capture */
USE [msdb]
IF NOT EXISTS(SELECT * 
			FROM msdb.sys.database_principals
			WHERE name = ' + QUOTENAME(@LoginName,'''') + ')
BEGIN
	CREATE USER ' + QUOTENAME(@LoginName) + ' FOR LOGIN ' + QUOTENAME(@LoginName) + '
END
ALTER ROLE [db_datareader] ADD MEMBER ' + QUOTENAME(@LoginName) + '
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER ' + QUOTENAME(@LoginName) + '
'
PRINT @SQL
EXEC sp_executesql @SQL

Repository Database Permissions #

The service will also need db_owner permissions to the repository database. The repository database is created by clicking the “Deploy/Update Database” button in the service configuration tool, otherwise it’s created when the service starts. To allow the service account to create the repository database you can use:

DECLARE @LoginName SYSNAME = 'DBADashService' /* !!!! Replace with your own service login !!!! */
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
GRANT CREATE ANY DATABASE TO ' + QUOTENAME(@LoginName) 
PRINT @SQL
EXEC sp_executesql @SQL

Or to grant the permissions after creating the repository database:

DECLARE @LoginName SYSNAME = 'DBADashService' /* !!!! Replace with your own service login !!!! */
DECLARE @RepositoryName SYSNAME = 'DBADashDB' /* !!!! Replace with your own Repository Database Name (default:DBADashDB) !!!! */
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
USE ' + QUOTENAME(@RepositoryName) + '
GO
CREATE USER ' + QUOTENAME(@LoginName) + ' FOR LOGIN ' + QUOTENAME(@LoginName) + '
GO
ALTER ROLE [db_owner] ADD MEMBER ' + QUOTENAME(@LoginName) 
PRINT @SQL
EXEC sp_executesql @SQL

DBA Dash GUI #

To use the DBA Dash GUI, users only need access to the DBA Dash repository database. No access is required to the monitored instances. To grant the minimum permissions to run the DBA Dash GUI, add the user the the App role in the DBA Dash database. This grants the user SELECT and EXECUTE permissions to the database.

The ManageGlobalViews role can be used to allow the user to save their customized metrics dashboards for all users. They will also have access to delete shared dashboards. Users with db_owner will also have the same access. Otherwise the user can still create their own dashboards.