Datamart ODBC DSN Timeout

[itchyeyeballs]

Hi all,

Does anyone know if it's possible to set up an ODBC DSN to connect to a Datamart? Our tests keep timing out.

[Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Timeout error [258].
[Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
[Microsoft][ODBC Driver 17 for SQL Server]Unable to complete login process due to delay in login response

Our organisation just upgraded to Gen2 so we are testing functionality. We have successfully connected Excel (awesome, huge step forward) and SSMS but have hit timeout issues using other tools. Specifically, we want to enable an ODBC connection to enable Alteryx to consume the datamart tables. 

Any help appreciated 

[otravers · Accepted solution]

This now works, make sure to have the latest driver:

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/

[itchyeyeballs]

👍

Any idea if they have fixed the other issues? I stopped trying to use them as they were super slow and gave me loads of problems with things like dataflows,

[otravers]

I haven't tried to load a lot of data recently and my early experience was similar to yours. It's probably worth a second look, anecdotally it feels faster but I have yet to try any serious data load this year.

[itchyeyeballs]

I'll see if I can dig out the workspaces I set up to compare. 

As this functionality seems so Beta (compared to the other preview functionality IMO) it feels like it needs a much more detailed and up-to-date communications page from Microsoft. It's being actively pushed to users but I never knew if it was broken or if I was doing something wrong. I'm reluctant to sink much more time into it even though it seems like it has a lot of potential.

[v-henryk-mstf]

Hi @itchyeyeballs ,

For this type of problem, try the following statement Use "ConnectionTimeout" instead:

Source = Odbc.DataSource("dsn=Production", [value=true, ConnectionTimeout=#duration(0, 0, 15, 0)])

Refer to related document:

Odbc.DataSource - PowerQuery M | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[itchyeyeballs]

Thank you for the response, still no luck though, does seem like it's just not supported.

Error message is the same in the original post.

[otravers]

It seems the way they handle authentication is particular as I haven't been able to connect with many tools aside of the officially supported SSMS and ADS. How are you setting up authentication in your DSN? I tried the various AAD authentication options offered by the v18 ODBC SQL driver but like you I'm always getting timeouts when I'm testing the connection.

[JasonPad_]

This is why I get confused.

Last month, I was able to access datamart in Python Notebook and there was running log to prove it.

yet, it doesn't work anymore when I tried to re-run the notebook this week.

I wonder whether it has been blocked somehow. I don't know who could confirm it tho.

[itchyeyeballs]

We have tried all the possible DSN configurations, suspect it's just not supported. Hopefully will come in time.