Let’s break down the most common reasons this error pops up with your setup, and how to fix them step by step:

1. Confirm Your Authentication Mode & Login Configuration #

SQL Server has strict rules around passwordless logins, so we need to make sure your setup aligns:

• If you’re using SQL Server Authentication:

  • Double-check that your SQL login truly has no password. If you have access to SSMS, run this query to verify:

    SELECT name, is_disabled, password_hash FROM sys.sql_logins WHERE name = 'your_username';
    

    A NULL value in password_hash confirms the login is passwordless. Note: SQL Server requires disabling password policy enforcement (CHECK_POLICY = OFF) to create such logins—if this wasn’t done, the login might have a hidden default password you’re missing.
  • Ensure your SQL Server instance allows SQL Server Authentication: Open SSMS, right-click your server → Properties → Security, and confirm the server authentication mode is set to SQL Server and Windows Authentication Mode.

• If you should be using Windows Authentication (super common in enterprise environments):

  • You might have picked the wrong auth option in Power BI! Instead of manually entering a username, select Windows Authentication in the connection dialog. This uses your current Windows login automatically, so no password is needed.

2. Correct Your Power BI Connection Settings #

Let’s reconfigure the connection properly:

1. Go back to Get Data → SQL Server in Power BI.
2. Enter your host-name:port or IP Address:port and click OK.
3. In the authentication window:
   • For passwordless SQL logins: Select SQL Server Authentication, type your username, leave the password field blank, and check the "Save password" box (oddly, this can bypass some authentication checks even when there’s no password).
   • For Windows Authentication: Just select this option—no username/password entry needed.
4. Click Connect and wait for validation to complete.

3. Verify Endpoint & Permissions #

• Check port connectivity: Use PowerShell to test if your Power BI machine can reach the SQL Server port:

  Test-NetConnection host-name -Port port_number
  

  If this fails, you might have a firewall blocking the connection, or the SQL Server instance isn’t listening on that port.
• Confirm read-only permissions: Make sure your login has both server-level access and database-level read permissions. Run this query in SSMS (if you can):

  USE your_target_database;
  SELECT dp.name AS database_role, sp.name AS login_name
  FROM sys.database_role_members drm
  JOIN sys.database_principals dp ON drm.role_principal_id = dp.principal_id
  JOIN sys.server_principals sp ON drm.member_principal_id = sp.principal_id
  WHERE sp.name = 'your_username';
  

  You should see db_datareader in the results—this is the role that grants read-only access to the database.

4. 64-bit Power BI Specific Checks #

Since you’re using 64-bit Power BI, driver compatibility matters:

• Open the ODBC Data Source Administrator (64-bit) on your machine and confirm you have the latest 64-bit ODBC Driver for SQL Server (version 17 or newer recommended).
• Avoid mixing 32-bit and 64-bit drivers—this can cause silent authentication failures.

Last Resort: Test with SSMS & Clear Power BI Cache #

• Test connectivity with SSMS: If you can connect to the SQL Server using the same credentials (or Windows auth) in 64-bit SSMS but not Power BI, the issue is likely with Power BI’s cached settings.
• Clear Power BI’s data source cache: Go to File → Options and settings → Data source settings, find your SQL Server connection, click Delete, then try reconnecting from scratch.

内容的提问来源于stack exchange，提问作者trans min