SQL connection errors in Risk Automation Suite

Article:TECH147221  |  Created: 2010-12-30  |  Updated: 2012-01-09  |  Article URL http://www.symantec.com/docs/TECH147221
Article Type
Technical Solution


Issue



One or more components of Risk Automation Suite are reporting SQL database connection errors in the Windows Event Log.


Error



Error making SQL query to the <database> database :
System.Data.SqlClient.SqlException: Ambiguous column name 'unit_ID'. Ambiguous column name 'subunit_ID'. Ambiguous column name 'unit_ID'. Ambiguous column name 'subunit_ID'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at DatabaseLibrary.Database.SqlArrayListSelectMultiple(String sql, Int32
NumberOfValues)SELECT ISNULL(aco.organization_abbrev, 'Unassigned') AS [Business Unit], COUNT(DISTINCT host_ID) AS 'Used', (SELECT SUM(total_addresses) FROM assets_networks WHERE ( (unit_ID = 100282 OR subunit_ID = 100282 OR unit_ID = 100281 OR subunit_ID = 100281)) AND status = 'live' AND assets_networks.unit_ID = aco.organization_ID OR (assets_networks.unit_ID = 0 AND aco.organization_ID IS NULL)) AS 'Total' FROM assets_networks an JOIN assets_hosts ah on an.network_ID = ah.network_ID LEFT JOIN adm_conf_organization aco ON aco.organization_ID = an.unit_ID WHERE ( (unit_ID = 100282 OR subunit_ID = 100282 OR unit_ID = 100281 OR subunit_ID = 100281)) AND an.status = 'live' AND ah.status = 'live' GROUP BY aco.organization_ID, aco.organization_abbrev ORDER BY aco.organization_abbrev 


Cause



The connection string to the SQL database is incorrect.


Solution



Delete and re-create the connection string for the reporting component.

  1. In the Windows Event Viewer determine the component of Risk Automation Suite that is reporting the error.
  2. Open a command prompt and navigate to the folder for the component
    1. This will look like C:\<install path>\<component>
  3. Run the executable for the component followed by the setup switch.
    1. If the component was SFAssigner, this would look like:

From a command prompt:
C:\<Install Path>\SFAssigner\assigner.exe -s

Risk Automation Suite SFAssigner v4.0.3.1907 - Copyright c 2003-2010 Symantec Corporation, Inc. All Rights Reserved.

 Currently configured DB connection strings:

1. Data Source=(local);Initial Catalog=Gideon;Integrated Security=True;Connect Timeout=1200;Encrypt=True;TrustServerCertificate=True;Packet Size=4096

 Delete the current string:

Select an action (a) add or (d) delete or (x) exit: d

Please enter the number of the connection string to delete: 1

Currently configured DB connection strings:

Re-Configure the string:

Select an action (a) add or (d) delete or (x) exit: a

Please enter a database server, or enter for localhost: <this will depend on whether your install is on a single box or if you have a separate SQl Server>

Please enter the database server instance, or enter for default instance: <this will depend on your SQL Server configuration>

Please enter the database name: <your database name>

Use integrated security? (yes/no) yes

Please enter the connection timeout, default is 1200:

Currently configured DB connection strings:

1. Data Source=(local);Initial Catalog=<your database name>;Integrated Security=True;Connect Timeout=1200;Packet Size=4096

Select an action (a) add or (d) delete or (x) exit: x

Do you want to setup a connection to a Remedy system now yes/no (yes)?no

Do you want to enable integration with SMP yes/no (no)? no

Set the logging level low/medium/high or hit enter to leave current setting (low): low

Do you wish to restart the service, SFAssigner, so the changes can take effect? (yes/no) yes

 Interactive setup is complete!




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


Terms of use for this information are found in Legal Notices