Why could .mxd with no layers store SDE database connections?

[TobiasFimpel1]

I have an .mxd with one dataframe that used to contained layers with data source being Oracle 11g, 10.3 Geodatabase. Database credentials were not stored in those layers/.sde connections. As expected the user was prompted to enter credentials upon opening the mxd.

Now I remove all the layers, and the user is still prompted for credentials upon opening the .mxd file. This is unexpected behavior and I need to understand why this is happening (have many such .mxd files and it's a huge problem when the .mxd's actually do store passwords.)

I stripped everything I can find from the .mxd (toolbars, layout elements, etc.). Also have run .mxd Doctor and Defrangementer. The only way to avoid the prompt for credentials is to remove the dataframe. The .mxd was created in ArcMap 10.1, using ArcMap 10.3 instead seems to make no difference.

Any clues are greatly appreciated. Thanks, Tobias

[FreddieGibson]

Hi Tobias,

The problem here is that sde connection information is stored within the map document. Even if you were to remove all of the layers the information is persisted so that data can be quickly added from that workspace in the future.

I logged an enhancement for this issue in 2014. If you have a support account with Esri you can call technical support and we can get you attached to NIM092675 : Allow users to programmatically suppress or remove the sde login prompt from map documents that no longer reference data to a particular sde instance.

If your original sde workspace still exists, do you still get the prompt after supplying the credentials on the original map, changing all of the datasources to your new workspace, and then saving the map? According to my notes, the MXD Doctor was able to remove the prompt for me when I leverage it to create a new map document.

[TobiasFimpel1]

Freddie and Melissa, thank you very much for pointing me in the right direction. Esri support attached me to NIM092675.

I will test, but I don't fully understand the question. By original, do you mean the workspace as the .mxd accessed it prior to me running arcpy.mapping.replaceDataSource ? Or does it not matter whether I change layer data sources via Python, ArcCatalog, or ArcMap? The strange thing is that some of the .mxd's that I have processed with arcpy.mapping.replaceDataSource show that issue, and some don't - I haven't figured out differentiating factor(s) that matter.

[KDeVogelaere]

Hi Freddie,

We have a similar case with the SDE Account becoming locked when an MXD is opened with a reference to the old connection details.  Has anyone logged a defect for allowing users to programmatically suppress the automatic connection to external data sources in ArcMap?

ESRI Customer #332947 - Esri Case #01702901

[FreddieGibson]

I'm not sure if the bug I logged would be applicable for this. I've also never seen this error. Do you have access to Esri Support so that you can have a case logged for this to determine if we can replicate this in-house?

[KDeVogelaere]

Thanks Freddie for the response, a little background, stricter security standards have been put in place for Oracle passwords (expiry after 365-days and account lockout after 3-failed login attempts). Security prevents us from reusing old passwords, so opening any maps that have Oracle details saved before the password change will immediately lock the account. Here is the ESRI support case #01702901.

A few solutions shared by an ESRI staff member can fix the issue outside of ArcMap, I'm wondering if an ArcMap solution would be possible.

Fixing ArcMap Oracle DBMS Connections [ORD-28000: the account is locked No extended error.]

[TobiasFimpel1]

K DeVogelaere​, note that the method you are linking to describes how to change the datasources for existing map layers and tables. Like I state in the discussion title I am coming across many .mxd files that also store the datasource incl. the username and expired password "somewhere else". (I just don't have a better word for it, but wherever it is stored it seems impossible to remove it)

[TobiasFimpel1]

Freddie Gibson​, MXD Doctor seems to not work in my cases. In the oracle audit table I can still see that immediately after my map layers successfully connect to the database using the new datasource there is one connection attempt sent to the database using the old datasource. The .mxd's layer datasources were changed using "ArcCatalog --> Set Data Source(s)...". The resulting map document was run through the MXD Doctor  . Do you have any other suggestions? Thanks!

[AlisonGaiser1]

We are experiencing the same problem with a map document holding on to a reference to an SDE database.

We found that the reference was hiding somewhere in the data frame (not visibly though). We found the only way to work around it was the following: Create a new data frame, copy all the data from the old data frame into the new data frame, delete the old data frame, rename the new data frame to the name of the old one. Then problem went away.

Has anyone had any official word from Esri on this front? Anyone has a NIM?

Thanks,

   Alison

[AndréVinko]

I noted the described behaviors since ArcGIS 10.0 at least with SDE/Oracle. A fine workaround to this most inconvenient feature I applied successfully over the years:

• enable local firewall rule blocking tcp/1521@db-host for ArcMap.exe only
• open MXD with enabled rule breaking up all layers pointing to that specific host
• repair all layers using a connection file targeting the new database/host
• save MXD File/Save as... with a new filename.
• disable firewall rule
• observe the size of the file before/after. An MXD with one data frame only and no layers is smaller than 1MB. There were some older MXD with all layers deleted showing the phenomena grown up to 15MB.