'Status code 400' error when connecting SQL data source to on-prem gateway

[alanperrott]

I have a connection to a remote SQL server data-source which works OK in PBI Desktop. I can add the data-source to the gateway in 'Manage Geteways' and the connection is successful. However, when I try to use the on-prem gateway for my datasource it fails with the unhelpful error "Something went wrong...", and a status code of 400. I have restarted the gateway service several times to no avail. I have turned on 'additional logging' but that doesn't show anything. Can anyone suggest why this might be failing, or advise how I can get more diagnostics?  

[vandit007]

Kindly use virtual machine gateway which stays online.

[Sheila]

Same problem also for me.

Any ideas?

[rafaeldamaceno]

I have the same problem. 
In 2 reports, only in 1 i receive this error message. 

*edit - Im using Mysql only on report with error. 

I uinstall and reinstall powerbi gateway, but the problem persists. 

ID da atividade: f0ee2394-e11b-40b3-a53f-70f93f2cacd0

Solicitar ID: bd4f15d7-ecf0-c4cc-ef86-5b151b1fc945

ID de correlação: b642e5aa-26c4-4fe8-cd85-f3757112fa71

Código de status: 400

HoraTue Aug 07 2018 16:25:34 GMT-0300 (Horário Padrão de Brasília)

Versão13.0.6199.204

Cluster URI https://wabi-brazil-south-redirect.analysis.windows.net

[Anonymous]

Hi All,

   I am facing similar issue. See below. Wanted to know if there is some solution for this, or atleast possible reasons for this error message.

Failed to use the selected gateway. Please make sure the gateway is up and running and try again.

Please try again later or contact support. If you contact support, please provide these details.

• Activity ID6fb49f11-a628-4e9c-87f9-5ddd2ad9926e
• Request IDde8d5c11-31b7-6da2-d6cb-e0b07b17cd74
• Correlation ID643476b2-76f9-2a62-0670-9869661c8e49
• Status code400
• TimeThu Jul 09 2020 14:53:20 GMT-0700 (Pacific Daylight Time)
• Service version13.0.13804.93
• Client version2006.5.01795-train
• Cluster URIhttps://wabi-us-east2-redirect.analysis.windows.net/

[Anonymous]

I had the same issue when trying to publish a report. The Dataset used by the report had been published without errors shortly before, BUT: The On-premises gateway configuration was incomplete so the dataset could not refresh. Basically, the error when publishing the report was caused by the mangled gw config.
I was able to solve this by a) reconfiguring the gateway locally in the on-premises-machine and b) complete the gw configuration in the BI service. The final gw test in the Power BI service was successful and i was able to do a dataset refresh. Finally, i could publish my report without any issues.

Hope that helps

[Johnemcb]

For me this issue was combined with the message "(privider:SSL Provider, Error:0 - the recieved certificate has expired.)"

I worked a long time with Microsoft on this – they were very helpful.  In the end I believe it was due to the SQL server setup.  Our SQL server did not have encryption enabled. 

Microsoft asked me to test the following:

Please enable 'encrypt connections'  in PowerBI Desktop in data source settings. we should expect the same "cert expired" failure.

if yes, Please double check the settings on their SQL Server to make sure the cert is configured correctly to support encrypted connections.

When I did that in desktop, instead of connecting I got the "(privider:SSL Provider, Error:0 - the recieved certificate has expired.)" message - indicating that the SQL server was not set up for encrytped connections.

 As Microsoft put it.

Power BI Desktop will fall back to unencrypt connection, but the power bi service side won't. So, Please try this to know whether the setting of the encrypted connection to cause the problem.

 Our database expert – sent me this link describing what he did to the SQL server:

The link to modifying the SQL Server setup:

https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-...

I have read in another posts that suggested simply restarting the SQL server to fix the certificate issue – I believe we have done that before with the result that after a while the error returned.  Now we have enabled encryption and it seems to be working for the time being.

Best of luck

[alanperrott]

Unfortunately I am still having the same problem and getting an unhelpful 'Status code 400' whenever I try to use an on-prem gateway for my datasource 😞

As before, the gateway appears as a suitable online gateway in PBI settings, but when I press the 'Apply' button then I get the 'status code 400' error with nothing significant that I can see in the gateway logs or elsewhere. 

[MaranMichele]

Yes, also for me it's all OK.

[sea-fritz]

Seems that Microsoft fixed this, for me everything running fine now!

[francorg]

IT WORKS AGAIN!!

[MaranMichele]

However this is a serious problem.

[MaranMichele]

Same problem also for me.

Any ideas?

[Johnemcb]

Hi MaranMichele, the only thing related that I could find is a comment on the support sight https://powerbi.microsoft.com/en-us/support/ under the awareness comments Users are unable to schedule a refresh for datasets that use a gateway. Additionally, changing the gateway of an existing dataset will prevent it from functioning. Engineers are aware of the issue and are working on a fix. Next Update: @05/11/2018 17:00 PDT. Best, John

[MaranMichele]

Thank you. It's almost my same situation. So we are in the hands of the delevopers!

[Johnemcb]

Same problem - please help

[sea-fritz]

Same problem 😞 plesae help

[francorg]

Exactly same problem here with on-prem gateway and MS SQL Server source

In the manage gateways section everything works

but when I try to apply the gateway to the dataset, I get the error

Please help.

Thank you,

Roberto

[zoloturu]

@alanperrott,

Did you map Power BI Service login with your Windows admin? See example here - http://community.powerbi.com/t5/Integrations-with-Files-and/Gateway-connected-but-can-not-access-SSA...

[alanperrott]

Thanks for the suggestion. I'm not using SSAS just standard SQL Server, so I'm not sure if mapping users applies. My Gateway is running on the same server as the SQL server database and is using a domain admin account so there shouldn't be a problem with permissions I don't think.

[v-shex-msft]

HI @alanperrott,

Please refer following link to share some detail content to help us clarify your scenario.

Troubleshooting the On-premises data gateway

Spoiler

Tools for troubleshooting

Collecting logs from the gateway configurator

There are several logs you can collect for the gateway, and you should always start with the logs. The simplest way to collect logs after installing the gateway is through the user interface. In the On-premises data gateway user interface, select Diagnostics and then select the Export logs link near the bottom of the page, as shown in the following image.

Installer logs

Copy

%localappdata%\Temp\On-premises_data_gateway_*.log

Configuration logs

Copy

%localappdata%\Microsoft\On-premises Data Gateway\GatewayConfigurator*.log

On-premises data gateway service logs

Copy

C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises Data Gateway\Gateway*.log

Event Logs

The On-premises data gateway service event logs are present under Application and Services Logs.

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

Refresh History

When using the gateway for scheduled refresh, Refresh History can help you see what errors have occurred, as well as provide useful data if you should need to create a support request. You can view both scheduled, as well as on demand, refreshes. Here is how you can get to the Refresh History.

1. In the Power BI navigation pane, in Datasets, select a dataset > Open Menu > Schedule Refresh.

   

2. In Settings for... > Schedule Refresh, select Refresh History.

   

   

BTW, it seems like power bi request can't achieve to your gateway, please double check on your database and gateway status.

400 Bad Request Error: What It Is and How to Fix It

Regards,

Xiaoxin Sheng