SQL Sever Connection

[jland47]

I have a report that already has a connection to one SQL server (cloud based), I have this report saved on one drive and also published it, everythign is good.  Now I am trying to add another SQL connection which is stored on a separate on premise server.  I can make a connection if I save the file on a local drive and when I save the file on onedrive, the issue I have is when I go to publish the file the service says it cannot make a connection.  I am not sure what is a good way around this, I've tried different settings, deleting and renetering connections but keep getting multiple different errors from; Power BI Gateway: Received error payload from gateway service with ID 181477: Error logging on username 'as\administrator'..

-Unable to create connection for the following reason: The on-premises data gateway's service account failed to impersonate the user.

-Unable to create connection for the following reason: SQL Server connection failed

Details:

Power BI Gateway: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

[jland47]

I thought it worked, the reports will allow me to publish with no errors now, but when I went to app.powerbi.com to view the reports I get an "Invalid connection creditianals error" with "An exception occured due to on premise service issue".  The visuals will not load now with the new SQL data I I connected too.

[v-xianjtan-msft]

Hi @jland47 

After you install an on-premises data gateway, you need add data sources(SQL Server) to use with the gateway.

In Power BI Services, open Settings and select "Manage connections and gateways", then click New Connection.

Please refer to the following link to learn how to add a SQL Server data source to your on-premises data gateway.

Manage a SQL Server data source - Power BI | Microsoft Learn

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @jland47 - Since your on-premises SQL server requires a connection outside of Power BI’s cloud service, you need to use the On-premises Data Gateway. Ensure that the gateway is correctly installed and configured on a machine in your network that can access the on-premises SQL server.
You can download and set up the gateway by going to Power BI Service > Manage Gateways. Make sure the gateway can communicate with your on-premises server and that your user account has permissions for the gateway.

Step2: 

After setting up the gateway, go to Power BI Service > Settings > Datasets > select your dataset.
Under Gateway Connection, map the data source to the on-premises gateway and ensure that the credentials for the connection are correctly set. You should set up both the cloud SQL server and on-premises SQL server within the same dataset's gateway settings.

Step3:

Make sure you are using the correct authentication method. In many cases, Windows authentication might cause issues if the Power BI Service can't impersonate your local account (like as\administrator).
For the on-premises SQL server connection, consider using Basic authentication with SQL Server credentials or configure the gateway to impersonate a specific SQL login that has permissions on the SQL Server instance.

Step4:

Verify that the on-premises SQL server allows remote connections. In SQL Server Management Studio, go to the SQL Server Configuration Manager and check if remote connections are enabled.

Hope the above steps works to configure the same.

[jland47]

Ok, will try to follow exactly.  Just to make sure I inderstand fully though, because I went in and deleted gateways and reinstalled them, I think I will go delete all of them again and start over.  My scenario, I have my local network pc (has power bi desktop), one server with sql (app-01, its a cloud based server, didnt have a problem with this before trying to add the other server on), and on premise server with sql (rdsvr).  Where should I install the gateway?  On my local pc and add both connections under the same gateway cluster?  Or should each server have a gateway installed on them?

[jland47]

Not sure if I followed your steps exactly but, I got it to work by downloading a new gateway version which let me creat a new gateway connection.  Once I had that done I added it the the gateway cluster already installed and it worked.

[rajendraongole1]

Yes, it is a good solutions by downloading the gateway version

[jland47]

Downloading the gateway version on what pc, should the gateway be on all machines or should it only be on my network local pc?

[rajendraongole1 · Accepted solution]

Hi @jland47 -Install the Power BI Gateway on a single, reliable machine within your local network that is always available. It does not need to be on all machines, only on one central or dedicated PC that has network access to your on-premises data sources and a stable connection to Power BI’s cloud services.

Hope this helps