Endpoint Protection Manager Home page displays error: Query Failed

book

Article ID: 169708

calendar_today

Updated On:

Products

Endpoint Protection

Issue/Introduction

The Home page in the Symantec Endpoint Protection Manager (SEPM) displays the message "Query Failed" on log in.

Full message displayed on the Home page:

Query Failed

This query could not be processed for one of the following reasons:

  1. The database query timed out. Try reducing the number of filters, or increasing the SQL Server query timeout value.
  2. An unexpected error occurred. Try running the query again by using default filter values.
  3. String encoding was possibly not UTF-8, and may result from copying and pasting data instead of typing data. Try typing data in the input fields.

From the Reporting.log:

2017-06-26 14:52:05 ERROR:<br>QUERY: EXEC SEM_GET_OUTOFDATE_CLIENTS_MAC_EE_COUNT '0', 'AF3C39A10A320801000000DBF200C60A', 'ERROR_NO_ALLOWED_DOMAIN', '', '', '2017-06-16 rev. 999', '2017-05-24 rev. 999', '2017-06-16 rev. 000', '2017-05-27 rev. 000', '1', '1', '', ''<br>EXCEPTION: exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][SQL Server Native Client 11.0][SQL Server]The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'.' in C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php:568
Stack trace:
#0 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(568): com->Execute('EXEC SEM_GET_OU...', -1)
#1 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(328): ado_doPreparedStatement('EXEC SEM_GET_OU...', Object(com))
#2 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\connectdb.php(246): ado_query('EXEC SEM_GET_OU...', Object(com), false)
#3 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Inetpub\Reporting\Dashboard\endpoint_status.php(210): sav_query('EXEC SEM_GET_OU...', Object(com))
#4 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Inetpub\Reporting\Dashboard\ajax.php(591): include('C:\\Program File...')
#5 {MAIN.EN_US}Error message: <b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][SQL Server Native Client 11.0][SQL Server]The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'.<br>Error code: -2147352567<br>File and line: C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(568)<br>Trace: #0 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(568): com->Execute('EXEC SEM_GET_OU...', -1)
#1 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(328): ado_doPreparedStatement('EXEC SEM_GET_OU...', Object(com))
#2 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\connectdb.php(246): ado_query('EXEC SEM_GET_OU...', Object(com), false)
#3 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Inetpub\Reporting\Dashboard\endpoint_status.php(210): sav_query('EXEC SEM_GET_OU...', Object(com))
#4 C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Inetpub\Reporting\Dashboard\ajax.php(591): include('C:\\Program File...')
#5 {MAIN.EN_US}<br>

Cause

This probem happens when the limited SQL account used by the SEPM doesn't have permission to use the sp_executesql stored procedure.

Starting with SEPM 14, the Reporting server uses a limited SQL account 'REPORTER_' to query the information used on the Home, Monitors and Reports pages. This account inherits permission to the sp_executesql stored procedure from the Public role, which is associated with the master database in the SQL instance. If the SQL server has been hardened by removing permission to run the sp_executesql stored procedure from the Public role, the SEPM will be unable to load the SEPM Home page.

Resolution

Confirm the Public database role has permissions to the extended stored procedure sp_executesql. The following SQL query can be used to add the permission back to the Public role:
GRANT EXECUTE ON [sys].[sp_executesql] TO [public]

Note: If you do not want to add sp_executesql permissions to the Public role, you can create a separate database role under the master database, and assign this role to the SEPM reporting SQL user.

 

To verify the permission within Microsoft SQL Server Management Studio for the Public role:

  1. Expand System Databases, master, Security, Roles, Database Roles
  2. Open public
  3. Select Securables
  4. Look in the name column for sp_executesql and verify grant is checked for the explicit permission Execute