Can't make auto refresh data work in cloud, desktop uses : for port and cloud uses , (comma)

[ipiestudio]

Hello! I configured successfully Power BI desktop to connect to my sql server via ip and port like IP:PORT

Then I connected to cloud and everything was fine until I tried to enable auto renewal of data.

It was a surprise to me that cloud requires , for port, like MSSQL Management Studio, like IP,PORT

But I can't change this value! Cloud would not allow me to change IP and PORT since it gets it from my Power BI Desktop configuration.

How to enable auto renewal or change : to , in the cloud? The field with address is greyed out!

I tried changing address with comma in Power BI Desktop, but it fails and tells address is invalid. 😢

[vanessafvg]

Not quite sure where you are doing this, is this the power bi service? have you installed a gateway which you need for on prem data (i might not understand what you are asking)

[ipiestudio]

@vanessafvg  It's power bi online, I'm trying to connect desktop data to online:

In Desktop address should be like IP:PORT and in Power BI Online like IP,PORT

[vanessafvg]

unless you are storing your data in a cloud service, then you need to install the power bi gateway , have you done that yet?

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

[ipiestudio]

@vanessafvgI connect to the database from Power BI Desktop. Using IP:PORT. I have installed Power BI gateway. If I'm not mistaken, it is getting data from my Power BI Desktop to fill Power BI Online data.

But the problem is, Power BI Online gets the connection configuration and data from my desktop Power BI.

Everything is fine until I try to turn on auto refresh of data. Which then sends error because it does not understand IP:PORT that it got from my Power BI Desktop configuration. I learned online that Power BI Online requires format IP,PORT (like MSSQL Management Studio).

Which I cannot change (you see in the screenshot I sent earlier fields are greyed out). How to provide new format like IP,PORT to Power BI Online?

I'm surprised auto refresh does not understand IP:PORT format and doesn't try to adjust it to neede format 😮

[vanessafvg]

can you post the error that you are getting?

[ipiestudio]

@vanessafvghere is text of an error. Unfortunately, most of it in russian, but the important part with error number is there:

произошла ошибка, связанная с сетью или с определенным экземпляром. Сервер не найден или недоступен. Убедитесь, что имя экземпляра указано правильно и что на SQL Server разрешены удаленные соединения. (provider: SQL Network Interfaces, error: 25 - Недопустимая строка подключения)

(translation: Error occured with network or particular instance. Server not found or not accessible. Make sure that instance name is written properly and that SQL Server allows remote connections. Error 25 - invalid connection string.)

[v-easonf-msft]

Hi, @ipiestudio 

When providing a Port-no, it has to be preceeded with a "," and not a ":" . It should be modified  in the desktop first then pulished to service.You couldn’t directly modify it in PowerBI service .

For more detail,please refer to this  thread  discussing the same error .

Data-Gateway-error 

In addition ,please make sure  you  have added all of the needed ports to the firewall, and enabled SQL Browser.
If you are using personal gateway , you also can try to use a   standard on-premises data gateway to configure your datasource.

Best Regards,
Community Support Team _ Eason