Recommended Configurations

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

note

The SQL Solution Jobs report on both the SQL and Azure SQL Collection Jobs.

SQL Solution 0.Collection Job Group

The SQL Solution has been configured to inherit down 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 Sensitive Data Discovery Auditing – Sensitive Data Discovery Add-On installed on the Enterprise Auditor Console server
• 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

You can scope some of the 0.Collection Job Group queries to target specific databases/instances. However, you must populate the SA_SQL_Instances table before scoping the queries. Therefore, manually run the 0-SQL_InstanceDiscovery Job 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 the account has been provisioned, 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

One of the most important decisions to make is how frequently to collect this data. You can schedule the SQL Job Group to run as desired depending on the types of auditing being conducted and the scope of the target environment. Schedule the solution to run 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 Enterprise Auditor 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

Azure SQL 0.Collection Job Group

The Azure SQL Solution has been configured to inherit down from the Azure 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

• Full registration within Microsoft's Azure portal:

  • Creation of an Enterprise Auditor Azure SQL Role in the Access control (IAM) section
  • Successful registration of the Enterprise Auditor app
  • Successful creation of an Application (client) ID

• Successful configuration of an AzureSQL-specific connection profile

• Creation of an Azure Tenancy host list (ex. COMPANY.onmicrosoft.com) and Azure Active Directory user credentials

  info

  To avoid functional issues with Enterprise Auditor, create multiple connection profiles to accommodate multiple credentials.

• Define and validate connection information in the Connection screen

• 0-AzureSQL_InstanceDiscovery Job run successfully

Targeted Hosts

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

• All targeted Azure SQL Tenants

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 with database login enabled or a SQL account. After the account has been provisioned, 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 Databases > 0.Collection > Azure SQL > 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

One of the most important decisions to make is how frequently to collect this data. You can schedule the Azure SQL Job Group to run as desired depending on the types of auditing being conducted and the scope of the target environment. Schedule the solution to run daily.

Run Order

Run the 0.Collection Jobs first and in order. You can run the other Azure 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.