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:
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>
This problem 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.
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: