Help! - ArcSDE is locking our Oracle schemas

[NathanielWingfield]

We changed a password on an Oracle schema, but apparently we have some rogue SDE clients - probably old ArcGIS map services on a dev machine somewhere - attempting to connect with the old password. SDE is causing Oracle to lock out the schema because of the rapidfire attempts to connect with the bad password.

Is there any way to detect where the bad login attempts are coming from? I have turned on the ArcSDE intercept log, but I don't see anything related to connect attempts there.

We're using SDE 9.3.1.

[VinceAngelo]

The intercept log is the conversation between the server and the database. If connection
isn't made, there won't be much to talk about.

Are you using application server or Direct Connect connections? Application servers will
at least log in one location (and would be more forthcoming with "set SDEVERBOSE=TRUE"
in the dbinit.sde; Direct Connect will log on individual clients, but you'd probably be more
interested in the SQL*Net logs.

A more accurate title for this thread would have been "ArcGIS Server SOC(s) with old
password locking Oracle user accounts". ArcSDE is quite innocent of all charges.

- V

[NathanielWingfield]

I turned on verbosity, then made a test connection from ArcCatalog with a bad password. Looking at the giomgr_x.log file, this is all I see. Should I look elsewhere? Is the client hostname or IP address not logged somewhere?

Mon Oct 22 12:13:24 2012 - Error (-9):Couldn't Start Server Task.

Mon Oct 22 12:13:24 2012 - Process 15444, no shared information block established

Mon Oct 22 12:13:24 2012 - SDE Server 15444 exit'd with status 3

[VinceAngelo]

Try enabling a firewall on the ArcSDE server, set to permit but log connection requests
on the application server port.

After that you'll need to scan the AGS logs on all your development clients, looking for
a persistent-but-misguided SOC.

- V

[NathanielWingfield]

How do you suggest I then correlate the inbound connections to SDE with the corresponding outbound connections to Oracle?

Consider this a formal request for better logging capabilities. I think your customer base would appreciate more visibility into SDE's behavior.

Mapping inbound spatial queries to outbound SQL (e.g., for performance tuning) is another deficiency that needs attention, perhaps even more so.

[VinceAngelo]

Remote application servers are not recommended, but that is unreleated to this
problem -- you just need to know which clients are attempting connect on the
application server port (to identify the rogue actors), so it doesn't really matter
how connections map back out again.

All formal requests should go through formal processes (like the ideas.esri.com site).

- V