topic Why could .mxd with no layers store SDE database connections? in ArcGIS Enterprise Questions

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

TobiasFimpel1 — Wed, 30 Sep 2015 18:29:44 GMT

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

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

Anonymous User — Fri, 02 Oct 2015 20:00:18 GMT

Hi Tobias,

Can you reproduce the same behavior if you use a new blank MXD from 10.3?

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

MichaelVolz — Fri, 02 Oct 2015 20:24:27 GMT

Can you try doing a SaveAs to a new filename to see if the SDE connection information is then removed from the newly saved mxd? If this works you can write a python script to automate this process by walking through the directories that contain your problematic mxds.

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

RandyKreuziger — Fri, 02 Oct 2015 20:30:11 GMT

Could there be a SDE table remaining in the map? Change your Table of Contents to List By Source.

And if that doesn't work check out the data sources in the MXD through ArcCatalog's "Set Data Sources" thing.

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

TobiasFimpel1 — Mon, 05 Oct 2015 20:47:13 GMT

Hi Katie.

No, this does not occur with new, blank MXD's.

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

TobiasFimpel1 — Mon, 05 Oct 2015 20:48:23 GMT

Hi Michael,

The issue persists even after I do "File"--> "Save As..." .

Thanks,

Tobias

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

TobiasFimpel1 — Mon, 05 Oct 2015 20:53:46 GMT

Hi Randy,

No, there is not layer or table in the Table of Contents.

With ArcCatalog --> Set Data Sources I get the same information: no layers or tables in the mxd.

Thanks,

Tobias

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

MelissaJarman — Thu, 15 Oct 2015 16:47:34 GMT

Tobias -

How were the layers removed from the map document? Manually in the TOC or did you at one time use a tool such as set data source(s). ?

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

TobiasFimpel1 — Thu, 15 Oct 2015 17:09:18 GMT

Hi Melissa,

At one time I used arcpy.mapping replaceDataSource (workspace_path, workspace_type, {dataset_name}, {validate}) .

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

FreddieGibson — Thu, 15 Oct 2015 17:32:17 GMT

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.

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

MelissaJarman — Fri, 16 Oct 2015 11:26:44 GMT

Like Freddie said there are some defects that are logged regarding this behavior. In order to be associated with one you will need a reproducible workflow to present to support starting with a new map document. Keep us posted if you submit a support case!

Esri Support

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

TobiasFimpel1 — Fri, 16 Oct 2015 17:56:01 GMT

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.

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

KDeVogelaere — Mon, 09 Nov 2015 19:43:23 GMT

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

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

FreddieGibson — Tue, 10 Nov 2015 16:26:02 GMT

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?

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

KDeVogelaere — Tue, 10 Nov 2015 17:18:39 GMT

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.]

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

TobiasFimpel1 — Tue, 10 Nov 2015 17:50:35 GMT

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)

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

TobiasFimpel1 — Mon, 16 Nov 2015 17:58:06 GMT

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!

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

AlisonGaiser1 — Wed, 04 May 2016 20:49:01 GMT

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

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

AndréVinko — Tue, 08 Oct 2019 13:37:52 GMT

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.