KBI 220653 Data List Becomes Corrupted With SQL Authentication

Version

Argent Data Consolidator 8.0A-0610 and 8.0A-0701

Date

2 February 2007

Summary

Data List becomes corrupted with SQL Server authentication.

Technical Background

When Argent XT is installed against a SQL Server with SQL Server Authentication, the default Data List &DS_DEFAULT is created with a connection string matching what was provided during setup.

With passwords of even modest length (8 characters or longer), this Data List becomes corrupted via the encryption algorithm. The password is truncated and the connection string then is invalid. This results in unusable reports.

Example

Configure a Data List as follows:

A sa password of ‘12345678’ results in a Data List such as:

MS_SQL(., argentxt, sa, {\210\153\154\155\156\157\158\15), ALC_ArcEvtLog

Note that the encryption algorithm is not deterministic, so a different string results each time. Similarly this issue does not always reproduce; if the encrypted string has no high-bit characters in it the issue is avoided.

Observe that the string is missing the closing brace and likely a digit from the last number (\159). Running reports such as RPT_EVENTLOG_SUMMARY against this data list shows the following issue:


Error: Failed to open database use "\210\153\154\155\156\157\158\15".Error:Data source name not found and no default driver specified

Invalud connection string attribute

(80004005).

Other reports such as RPT_LOGON_OFF also fail, but with a different symptom:


Error: Logon failed.

Details: ADO Error Code: 0x80040e4d

Source: Microsoft OLE DB Provider for SQL Server

Description: Login failed for user 'sa'.

SQL State: 42000

Native Error 18456 (80047e48)

Resolution

A workaround is to edit the Data List definition via the underlying SQL table to give an unencrypted password. Find the row in ALC_DSList corresponding to the affected Data List, and replace the encrypted password in curly braces with the unencrypted password. With the example above,

MS_SQL(., argentxt, sa, {\210\153\154\155\156\157\158\15), ALC_ArcEvtLog

is changed to

MS_SQL(., argentxt, sa, 12345678), ALC_ArcEvtLog

Reference:

The text messages of the relevant errors are listed to aid in KBI searches:


Error: Failed to open database use '\191\237\235\184\233\190\153\23'. Error: Data source name not found and no default driver specified

Invalid connection string attribute (80004005).



Error: Logon failed.

Details: ADO Error Code: 0x80040e4d

Source: Microsoft OLE DB Provider for SQL Server

Description: Login failed for user 'sa'.

SQL State: 42000

Native Error: 18456 (80047e48).