SQL connection in Direct Query mode not working

[aivarsrix]

Hi,

I am using Power BI Desktop. PowerBI in Direct mode fails to connect to SQL Server. But it does load the table and I am able to see and refresh data in Power Query (not making any modifications there - just viewing the data).

If I connect to same DB via Import mode then everything works fine, I am able to make visuals etc.

Basically I do connect to SQL server, I see the data in PowerQuery, all the credentials are correct. But for some reason I am not able to make any reports / visualisations and I get the error:

Error fetching data for this visual

A connection could not be made to the date source with the Name of ‘{“protocol”:”tds”,”address”:{“server”:”server name:port”,”database”:”db name”},”authentication”:null,”query”:null}’.

I have multiple older semantic models with connection to same SQL DB in Direct mode and they work without problems. I can open them in Power BI Desktop, run the refresh, all visuals work. But for some reason I am unable to make new Direct connections to same SQL anymore.

To be clear - I am not publishing anything to Power BI Services at this point. Just working in Power BI Desktop and trying to make a new model which I would then publish to Power BI Services, connect to Gateway etc.

[aivarsrix · Accepted solution]

Seems that we were able to resolve the problem on our own.

For some reason the syntax needed to be changed. Instead of [server name]:[port] it was necessary to use [server name],[port] i.e., we had to replace colon ":" with comma "," and for some reason that worked.

No idea why so and why any older connections still work with ":" and also Import mode works with ":", but I am just glad that problem has been resolved and we are able to move forward with new reports.

[aivarsrix]

Hi @Anonymous,

I do have the latest Power BI Desktop version installed. Also SQL server client drivers seem to be up-to-date.

In this case that you are decribing - wouldn't that then cause same problems and error on the other Power BI models where there are also the connections to the same SQL server database?

I just cannot figure out how is that possible that those connections work selectively - old ones work without problems, but new ones don't.

[Anonymous]

Hi @aivarsrix 

The error message mentions an issue with the tds protocol. This could indicate an issue with how Power BI is trying to communicate with SQL Server. Make sure the SQL Server client libraries are up to date on your machine, as older versions might not fully support newer features in Power BI.

Solution: Ensure that you have the latest version of Power BI Desktop and the necessary SQL Server client drivers installed. You can also try connecting with a different authentication method (e.g., Windows Authentication vs. SQL Server Authentication).

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.