topic Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance in Developer

Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

IT_IN_BC — Wed, 24 Apr 2024 17:31:21 GMT

We are integrating SQL with PowerBI. We have one dataset that I am trying to authenticate to the SQL instance and it is failing. We do have the user setup in SQL and it is authenticating with Windows credentials. But it is not authenticating when I set up the data source credentials. I have done research and nothing has worked so far. So I am reaching out.

I wanted to note that the gateway is setup on a server and the user account that I am trying to authenticate which keeps failing does not have access to remote into the server. I am signed into the server with my admin account but the datasources in PowerBI are being managed with a different account.

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

lbendlin — Wed, 24 Apr 2024 23:42:40 GMT

You mentioned Windows authentication. Your screenshot says "basic". Please validate.

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

IT_IN_BC — Wed, 24 Apr 2024 23:47:59 GMT

I am signed onto the server as an administrator. However the authentication for the datasets are under a different account.

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

lbendlin — Thu, 25 Apr 2024 00:28:01 GMT

can you authenticate with that basic user credential in SSMS?

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

IT_IN_BC — Thu, 25 Apr 2024 13:21:00 GMT

That was a good question. I tested and I had to give the PowerBI account I am trying to authenticate the PowerBI dataset to access to the server (NOTE: Not my admin account). I signed onto the server as this PowerBI account account and could get into SQL. However now I am getting a failed to update data source credential message. Below is message:

I am still looking into the message.

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

lbendlin — Thu, 25 Apr 2024 13:55:33 GMT

This is an on-prem data source. Is your gateway set up and ready to be used?

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

IT_IN_BC — Thu, 25 Apr 2024 16:13:26 GMT

I ran into issues with getting the on-premise gateway configured and had to do an emergency gateway migration so it is setup on the server as personal. I was able to get it to work by signing onto the server as the PowerBI account and was able to get the the SQL to sync.

Separate question I kept gett the below errors when I tried to setup a Onpremise gateway

I have verified the domain account for the first error was correct and they have access to the network drives that the source data is housed. THe drives are mapped on the server under the PowerBI user so I do not know why I get getting this issue. Hense the personal gateway was setup because the authentication worked. I am not sure if you may have an idea or suggestion.

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

lbendlin — Thu, 25 Apr 2024 17:03:39 GMT

The enterprise/standard gateway doesn't know what a Z:\ drive is. You should place the files onto a proper network share, or ideally onto a OneDrive/SharePoint (which doesn't require a gateway, by the way).

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

IT_IN_BC — Thu, 25 Apr 2024 17:25:45 GMT

Hello lbnedlin - Thank you so much for your assistance and time. I just want to make sure I am on the same page. So it sounds like the non-personal gateway does not recognized mapped network drives that point to a spearate file server. Am I understanding this correct? Good information to do the Datasets on a OneDrive/Sharepoint site. I did not realize it did not require a gateway then. I do know that it is a recommended best practice to store the source data via OneDrive/SharePoint from what I read and not locally. Sadly that is not how a department in our company did it and other locations are following that same behavior from what they were shown. Trying to make this as easy as possible. Again thank you for your help and your continued assistance.

Re: Authentication Issue In PowerBi When Updating Data Source Credentials for SQL Server Instance

lbendlin — Thu, 25 Apr 2024 18:13:09 GMT

correct