SDE is causing Oracle to lock out the schema

[User35489]

Greetings,

My Environment: ArcSDE 10.1, RHEL 6, Oracle 11g

We changed a password on an Oracle schema, but apparently we have some rogue SDE clients - probably old ArcGIS map services or map documents 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.

I found that Oracle audit always trace SDE server name as it is unable to see the underneath connections of SDE application server.

Is there any way to detect where the bad login attempts are coming from?

My Environment: ArcSDE 10.1, RHEL 6, Oracle 11g

Thanks

[George_Thompson]

Adding the Geodatabase​ group....

[VinceAngelo]

Blaming ArcSDE for the lockouts is a bit like blaming the doorman for the rent.

You have services which are connecting as a user.  Those services had been giving the correct password, but now that it's been changed, they are no longer doing so. You'll need to track down the connection file(s) for these services, and update them with the correct password.

Unfortunately, there may also be project files and layer definitions which also have this (now) incorrect password, and will try the password once for each layer linked to the connection.  Now that Direct Connect is the norm,  there is no application server to host all the connection failure logs, so you're likely going to need to consult the database log of login failures (though where that is is not immeadiately obvious) or to consult the Direct Connection logs on every network client machine  (which is likely to be tedious).

- V

[User35489]

Thanks Vince for helpful comments.

We have more than 500 users, so its quite difficult to reach them out and check all the documents. I wish SDE being spatial database admin to have some function which can trace these connection information out, like oracle RDBMS have one. But unfortunately this function is not yet available. Let me see what i can do with documents with incorrect passwords.

-AS

[VinceAngelo]

The most important thing to remember when assigning wishes to SDE is that it no longer exists!

You need to think "enterprise geodatabase", not SDE.  And in your enterprise environment, it is Oracle who is the gatekeeper to Oracle access.  The SE_connection_create function just knocks at the door.  You don't walk around asking people in the neighborhood if they knocked on your door, you ask the doorbell logger.

It is probably true that ArcGIS needs an idependent mechanism for tracking unsuccessful login attempts, but that isn't a solution to the problem of changing passwords associated with service accounts.  I'm not even sure that problem is tractable.

- V

[User35489]

Thank you once again for comments.

I think thats exactly what i am looking for, a mechanism(optional, can be enabled or disabled as per need) in ArcGIS to track unsuccessful login attemps.

-AS

[VinceAngelo]

There would still be the fundamental issue that each host would have its own collection of connection logs, making meaningful analysis impossible.  The only central repository of login attempts is the database, which clients can't log to, because they didn't successfully connect.  You just need to enable logging by the database (which is outside the scope of ArcGIS).

- V