[GIS] How to remove username / password from an ArcGIS mxd

arcgis-desktopconnectiondatabasemxdpassword

When you create an mxd and use layers from a database connection where you are using "Database authentication" and clicked "Save username and password" and share the mxd with others, why are others then able to access the database with my account? Or, how do I removed the cached username / password / connection from my mxd? Or, why is my account getting locked out everyday?

This is a terrible problem, first and most importantly, for database security, and second, the account will continually be locked out until you track down everyone who has the mxd that was shared.

(Why will it be locked? A. Your database may restrict the number of connections that your account can make and with multiple people using the mxd with your account, your account will be logged in too many times. B. When your database password changes, others will be using the mxd with your account and your old password that tries to login, the login will fail (multiplied by however many layers in the mxd), and it will lock your account for failed attempts.)

Best Answer

This has been partially answered in other posts:

but I want to make it exceedingly clear here.

Non-Solution: You might be thinking: Ahh..Just go back into the connection in ArcCatalog, remove my username/password and un-click "Save Username and password", save the connection and save the mxd. WRONG. The connection information for the layer is already cached in the mxd. Changing the connection won't do anything. Even deleting the connection from ArcCatalog won't do anything. Try deleting it and restarting ArcMap with the mxd, it connects back to the database without a problem because the connection is cached in the mxd.

Solution #1 - Manually: You need to create a new database connection where you have NOT clicked "Save username and password". All the layers added to an mxd that were using a connection with username/password saved need to be changed to use this new connection, or you could start with a new mxd. Unfortunately, if you have a lot of cartographic customizations you are going to lose them. (If you can trick the problem data source into failing, say change the account's password, re-open the mxd, see the red exclamation points, create a new connection without username/password, then Data > Repair Connections and point to the new connection, you may be able to change them all at once and keep your cartography.) After you're done, re-open the mxd and you should be prompted for username / password. Then, you know you can safely share away!

Solution #2 - Python Script: From ArcGIS Help, http://resources.arcgis.com/en/help/main/10.1/index.html#//00s30000004p000000 , see the latter part of the section “SDE connection properties are changed” where it mentions setting the validate parameter to False.

"SDE connection properties are changed ... In this scenario, a user wants to remove the password information saved within a map document. The data sources in the map document came from an SDE connection file where the password information was saved with the connection information. Next, the user created a new SDE connection file to the same database, but this time did not save the password information. In the script below, the validate parameter must be set to False for the password information to be successfully removed. After the script is run, a user will need to log in to open the resulting map document."

import arcpy
mxd = arcpy.mapping.MapDocument(r"C:\Project\Project_default.mxd")
#The connection is probably here r"Database Connections\myConnectionFile.sde"
mxd.findAndReplaceWorkspacePaths(r"C:\Project\Connection with password info saved.sde", r"C:\Project\Connection with no password info saved.sde", False)

From the ESRI documentation and some other posts, this has some limitations. Mostly they concern less frequently used types of data such as raster, query layers, label expressions, etc.

A note on tracking down mxds that have your DB account and keep locking it, commonly, your database will be logging IP addresses and sometimes logging the OS user along with the DB user who has logged in. Check with your DBA for help. Alternately, give up on the account and create a new one.

Related Question