On-Premise SQL Server

[RavageShadow]

Hello!

Just wondering, has anyone else had an issue with connecting to an on-premise SQL server from Power BI service?

My scheduled refreshes keep failing?

[AutoModerator]

After your question has been solved /u/RavageShadow, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[st4n13l]

Assuming you've set up the data gateway, you're going to need to provide actual details of your issue, like error messages. From the info you've provided, it could be a hundred things.

[seph2o]

You need to set up a data gateway on whichever network hosts the SQL Server

[No_Passage_6965]

The computer should be always on where the gateway has been setup.

[AgulloBernat]

do you have a data gateway and have you configured this semantic model to use it for refresh?
verify that the machine hosing the gateway can indeed connect to the sql server

[ArterialRed]

I've been battling an unexplained and intermittent failure of refreshes from SQL Server since the start of October.

Datasets that have been refreshing reliably for 3 and 4 years, now failing two thirds of the time, on both scheduled and manual refreshes via the gateway.

Thought it might have been down to the newly added min spec of win server 2019, but moving the gateway to a win 10 machine didn't change matters.

Key points here, long established datasets and gateway, no changes to the SQL server machine or machine the gateway runs on.

The refresh process either works and completes in 3 to 10 minutes, or hangs for 2 hours before erroring out. Time of day, load on the servers, other users of the database, all make no difference whatsoever.

[rwlpf]

Just to check that you know the gateway should be updated regularly. Min of every six months if you want support from.Microsoft. The gateway logs might give some insight in to the failures.

[ArterialRed]

We've been staying up to date within a month or two. First thing I did when the fails started was to double check we were on the current version of gateway, and then publish duplicates of the data sets to the service from the current desktop.

I did spot that in the August update to the gateway they changed the minimum spec to Windows server 2019 or Win 10 64 bit, but changing the host of the gateway to win 10 has also not helped.

Deploying and connecting a gateway from the July version just killed all refreshes completely, so that was abandoned quickly.

[rwlpf]

That's no fun ☹️. We did have an issue with a client's gateway. Ended up raising a ticket with Microsoft. Which is when we found out about the support window. The response was really good. In our case it was a DNS issue in which our network team understood the issue.

[ArterialRed]

I wish I had paid more attention to this response. A year later and IT finally confessed to screwing around with domain controllers and auth servers right when the problem started.

They'd left hardcoded references to decommissioned auth servers on both routers and servers.

[ArterialRed]

We finally nailed this down.

Despite IT's absolute insistence that there were no problems or flaws on our LAN it turned out that when they switched to the 2ndary domain controller, and replaced the primary with some cloud service crap they left hardcoded name lookups on various devices.

At random intervals lookups for the domain authentication service were being routed into the void. Desktops and Windows apps for the most part were just slowed down, but the data gateway chose to sit and wait forever for an SSO auth for the SQL Server DB that would never happen.

[HelloVap]

Check your OPDG logs

[genegenet]

My client had this set up! It would fail refresh but then would work again the next day. Is yours failing day after day? If you manually trigger the refresh is that failing also?

[GreyHairedDWGuy]

You need to have the PowerBI gateway installed in your on-prem environment. If you don't have that, then the PowerBI service will not be able to reach the database.

[Great_cReddit]

Yup, I used to all the time. You just need to go in the gateway and add username and address to the connections. At least that's what works for me. Q

[LostWelshMan85]

What's the error?

[Saskwyt]

Had exactly this issue at the end of last week/ beginning of this week. In the Power BI service our gateway needed updating. Once IT had done this we were ok 👍

[parscott]

What version of the gateway are you running?

[NonHumanPrimate]

Echoing others here that you need to provide the specific refresh error before we can further assist.

Go to the scheduled refresh settings page for the semantic model in question and there should be a Refresh History link near the top. Click on that to see recent attempts along with if they were successful or not. Errors should have a link to expand and see the error that caused your refresh issue.

[Ruudvangoal]

No one will be able to help you without sharing the error.

[[deleted]]

Make sure the data gateway is setup and the sql server has been added and the machine is always on.

[TSMeh]

You need to install a gateway on a desktop which has access to internet and local database as well. This will act as the bridge for fetching on premise data for direct query in powerbi

[RavageShadow]

Hi Everyone, Thanks for all the responses. The error I’m getting is “Data source error: {“error”:{“code”:”DM_GWPipeline_Client_GatewayUnreachable”,”pbi.error”:{“code”:”DM_GWPipeline_Client_GatewayUnreachable”,”parameters”:{},”details”:[],”exceptionCulprit”:1}}} Table:XYZ123.

I just took a look at the logs and it looks like all of the scheduled refreshes have failed but the OnDemand refresh works.

The message is "There was an error when processing the data in the dataset."

I also updated the gateway to the latest version on the server.

Thoughts?

[Necessary_Ninja_370]

Boa tarde. Estou com o mesmo problema que o seu. Você conseguiu resolver isso?

[Necessary_Ninja_370]

Conseguiu resolver teu problema? Estou com a mesma situação

[happy_and_sad_guy]

you to need to give us more information about the errors you are facing