Connecting to a remote SQL Server using the DirectQuery method

[itays]

Hi,

I have a SQL Server database running on some server on the network. I have another machine on the same network which runs the gateway to that SQL Server database and it functions correctly.

I'm trying to create a live report, which uses the DirectQuery connectivity method, and I cant seem to connect to my data source.

When I use the import method - I can choose my gateway and connect to the source, but using DirectQuery I can't provide any credentials other than server address.

Is there even a way to connect to a remote SQL Server using DirectQuery?

Providing server nameFailing to connect (Probably since no credentials have been provided)

[serives4]

Connecting to a remote SQL Server using the DirectQuery method in Power BI involves establishing a live connection between your Power BI report and the SQL Server database. This method allows you to retrieve and visualize data directly from the SQL Server in real time. Here's how you can do it:

1. Prepare Your SQL Server:

• Ensure that your remote SQL Server is properly configured and accessible from the network where your Power BI application resides.
• Make sure you have the necessary credentials (username and password) to connect to the SQL Server database.
• chiropractor rome, ga

[ToddChitt]

Have you tried with supplying a Database Name? 

[itays]

Thanks for your reply. I have tried providing the database name, still no connection was made.

The database is secured with a username and password and it seems impossible to connect to it without providing any credentials.

[ToddChitt]

>>The database is secured with a username and password and it seems impossible to connect to it without providing any credentials.<<

Is this a surprise to you? What happens when you supply theese? Do you even have them? If not, that's not Power BI's fault.

Me to my mechanic: My car won't start!

My Mechanic: OK, let me have the keys and I will try it.

Me: I don't have the keys.

🙂

[itays]

Obviously I do have the credentials (I did manage to set up the gateway which requires db authentication). As I mentioned before: There is no appropriate place to provide the db credentials as well as choosing to connect to the gateway. What I'm asking is if there is a way to connect to a SQL Server which is NOT on my local machine?

[ToddChitt]

The error in the orgininal post indicated that Power BI could not find the SQL Server. Let's start there. Can you connect to that SQL instance via SSMS or some other SQL client FROM YOUR LOCAL MACHINE?

[itays]

Yes, from my local machine I can connect with a SQL Server client by providing a Username and Passsword

[ToddChitt]

RDP as in Remote Desktop. No, doesn't count. I need you to verify that your LOCAL MACHINE can make a SQL Connection to that server. Do you have SSMS installed locally?

[itays]

The machine which has the gateway installed on it has a SSMS client and can connect to the db machine.

[ToddChitt · Accepted solution]

Your original post indicated that when designing a Power BI model that was to connect to SQL Server, you got an error stating that it could not find that SQL server on the network. You are desiggning from you LOCAL MACHINE correct? Does that LOCAL MACHINE have the ability to connect to the SQL Server?

[itays]

You are right, I tried to connect through my local machine and just now realized the server address im trying to connect to is an organizational IP address. Thanks for the hints, I will take it from here!