Error adding connection to Symantec Endpoint Protection database from IT Analytics

Article:TECH156581  |  Created: 2011-03-25  |  Updated: 2011-06-07  |  Article URL http://www.symantec.com/docs/TECH156581
Article Type
Technical Solution


Issue



Attempting to add a connection to a Symantec Endpoint Protection database from IT Analytics produces an error


Error




Server Error in '/Altiris/ITAnalyticsSEP' Application.

Invalid object name 'dbo.fnITAnalytics_SEP_GroupToGroupPolicyLinkFact'.
Invalid object name 'fnITAnalytics_SEP_AdministratorDim'.
Invalid object name 'vITAnalytics_SEP_ClientFact'.
Invalid object name 'vITAnalytics_SEP_AdministratorRightsFact'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.fnITAnalytics_SEP_GroupToGroupPolicyLinkFact'.
Invalid object name 'fnITAnalytics_SEP_AdministratorDim'.
Invalid object name 'vITAnalytics_SEP_ClientFact'.
Invalid object name 'vITAnalytics_SEP_AdministratorRightsFact'. Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): Invalid object name 'dbo.fnITAnalytics_SEP_GroupToGroupPolicyLinkFact'.
Invalid object name 'fnITAnalytics_SEP_AdministratorDim'.
Invalid object name 'vITAnalytics_SEP_ClientFact'.
Invalid object name 'vITAnalytics_SEP_AdministratorRightsFact'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6281548
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6282737
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +522
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +182
Altiris.ITAnalyticsSEP.Web.Settings.RegenerateViews() +150 [AeXException: Exception in Regenerate Views inside SEP ConnectionsInvalid object name 'dbo.fnITAnalytics_SEP_GroupToGroupPolicyLinkFact'.
Invalid object name 'fnITAnalytics_SEP_AdministratorDim'.
Invalid object name 'vITAnalytics_SEP_ClientFact'.
Invalid object name 'vITAnalytics_SEP_AdministratorRightsFact'.]
Altiris.ITAnalyticsSEP.Web.Settings.RegenerateViews() +370
Altiris.ITAnalyticsSEP.Web.Settings.controlButtonsEdit_Click(Object sender, EventArgs e) +351
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +140
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981
   


Environment



This issue is isolated to the following environment:

NOTE: As this issue has been resolved in more recent versions of IT Analytics (ITA), specifically, ITA 7.1 SP1, applying this fix to more recent version will product unknown results with the likely hood of breaking the solution

• IT Analytics for Symantec Endpoint Protection 7.1.1011
• Symantec Endpoint Protection database (by default it is named sem5) contains tables and views which are owned by a schema other than dbo. This can be determined, for example, if instead of the ALERTS table showing as dbo.ALERTS in SQL Management Studio, it is displayed as something else, such as fsem5.ALERTS. 

 


Cause



This environmental situation typically is caused when the installation of Symantec Endpoint Protection uses a SQL account to create the sem5 database instead of using Windows Credentials at the time.

The sql queries / views that IT Analytics utilize to operate with the Symantec Endpoint Protection Database require the tables and views within that database to be owned by dbo.

Ownership by a user other than dbo can typically occur when the installation of Symantec Endpoint Protection uses a SQL account to connect to the sem5 database rather than using Windows Credentials


Solution



This issue is resolved with IT Analytics 7.1 SP1. If an older version is installed, review the following workaround:

Run the attached sql script to alter the stored procedure. The change to this stored procedure creates a new function which appropriately identifies the table owner and queries it correctly.

NOTE: Do not run this script if running a version of IT Analytics for Symantec Endpoint Protection that is greater than the version shown above. This issue has been fixed in later versions of IT Analytics for Symantec Endpoint Protection.

Once the attached script has been executed from within SQL, Execute the stored procedure by issuing the following command from within SQL Server Management Studio:

Exec spITAnalytics_SEP_CreateViews

Once the stored procedure is both modified and executed successfully , you should be able to add and remove SEP connections without this error occurring,.

As always, if you uninstall or repair IT Analytics for Symantec Endpoint Protection you will have to apply this KB again as the repair will return the affected code to its original state.
   


Attachments

SQL query to modify stored procedure spITAnalytics_SEP_CreateViews
Alter SEP CreateViews SP.txt (96 kBytes)


Article URL http://www.symantec.com/docs/TECH156581


Terms of use for this information are found in Legal Notices