Power BI SQL Datasource Authentication modes

[madan]

From this forum, i understood that Power BI service connects to the SQL datasource using the credentials provided in datasource settings for windows authentication and basic authentication, so it never uses logged in user credentials to connect to SQL datasource?

If its true the what is the difference between basc authentication and windows authentication here? i am talking in perspective of credentials powerbi uses to connect to SQL datasource. is there any document/reference explaining this in detail?

As per my understanding when i use windows authentication in my connection, application uses logged in domain user credentials to connect to datasource but here if powerbi service is using the static credentials provided at design time. Please share your thoughts on this.

[v-micsh-msft]

Hi madan,

Power BI obeys the authentication method implemented at the Data Source (Microsoft SQL server) side. So here the authentication method should be Microsoft SQL server Authentication, check the article below regarding Credentials:

Credentials (Database Engine)

Basic Authentication should be SQL server authentication, check the article below for the differences between them.

Choose an Authentication Mode

If you have any further questions, please feel free to post back.

Regards

[madan]

Thanks Ankit & Michael for your time.

I do have a o365 account (assume that its "madan@abc.com", domain user account "abc\madan") and subscribed for 60-day free Power BI pro. I uploaded a dashboard onto Power BI service from my Power BI Desktop, also installed and configured On-premise data gateway to communicate between power bi service and on-premise data. In PowerBi service, I have configured gateway with the same name as my on-premise gateway then created a datasource with windows authentication referring my upn (madan@abc.com, which is my upn and has on-premise SQL server access) and the connection was successful then i updated my the dataset to use the gateway created and is currently setup with on-demand refresh. so far so good.. once i refresh the dataset, my dashboard is empty and its not showing any errors.

I have no clue on whats wrong here. My dashboard pulls the data from SQL view where i filter the data based on user account executing the dashboard with the SQL function "SUSER_NAME()". I ran trace upon dataset Refresh, trace shows me the LoginName "abc\madan" but the credentials i specified in datasource is "madan@abc.com", however  i have data for my UPN (madan@abc.com) and also for domain account ("abc\madan") so my view returns data in both cases. Same dashboard shows me data when i execute from Power BI desktop.

I am sure many of you would have built dashboards accessing on-premise SQL datasource, please let me know if something wrong in how i setup datasource/dataset or anything.

[v-micsh-msft]

Hi madan,

Apologize for the late response.

If the dashboard could receive data as you configured, then it should be OK.

Usually we would just put the DomainName/UserName in the Windows credentials part, using UPN here would also work. As the authentication is authorized by Windows, once succeed, it would return a user token which contains the DomainNmae/userName attribute, then Gatreway service would use this token to access SQL server.

If the DataSet is configured to use a data gateway, the credentials is managed under the gateways, if there is no issue configuring the data source under the gateway side, then it should be no problem.

Check the article below for more information regarding on-premises data gateway.

The on-premises data gateway

If you need any further assistance on this topic, please post back.

Regards

[ankitpatira]

@madan See below screenshot of sql server connector and type of credentials it required.

In power bi service for reports using sql server data source to either configure schedule refresh of data or use directquery feature you have to utilise power bi gateway that acts as a bridge between report in cloud and data in sql server. When you add data source of sql server to gateway you have to supply credentials that gateway will use to access data in that source and will always use that account regardless of type windows or sql. Check out about that here.