SQL Server Connection Error WITH POWER BI

[Suresh_BI]

Dear Community experts , i am try to connect SQL Data Base with sql server user authentic through Direct Query after loading or Transfer the data i am getting the error as per screenshot.

[mark_endicott]

@Suresh_BI - Do you get the same if you try to import the data from one table into this model with the same credentials?

[Suresh_BI]

i am not pacing any issue in Import Mode succefully connected and able to load with the same User Credentials.

[mark_endicott]

@Suresh_BI - Ok, there's lots of things you need to check:

Here are some key areas to investigate when Import works but DirectQuery fails using the same credentials:

1. Authentication & Permissions

   • Verify that the login method (Windows vs. SQL auth) supported in Import mode is also enabled for DirectQuery on the SQL server.

   • Check that the user has the necessary server-level and database-level permissions for live queries (e.g. VIEW SERVER STATE, VIEW DATABASE STATE). Sometimes Import mode only requires SELECT rights, but DirectQuery may need additional metadata permissions.

   • Ensure the user isn’t being blocked by an explicit DENY on any objects (views, schemas) that the DirectQuery model touches.

2. Data Source Settings in Power BI Service / Desktop

   • In Power BI Desktop, under File → Options & settings → Data source settings, confirm that the credentials for the SQL source are set to “Windows Credentials” or “Database credentials” consistently—mismatches here can block DirectQuery.

   • If you’re deploying to the Power BI Service, check the Gateway connection:

• Is the on-premises data gateway online and configured to allow DirectQuery?

• Are the gateway’s credentials up to date and mapped correctly to Windows/SQL credentials?

3. Firewall & Network

   • Import mode often caches data and may use a different port (e.g. 1433) or service endpoint than DirectQuery. Confirm the SQL server’s firewall allows inbound connections for both.

   • If you’re using Azure SQL or Managed Instance, ensure the VNet/firewall rules permit traffic from the Power BI gateway (service tag “PowerBI”) for live queries.

4. Encryption / SSL Settings

   • DirectQuery may enforce encrypted connections by default. Check if the SQL server’s SSL certificate is valid and if Power BI’s “Encrypt connection” toggle (in the Advanced options) matches the server’s configuration.

   • If you have a self-signed cert, you may need to disable “Trust server certificate” or install the cert on the gateway machine.

5. Compatibility & Version

   • Ensure your Power BI Desktop version supports DirectQuery against your specific SQL engine (e.g. SQL Server 2016+). Some older servers or compatibility levels don’t support the metadata calls DirectQuery issues.

   • If connecting to Azure Synapse or Azure SQL Data Warehouse, verify you’re using the dedicated connector (not the generic “SQL Server” connector), as the latter may import but not support DirectQuery.

6. Privacy & Data Source Settings

   • In Options → Current File → Privacy, if your data source is marked as “Private” and you’re combining it with others, the mashup engine may disable DirectQuery. Try setting to “Organizational” or “None” for testing.

7. Query Folding & Model Design

   • If your model uses custom SQL or M query steps that break query folding (e.g. invoking transformations before the native query), DirectQuery may not be able to push queries back to the source. Simplify or defer transformations until after connection.

   • Avoid M functions or Power Query steps that are unsupported in DirectQuery (Table.Buffer, complex joins).

8. Diagnostics & Logging

   • Enable SQL Profiler or Extended Events to see the login attempts and errors when DirectQuery is used. Look for authentication failures, permission denied messages, or timeouts.

   • In Power BI Desktop, open File → Options → Diagnostics and turn on tracing. Then inspect the trace logs to pinpoint the exact error.

9. Timeouts & Performance Settings

   • DirectQuery has default command timeout (10 minutes). If a live query takes longer, it might fail silently. In the Data source settings’ “Advanced options,” try increasing the timeout.

   • Check if the SQL server’s resource governor is throttling queries from your user.

By systematically checking authentication modes, gateway configuration, network/firewall rules, encryption settings, connector compatibility, privacy levels, and query folding, you’ll usually uncover the root cause preventing DirectQuery from connecting even though Import works.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!