PowerBI Service+Gateway with HTTPS SQL Server

[Incipient]

I'm using a remote SQL Server data source over HTTPS (accessible to the internet) using a database account. However when trying to connect to it for refresh via the gateway, it only offers basic/windows/oauth options - no database credentials.

This is going via an on prem gateway that has access to the server - the powerbi service also has access, as it's open to the internet.

Does anyone have any experience with making this work?

[ibarrau]

Hi. The Basic option is the one for database credentials. Then the other two are windows and AD or Azure AD.

I hope that helps,

[Incipient]

Thanks for the reply on this.

Using basic throws the below error:

It works perfectly fine from PowerBI Desktop so the SQL Server is clearly there and working fine. PowerBI Desktop is remote (over the internet) from the SQL Server.

[ibarrau]

You might need IT assistance if you are not the owner of the db. It looks like there is an issue with the database or network because Power Bi Service can't establish connection. Is the gateway in the same machine than the one you are using for Power Bi Desktop?

Even if you can from desktop, ask them if there is any configuration in the database blocking remote connections. Then consider the network and ports in the machine that contains the gateway. Is it blocking IP addresses or something like that? 

I hope that helps,

[Incipient]

I am the owner of the db, so can make any changes we need. There is no IP blacklist or whitelist on the db/server. The port is the standard 1433. 

The gateway and PowerBI Desktop are running on the same machine. PBI Desktop can connect fine to the db, the gateway/service gives me the above error.

[ibarrau · Accepted solution]

Ok. That's good. Now that we have cover that, be sure to rewrite the name of the instance. Try with "," comma instead of ":" or even without the port. There is something in the way it's written now that PowerBi doesn't like.

Regards,

[Incipient]

Oh yes, the PowerBI Service cannot handle "host:port" connections! How very strage. Thank you for the help 🙂