Unable to access DB from Azure Virtual Machine using Gateway

[Anonymous]

Hi Team,

The dashboard is created under this PC and the SQL Server data source is import from cloud server and the server is created using Microsoft Azure. Through design phase, im able to refresh data manually through Power BI App and Desktop App.

So i try to create automation for my dashboard to able to keep refresh by setted time schedule.
Under manage gateway settings, i try to add SQL Server data source from cloud server through this PC, and the error display as below.

 

Been refer through lots of reference and troubleshoot, but still unable to find solution.

Need help though, thanks.

[Anonymous]

Hi Ibarrau,

Currently has switch to standard gateway connection, it seems connected successfully. So i check setting for "dashboard name" > data source settings for certain data it displays:

However for manage gateway settings > data source settings, i have choose:Use SSO via Kerberos for DirectQuery And Import queries

with basic authentications only it connects.
If i didnt choose that option, the connection will failed.

 

Atlthough the connection display it was sucessfull, when i try to refresh dahsboard on power bi app on web, it doesnt refresh at all.

If gateway doesnt work, is there any other option than using gateway to access DB?

Thanks.

[ibarrau]

Hi. You don't have to skip connection. Otherwise we won't know if that's ok. I looks like there is somethign wrong in configuration you made because it can't get to it. If you force to test connection the gateway won't allow the data source unless it's really ready to do it.
Maybe a recap from this video can help you:

https://www.youtube.com/watch?v=Vlv1tbuZNcM

Regards,

[Anonymous]

Hi, Ibarrau.
I think configuration doesnt went wrong since i had tried several ways before that. Refer from error details below:

I assume the problem is come from IP address, i had been tried to insert network port test IP address into virtual machine, but the error remains same.

Any more solutions for these?

[ibarrau]

It's clearly a problem of the VM with the SQL Server. You have to allow remote conections to the server and check if there is something else in the network blocking access. You might need to read about this in microsoft doc or check some youtube videos unless you have a database administrator to ask about it :S

Regards,

[Anonymous]

Hi ibarrau,
Currently connection issue is solved by insert certain region of Public IP from azure services under this website
https://www.microsoft.com/en-us/download/details.aspx?id=56519&ranMID=24542&ranEAID=je6NUbpObpQ&ranS...
Other than that, is there a way to find certain IP address that my current Power BI server using under certain region?

[ibarrau]

I don't think there is another. You can check urls too if you want:

https://docs.microsoft.com/en-us/power-bi/admin/power-bi-allow-list-urls

Regards,

[ibarrau]

Hi. The gateway is used for on premise data. You have to install a client in a machine with access to the data. If your only data source is in Azure SQL Server Database and you haven't configure a vnet around it. Then you don't need gateway. You can just go to "Schedule Refresh" setting for the Dataset in Power Bi Service. Look for the option "Data Source Credentials". You will see a small X and "Edit Credentials". Add the credentials to the SQL Server and you would be ready to go without a gateway.

Hope that helps,

[Anonymous]

Hi Ibarrau,

The data source is created using Microsoft SQL Server Management Studio 18 inside virtual machine using Microsoft Azure, not Azure Cloud Database.

In that case, should i still need on-premise gateway to get access to the data.
Additional, i had download on-premise gateway (personal mode) in cloud server and try out, the personal gateway is available under configuration.

Issue is on credential where error display as below,

The only credential that i can't configure is server and database name.

Thanks.

[ibarrau]

I see. You can try the Gateway in standard mode that is more stable and secure. That one will let you configure from Service the instance and credentials.

Hope that works.