Connectivity to sql server on-premise fails from within network. Direct query works though

[JoelEquestrian]

I have setup a hybrid gateway on a server in-network and it works fine to create and publish reports when using direct query against an on-premise SQL Server instance.

When creating a report or changing the data source it is unable to connect via "Import" connectivity to the on-premise SQL Server even though Power BI desktop is located on an on-premise machine. Through SSMS the connection to local SQL works fine. When making the connection to SQL Server it also seems unable to create an encrypted connection and I am not sure why

[JoelEquestrian · Accepted solution]

Problem resolved. The issue was the DBA had provided the incorrect name for the Database and we all had overlooked this. 

Connecting with Direct Query only the SQL Server name was provided so it worked. When connecting with Import Query the SQL Server name and the Database was provided (incorrect DB name) so it failed. 

Once I noticed it was the wrong DB name and corrected it then it worked fine. 

Just for other people as a tip:  ignore "unable to establish encrypted connection" warning when connecting to SQL server. Power BI Desktop automatically tries to establish an encrypted connection but it is not required. If SSL is required for your connections then that needs to be setup and certificates copied. See this blog post here for setting up SSL with a hybrid gateway:  https://community.powerbi.com/t5/Desktop/unable-to-connect-to-the-data-source-using-an-encrypted/m-p...

[amitchandak]

@JoelEquestrian , I also got this warning. But after ok, I was able to connect.

If SQL Server is on the local machine, try to use Windows Authentication, if that is working. If yes, then it might be a credential issue.

[JoelEquestrian]

Hi Amit. That is not the issue. The credentials work without issue whether I login with SSMS using SQL credentials or Windows Authentication

The issue is that Direct Query can make a connection to the SQL server but Import query cannot. There is also a Hybrid Gateway setup but that should not matter when connecting on-premise to an on-premise SQL server.

Is there something special that needs to be setup to allow Power BI Desktop to connect via Import Query to SQL Server on-premise?