Cannot enter the correct data source credentials

[gpehlev]

Hello everyone. We are using Power BI service and fetching data from Azure SQL Database using DirectQuery. I use two different accounts to access Power BI service and the data source:

• Power BI service - myaccount@company.com (OAuth)
• Azure SQL Database - myaccount-azr@company.com (OAuth)

When I publish a report to Power BI service, I am prompted to enter the data source credentials because myaccount@company.com does not have access to Azure SQL Database. I then enter myaccount-azr@company.com at the sign-in prompt but then I am prompted for an MFA code (we use Azure AD with multifactor authentication) for my account myaccount@company.com.
I suspect that the issue is due to the fact that we use the same ADFS server to authenticate both access attempts. Therefore, when I sign into www.powerbi.com, my browser has already cached the credentials for myaccount@company.com and passes myaccount@company.com as the data source credentials.
Am I missing something or this is just how ADFS works in our scenario?

[v-piga-msft]

Hi @gpehlev,

By my tests, if we connect Azure SQL Database with Direct Query, we should edit credential with Basic.

When you connect to the Azure SQL database in Power BI Desktop, what credentials do you use?

Normally, we should keep the same credentials used by the desktop and Power BI services.

Best  Regards,

Cherry

[gpehlev]

Hello @v-piga-msft,

We do not use Basic authentication. All access to any Azure service must happen with an Azure AD account so conditional access policies may be enforced.
I use the same accounts with Power BI Desktop - myaccount@company.com to sign in and myaccount-azr@company.com for the data source. However, when I publish the report to Power BI online, I need to enter the same set of credentials in a browser popup. This is where things don't work - I am able to sign into Power BI online but when I try to enter the credentials for the database in Settings > Datasets > Data source credentials > Edit credentials, I choose Authentication method = OAuth2, click 'Sign in', enter myaccount-azr@company.com in the 'Sign in to your account' browser popup, and I can tell that the browser is using a cached cookie for myaccount@company.com as I get prompted for an MFA code for myaccount@company.com. There's no getting around that with browsers other than IE.