“Please specify how to connect”: Handling Anonymous vs. Windows Auth.

The “Please specify how to connect” prompt appears in Power Query when the data engine cannot determine which authentication protocol to apply to a data source. This typically occurs when connecting to web endpoints, internal network shares, OData feeds, or on-premises servers where no default credential model is detected.

Quick Answer:

Choose Anonymous if you are connecting to a public website, open web API, or feed that does not require login credentials. Choose Windows if you are accessing an internal file share, an on-premises SQL database, or a company intranet site. In the Windows tab, select Use my current credentials to pass your active domain login.

---

Understanding the Authentication Choice

When Power Query establishes a connection, it tests the endpoint for supported authentication headers. If the target server does not explicitly negotiate a specific protocol (such as OAuth2 or a database-native login), Excel pauses and displays:

“Please specify how to connect to [DataSource].”

Selecting the wrong option causes immediate connection breaks, commonly returning 401 Unauthorized, 403 Forbidden, or network path errors.

                    [Data Source URL or Path]
                               │
               Does the resource require a login?
              /                                  \
            (NO)                                (YES)
             │                                    │
    Select: [Anonymous]                   Where is it hosted?
                                         /                   \
                               (Corporate Domain)       (Cloud / SaaS)
                                       │                       │
                               Select: [Windows]       Select: [Organizational]

---

When to Select “Anonymous”

Use Anonymous authentication when the destination server does not require user credentials to return data:

• Public Web Pages: Scraping tables from Wikipedia, government databases, or public financial websites.
• Public REST APIs: Free data endpoints that do not require an API key or bearer token.
• API Key via URL or Header: If your M query passes an API key inside the URL query string or in the request headers (e.g., [Headers=[#"x-api-key"="12345"]]), the connection itself should be set to Anonymous. Power Query handles the authentication inside the request rather than at the connection layer.
• Internal Servers without Authentication: Rare internal endpoints configured to allow unrestricted read access.

Warning: Selecting “Anonymous” on an internal network path will fail immediately because the Windows file system requires an authenticated security identifier (SID) to permit read access.

---

When to Select “Windows”

Use Windows authentication (NTLM or Kerberos) when accessing resources governed by Active Directory or your local machine’s user accounts:

• UNC Network File Paths: Shared folders on your corporate network (e.g., \\ServerName\FinanceShare\Data.xlsx).
• On-Premises SQL Servers: Databases configured to use Windows Authentication (Integrated Security). If connecting to SQL, compare domain authentication with SQL user accounts in Troubleshooting “Login failed for user”: Windows vs. SQL Authentication.
• Intranet SharePoint Servers: Older, on-premises SharePoint deployments (2013/2016/2019) running inside your corporate firewall.
• Local Machine Files: In some restricted enterprise environments, local drive paths (e.g., C:\Data\) require Windows credential confirmation.

How to Configure the Windows Credentials Screen:

1. Under Select which credentials to use, select:
   • Use my current credentials: Passes the login of the user currently signed into Windows.
   • Use alternate credentials: Enter DOMAIN\username and password if you need elevated access to a restricted server.
2. Under Select which level to apply these settings to, choose the broadest appropriate path (e.g., \\ServerName\FinanceShare rather than the specific .xlsx file) to prevent repeated prompts for other files in the same folder.
3. Click Connect.

---

What If You Selected the Wrong Option?

If you accidentally selected Anonymous for a protected file share, or Windows for a public web URL, Power Query saves that choice in its local credential registry and will repeatedly fail without prompting you again.

To reset the selection:

1. Go to Data > Get Data > Data Source Settings.
2. Select Data sources in current workbook.
3. Highlight the failing URL or path and click Edit Permissions.
4. Under Credentials, click Edit.
5. Switch between Anonymous and Windows, re-enter any necessary credentials, and click Save.

If the prompt persists or the connection refuses to update, clear the cached path entirely using Managing Data Source Settings: How to clear and reset Global Permissions.

---

When Neither Option Works: The SharePoint Online Exception

If you receive this prompt while connecting to a cloud-based SharePoint Online list or library, selecting either Anonymous or Windows will fail. Modern Microsoft 365 environments reject standard NTLM/Windows handshakes and block anonymous queries.

For SharePoint Online:

• Cancel the prompt.
• Change the authentication type to Organizational Account.
• Follow the dedicated authentication procedure outlined in Fixing “The user was not authorized” when connecting to SharePoint lists.