Check SQL Server Logins for Auditing

An audit installation requires at least two Microsoft SQL Server databases: one for the management database and at least one for the first audit store database. To successfully connect to these databases, you must ensure that the appropriate users and computers have permission to read or to read and write for the databases that store audit-related information.

The simplest way to manage SQL logins for auditors and administrators is to do the following:

  • Ensure you have a SQL login account for the NT Authority\System built-in

    account.

  • Add the NT Authority\System account to the system administrator role.

  • Use Audit Manager to grant Manage SQL Logins permissions to the Active

    Directory users and groups that require them.

If you use Audit Manager to manage SQL logins, you can use Active Directory membership to automatically add and remove the permissions required for auditing activity. There is no requirement to use the SQL Server Management Studio to manage logins or permissions. Because it is recommended that you have a dedicated SQL Server instance for auditing, giving the NT Authority\System account a SQL login and system administrator role is an acceptable solution for most organizations.

Create Security Groups for Auditing

Depending on whether you configure Microsoft SQL Server to use Windows only authentication or Windows or SQL Server authentication, your SQL Server login credentials might be a Windows account or a SQL Server login account that is not associated with a Windows account.

To facilitate communication and the management of SQL logins, you can create Active Directory security groups for the following users and computers:

  • Admins for the user accounts that perform administrative tasks

    using Audit Manager.

  • Auditors for the user accounts that user Audit Analyzer.

  • TrustedCollectors for the computers accounts that host the

    collector service.

If you create these Active Directory security groups, you can then use Audit Manager to grant Manage SQL Login permissions for each group to allow its members to connect to the appropriate SQL Server database. Creating Active Directory security groups with SQL Server logins enables you to manage access to the databases required for auditing through Active Directory group membership without the help of the database administrator.

Any time you want to add an administrator, auditor, or collector computer to the installation, you simply add that user account or computer object to the appropriate Active Directory group. If an administrator or auditor leaves or if you want to stop using the collector on a particular computer, you can remove that user or computer from its Active Directory security group to prevent it from accessing the database.