Recommended Configurations

The following sections describe the recommended configurations for the SQL Solution 0.Collection Job Group.

SQL Solution 0.Collection Job Group

The SQL Solution inherits settings from the SQL > Settings node. However, it is best practice to assign the host list and the Connection Profile at the data collection level, the 0.Collection Job Group. After you assign these to the job group, you can run it directly or schedule it.

Dependencies

• .Active Directory Inventory Job Group run successfully
• For Activity Auditing – SQL Server Audit Specifications to be configured on the target databases
  • Audit destination must be a binary file
  • See the Microsoft Create a Server Audit and Database Audit Specification article for additional information.
• For the SQL_SecurityAssessment Job – One or more of the following jobs or job groups must be run to produce results:
  • 0.Collection > SQL Job Group
  • 1.Users and Roles Job Group
  • 3.Permissions Job Group
  • 5.Sensitive Data Job Group
  • Privileged Accounts Job Group
  • Privileged Accounts Job Group

Some of the 0.Collection Job Group queries can be scoped to target specific databases/instances. However, you must populate the SA_SQL_Instances table before scoping the queries. Therefore, run the 0-SQL_InstanceDiscovery Job manually before scoping the 0.Collection Job Group queries.

Targeted Hosts

The 0.Collection Job Group has been set to run against the following default dynamic host list:

• All Microsoft SQL Server Hosts

  note

  Default dynamic host lists are populated from hosts in the Host Master Table which meet the host inventory criteria for the list. Ensure the appropriate host lists have been populated through host inventory results.

Connection Profile

The SQL Data Collector requires a specific set of permissions. See the Permissions section for necessary permissions. The account used can be either an Active Directory account or a SQL account. After provisioning the account, create a custom Connection Profile containing the credentials for the targeted environment. See the SQL Custom Connection Profile & Default Dynamic Host List topic for additional information.

Assign the Connection Profile under the SQL > 0.Collection > Settings > Connection node. It is set to Use the Default Profile, as configured at the global settings level. However, since this may not be the Connection Profile with the necessary permissions for the assigned hosts, click the radio button for the Select one of the following user defined profiles option and select the appropriate Connection Profile dropdown menu.

See the Connection topic for additional information.

Schedule Frequency

Schedule the SQL Job Group to run based on the types of auditing and the scope of the target environment. The general recommendation is to run the solution daily.

Run Order

Run the 0.Collection Jobs first and in order. You can run the other SQL Solution sub-job groups in any order, together, or individually, after running the 0.Collection Job Group.

info

Run the solution at the top level.

Workflow

Prerequisites:

• Successful execution of the .Active Directory Inventory Job Group
• For the 3-SQL_ActivityScan, configure SQL Server Audit and SQL Server Audit Specifications on target SQL Server Databases. Audit destination for the configured server or database audit must be a binary file.
• The 5-SQL_ServerSettings Job contains the Orphaned Users query which returns users that are orphaned for each database. The query uses the sp_Change_users_login procedure which requires the db_owner fixed database to be assigned to the Access Analyzer User. See the sp_change_users_login (Transact-SQL) Microsoft article for additional information.

1. (Optional) Configure the queries for the jobs in the 0.Collection Job Group

2. Schedule the 0.Collection Job Group to run daily or as desired

   note

   Running the 0.Collection Job Group is a prerequisite for the other job groups in the SQL solution

3. Review the reports generated by the 0.Collection Job Group’s jobs