Fix Excel Power Query Using the Wrong ODBC Credentials
Question details
The user needs to prevent Excel Power Query from applying the wrong stored ODBC credentials when multiple workbooks connect to the same SQL database.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening two separate Excel workbooks that connect to the identical SQL database via ODBC but require distinct login credentials.
- Observed behavior
- When the first workbook is opened, its credentials are cached globally, causing the second workbook to automatically use the wrong stored credentials and fail to connect properly.
Ensure you close all active Excel workbooks that are connected to the database before modifying credentials, so that the cache is not locked during the reset process.
Clear Stored Credentials and Restrict the Scope Level
This recommended solution clears the incorrectly cached logins and applies new credentials specifically scoped to the database level rather than globally.
Excel sometimes caches Power Query credentials at a global or server level by default. When multiple workbooks access different databases on the same server, this can cause authentication conflicts. Forcing the credentials to apply only to the specific database scope prevents workbooks from sharing the wrong login details.
Launch Excel and navigate to the 'Data' tab on the ribbon. Click on 'Get Data' and select 'Data Source Settings' from the drop-down menu.
In the Data Source Settings dialog, locate your SQL database connection under the 'Global permissions' tab. Select it, click 'Clear Permissions' (or 'Clear All Permissions' if needed), and confirm the deletion.
Close the settings window. Go back to 'Data' > 'Get Data', choose your specific database connector (e.g., ODBC or SQL Server), and enter the connection string to initiate a new connection.
When prompted for credentials, open the 'Database' tab on the left side of the prompt. Look for the 'Select which level to apply these settings to' drop-down at the bottom, and choose the most specific database path available (instead of the server root). Enter the correct username and password, then click 'Connect'.
Update Excel and ODBC Drivers
Use this method if the scope drop-down menu is missing from your credential prompt, which often happens in outdated versions of Office.
Try WPS Office for Seamless Data Management
If you frequently encounter complex credential caching issues or software version limitations in Microsoft Office, consider switching to WPS Office. It provides a lightweight, free alternative that easily handles everyday spreadsheet tasks without the heavy overhead.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the downloaded file and follow the straightforward setup wizard to install WPS Office on your device.
- 3. Open Your Workbooks: Launch WPS Spreadsheets, open your existing .xlsx files, and enjoy a seamless, hassle-free data editing experience.
Frequently Asked Questions
Why doesn't the 'Select which level to apply these settings to' drop-down appear in my Excel?
This specific credential scoping feature was introduced in later builds of Microsoft 365. If you are using an older version of Excel (like early builds of version 16.0), you need to update your Office suite via File > Account > Update Options to access this drop-down.
Will clearing my Data Source Settings delete my Power Query steps?
No. Clearing permissions in the Data Source Settings only removes the cached usernames and passwords used to authenticate the connection. Your actual queries, M-code, and transformation steps remain completely intact.
Does this issue happen with other database types besides SQL via ODBC?
Yes, Power Query's global credential caching can affect various data sources, including native SQL Server connections, Web APIs, and OData feeds if multiple queries point to the same host but require different credentials.
