Minimum User Profile Requirements

• ODBC connection from the monitoring node to the database server using an SQL Server Native driver.
• User connection with permissions to connect via ODBC and ALTER TRACE.
• A folder created in the server where the traces will be stored. It can be manually created or:
  • OPTIONAL: If you want the folder to be automatically created, the use the sysadmin user and have xp_cmdshell enabled.
  • OPTIONAL: Even if the traces rotate and no data is ever accumulated, the files cannot be deleted unless the user is sysadmin and has xp_cmdshell enabled.
• By default, Execute permissions are granted to members of the fixed server role sysadmin for:
  • xp_trace_addnewqueue
  • xp_trace_setqueuedestination
  • xp_trace_restartqueue
  • xp_trace_pausequeue
  • xp_trace_destroyqueue
  • xp_trace_enumqueuehandles
  • xp_trace_getqueuedestination

These permissions can also be granted to other users as the need arises.

• To perform select and delete queries on the table where event information is stored you need either a user belonging to the sysadmin server role, or the user that created the trace by calling xp_trace_setqueuedestination extended store procedure (i.e. the owner of the trace).
• To create a startup for a stored procedure you must be logged in as a member of the sysadmin fixed server role and create the stored procedure in the master database.

• To create the table tango_traces, the user must be granted CREATE TABLE permission. By default, this permission is granted to the members of the db_owner and db_ddladmin fixed database roles. Members of the db_owner fixed database and members of the sysadmin fixed server role can transfer CREATE TABLE permission to other users.

• To perform delete queries to table tango_traces, DELETE permission must be granted. This permission is granted by default to members of the sysadmin fixed server role, the fixed database roles db_owner and db_datawriter, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, in addition to the table owner, can transfer permissions to other users.

In SQL Server, the server must permit the use of the stored procedure xp_cmdshell. This stored procedure is disabled by default and must by enabled using the SQL Server Surface Area Configuration. See Enabling the xp_cmdshell Stored Procedure - SQL Server 2005 or Enabling the xp_cmdshell Stored Procedure - SQL Server 2008 (and higher) for further details.

Unlike SQL Server 2000, later versions of SQL do not have a strictly required sysadmin account which allows restriction of the given permissions to the DSN user. It is also possible to restrict the direct execution of the xp_cmdshell stored procedure.

Using xp_cmdshell directly with a proxy user

To use this option, assign privileges to users or login names manually. These privileges are granted by default to members of the sysadmin fixed server role and can be granted to other users with the following commands:

USE [master]

GRANT ALTER ON SCHEMA :: [dbo] TO [helpsystems_user]

GRANT SELECT ON SCHEMA :: [dbo] TO [helpsystems_user]

GRANT INSERT ON SCHEMA :: [dbo] TO [helpsystems_user]

GRANT UPDATE ON SCHEMA :: [dbo} TO [helpsystems_user]

GRANT DELETE ON SCHEMA :: [dbo] TO [helpsystems_user]

GRANT EXECUTE ON [master].[sys].[xp_cmdshell] TO [helpsystems_user]

GRANT EXECUTE ON [master].[dbo].[sp_trace_setfilter] TO [helpsystems_user]

GRANT EXECUTE ON [master].[dbo].[sp_trace_create] TO [helpsystems_user]

GRANT EXECUTE ON [master].[dbo].[sp_trace_setevent] TO [helpsystems_user]

GRANT_EXECUTE ON [master].[dbo].[sp_trace_setstatus] TO [helpsystems_user]

GRANT_EXECUTE ON [master].[dbo].[xp_fileexist] TO [helpsystems_user]

GRANT CREATE PROCEDURE TO [helpsystems_user]

GRANT CREATE TABLE TO [helpsystems_user]

GRANT ALTER TRACE TO [helpsystems_login]

When xp_cmdshell is called by a user who is not a member of the sysadmin fixed server role. It connects to the operating system by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell fails. Create the proxy account credential by executing the following command.

CREATE CREDENTIAL [##xp_cmdshell_proxy_account##] WITH IDENTITY = N’DOMAIN\USER’, SECRET = N’PASSWORD’

This user must have log on as a batch job rights, permission to act as part of the operating system, to increase quotas, to replace process level token as well as full control permissions in the directory where the traces are stored. See Grant Rights to Log On as a Batch Job.

SQL Audit collection technology also performs operations to check whether a file exists on the system and delete old temporary files where events were stored. This is done by executing advanced stored procedures xp_cmdshell, permission for which is granted by default to members of the sysadmin fixed server role but can be granted to other users.

It is important to note that when using a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

Grant Rights to Log On as a Batch Job

It is necessary to add some permissions for the (new, non-fixed server role) user you assigned to the proxy account so that the user is able to log on as a batch job. This is done in the local security settings of the machine you wish to monitor.

To give the user “Log on as a batch job” privileges:

1. Open the target SQL server.
2. Click Security Settings, select Local Policies and click User Rights Assignment.
3. Open Log on as a batch job and add the user that you assigned to the ##xp_cmdshell_proxy_account##. Click Apply and then OK.

Enabling the xp_cmdshell Stored Procedure - SQL Server 2005

In SQL Server 2005, users are forbidden from running the xp_cmdshell stored procedure. The SQL Server Security Agent uses this procedure and it must therefore be enabled.

To enable the xp_cmdshell stored procedure:

1. On the task bar, click the Start menu, select All Programs > Microsoft SQL Server 2005 > Configuration Tools, and then click SQL Server Surface Area Configuration to launch the Surface Area Configuration utility.

2. Click the Surface Area Configuration for Features option.

3. Select xp_cmdshell and click the Enable xp_cmdshell check box.

Enabling the xp_cmdshell Stored Procedure - SQL Server 2008 (and higher)

In order to enable the xp_cmdshell procedure in SQL Server 2008 (and higher), you must run the following:

To allow advanced options to be configured:

To update the currently configured value for advanced options:

To enable the feature:

To update the currently configured value for this feature: