How to Prevent Power Query from Disabling AS/400 ODBC Accounts
Question details
The user needs to stop Power Query from repeatedly using outdated credentials, which causes their AS/400 ODBC account to become locked or disabled after a password update.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Connecting to an AS/400 database using ODBC via Power Query after recently changing the account password.
- Observed behavior
- Power Query automatically makes multiple connection attempts with the old password, triggering company security policies that immediately lock or disable the user account.
Ensure you have your new AS/400 account credentials ready and temporarily pause any automatic data refreshes in your workbooks to prevent further lockout triggers while you make these changes.
Clear Outdated Credentials in Windows and Excel
Removing old passwords from the Windows Credential Manager and Excel's Data Source settings stops Power Query from automatically attempting to use them.
Power Query caches credentials globally to speed up data refreshes. When an AS/400 password is changed, Power Query's multi-threaded background checks might fire off dozens of login requests with the cached, outdated password before prompting you for a new one.
Press the Windows key, type 'Credential Manager,' and press Enter. Click on the 'Windows Credentials' tab to view your saved system passwords.
Scroll through the list of Generic Credentials to find any saved entries related to your AS/400 ODBC connection. Click the dropdown arrow next to the entry and select 'Remove'.
Open Excel, navigate to the 'Data' tab on the ribbon, click 'Get Data', and select 'Data Source Settings'.
In the Data Source Settings window, select 'Global permissions' at the top. Locate your AS/400 ODBC data source in the list, click on it, and select 'Clear Permissions'.
Go back to your workbook and click 'Refresh All'. When the connection fails, Power Query will prompt you to enter your credentials. Input your new username and password, then save.
Adjust Account Lockout Policies with Your Database Administrator
If Power Query still causes lockouts due to unavoidable multi-threading retries, server-side security policies may need slight adjustment.
Try WPS Office for Seamless Data Management
Dealing with complex Power Query bugs and credential loop errors in Microsoft Office can disrupt your workflow. WPS Office provides a lightweight, highly compatible, and free alternative with intuitive spreadsheet tools to manage and analyze your data without the heavy system overhead.
- 1. Download WPS Office: Visit the official WPS website and click the free download button to get the latest version.
- 2. Install the software: Run the installer and follow the simple on-screen instructions to set up the suite on your device.
- 3. Open your spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing Excel workbooks to continue your data analysis.
Frequently Asked Questions
Why does Power Query try to connect multiple times simultaneously?
Power Query is designed to be highly efficient, so it often evaluates data previews and schema structures in the background using multiple threads. If a stored password is outdated, all of these simultaneous background requests fail at the same time, triggering a lockout.
Does clearing Excel's Data Source Settings delete my actual queries?
No. Clearing permissions in the Data Source Settings window only removes the saved authentication details (username and password). Your actual queries, transformations, and workbook structure remain entirely intact.
Can I stop Power Query from refreshing automatically when opening the file?
Yes. In Excel, go to Data > Queries & Connections, right-click on your query, and select Properties. Uncheck the box that says 'Refresh data when opening the file' to prevent immediate lockouts upon launch.
Where are ODBC passwords stored in Windows?
Passwords for system-level ODBC connections are typically stored in the Windows Credential Manager. You can find them under the 'Windows Credentials' or 'Generic Credentials' section by searching for Credential Manager in the Windows Start menu.
