Installing and configuring Microsoft SQL Server for Auditing

If you want to audit user activity on Windows, you must have at least one Microsoft SQL Server database instance for the audit management database and audit store databases. Centrify recommends that you use a dedicated instance of SQL Server for the audit management database. A dedicated SQL Server instance is an instance that does not share resources with other applications. The audit store databases can use the same dedicated instance of SQL Server or their own dedicated instances.

There are three database deployment scenarios for your audit installation:

  • Evaluation—You can install Microsoft SQL Server Express with Advanced features directly from the configuration wizard or by running the SQLEXPRADV_x64_ENU.exe setup program to create a new Microsoft SQL Server Express database instance for testing. However, if you are auditing a production environment*,* you should not use Microsoft SQL Server Express.

    If you choose to install a different version of Microsoft SQL Server Express for an evaluation and the version requires .NET version 3.5 SP1, you will need to manually install the .NET files yourself (the installer doesn't include these files)..

  • Manual installation with system administrator privileges—Install a Microsoft SQL Server database instance for which you are a system administrator or have been added to the system administrator role.

  • Manual installation without system administrator privileges—Have the database administrator (DBA) install an instance of Microsoft SQL Server and provide you with system administrator credentials or information about the database instance so that you can create the management database and audit store databases.

Downloading and installing SQL Server manually

You can use an existing instance of Microsoft SQL Server or install a new instance. You can install Microsoft SQL Server directly from the Centrify ISO or ZIP, or download it from the Microsoft web site. In selecting a version of SQL Server to download, you should be sure it includes Advanced Services. Advanced Services are required to support querying using SQL Server full-text search.

After downloading an appropriate software package, run the setup program using your Active Directory domain account and follow the instructions displayed to complete the installation of the Microsoft SQL Server instance.

When selecting the components to install in the setup program, expand the Database Services and select Full Text Search as a feature to be installed. For the authentication mode, select Windows authentication if all connections between auditing components will be in the same forest. If any communication will be outside of the forest, use Mixed Mode authentication and select the option to add the current user to the SQL Server Administrator role.

Delinea does not recommend running SQL Server under a high privilege account such as a LocalSystem account.

Configuring SQL Server to prepare for auditing

After you install the SQL Server database engine and management tools, you should configure the SQL Server instance for auditing. For example, depending on the version of SQL Server you install, you might need to manually enable fulltext search.

To prepare a Microsoft SQL Server database instance for storing audit data:

  • Use SQL Server Surface Area Configuration for Services and Connections to check the status and start the database engine, full-text search, and SQL Server Browser services.
  • Use SQL Server Surface Area Configuration for Services and Connections or SQL Server Configuration Manager to enable remote connections for TCP/IP.
  • Verify whether SQL Native Client Configuration Client Protocol is using the default TCP port 1433 for network communications. If you use a different port, you should note the port number because you will need to specify it in the server name when you create the management and audit store databases.
  • Use SQL Server Configuration Manager to restart the SQL Server and SQL Server Browser services.
  • Create a database backend service account in the system administrator (sa) fixed server role on the selected database server; you'll specify this account when you create the audit installation. This account is used to run backend stored procedures. If desired, this can be the same account that you use the create the audit installation, as mentioned in Creating a setup user account for installation.

Configuring Amazon RDS for SQL Server for auditing

You can deploy audit store databases on Amazon RDS instances, if desired. Centrify supports Amazon RDS for 2016 and earlier versions (not 2017).

You must host the audit management database on a traditional SQL Server, such as SQL Server Express, Standard, or Enterprise.

If you want to use an instance of Amazon RDS for SQL Server for audit store databases you need to do the following configurations:

  • After you set up your Amazon RDS for SQL Server, join the RDS SQL server to AWS Microsoft Active Directory.

  • Enable these DB Parameter Group settings on RDS SQL Server:

    • clr enabled

    • show advanced options

      You can use the AWS Management Console, API, or the AWS command line interface to enable these settings.

      For more details, see <http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html>.

  • Set up a one-way or two-way forest trust between the AWS Microsoft Active Directory and your on-premise Active Directory forest so that users of your on-premise Active Directory forest can access resources in the AWS Microsoft Active Directory.

    Amazon RDS for SQL Server with High Availability is supported.

Amazon RDS for SQL Server required permissions

The permissions for Amazon RDS for SQL Server vary a little from the permissions for local or network instances of SQL Server. This section covers the Amazon RDS for SQL Server permission required or granted for each auditing component.

Permissions to the audit store database stored procedures service account

The stored procedures service account (in other words, the ‘execute as’ account) no longer requires the sysadmin server role permission if the audit store database is on Amazon RDS for SQL Server.

The service account requires only the db_owner database role permission and the account will be added to be member of db_owner database role by Add Audit Store Database wizard.

Note:You do not need to grant the permissions manually. The Audit Manager console, Powershell cmdlet, or SDK grants the permissions to the service account.

Collector account permissions for audit store databases on Amazon RDS for SQL Server

The collector account requires the following server level permissions on the Amazon RDS for SQL Server:

  • 'View Any Definition' server level permission
  • 'View Server State' server level permission

The collector account requires the following database level permissions on the audit store database:

  • A member of the 'collector' database role

    You do not need to grant the permissions manually. The Audit Manager console, Powershell cmdlet, SDK, or the Collector Configuration wizard grants the permissions to the collector account.

Management Database Account permissions for audit store databases on Amazon RDS for SQL Server

The management database account requires the following server level permissions on the RDS SQL server:

  • 'Alter Trace' server level permission
  • 'Alter Any Login' server level permission
  • Grant permission of 'Alter Any Login' server level permission
  • Grant permission of 'View Any Definition' server level permission
  • Grant permission of 'View Server State' server level permission

The management database account requires the following database level permissions on the audit store database:

  • A member of 'managementdb' database role

    You do not need to grant the permissions manually. The Audit Manager console, Powershell cmdlet, or SDK grants the permissions to the management database account.

Permissions to create the audit store database on Amazon RDS for SQL Server

In order to create an audit store database on Amazon RDS for SQL Server, you must have the following permissions:

  • 'Create Any Database' server level permission to create the database on the server
  • 'Alter Any Login' server level permission to create the login for the management database account and the collector account
  • Alter Any Login' server level permission to grant the 'Alter Any Login' permission to the management database account
  • 'Alter Trace' server level permission to grant the 'Alter Trace' permission to the management database account
  • 'View Any Definition' server level permission to grant the 'View Any Definition' (with grant) permission to the management database account and also to grant the 'View Any Definition' permission to the collector account
  • Grant permission of 'View Server State' server level permission to grant the 'View Server State' (with grant) permission to the management database account and also to grant the 'View Server State' permission to the collector account

Permissions to upgrade the audit store database on Amazon RDS for SQL Server

The required permission to upgrade the audit store database on Amazon RDS for SQL Server is the ‘db owner’ permission on the database. No server level permissions are required