Azure SQL Database Authentication

[Anonymous]

I believe that I've looked at all the posts on this but I can't find my answer. Sorry if it's already covered.

What is the best authentication method for accessing an Azure SQL database from the Power BI service? Our current approach is to set up a service account which can access the database. We then want to use that as the credentials for the dataset. The idea is that the service account updates the data each night and then we publish that data via an app.

However, when I try to log in with the service account it is always defaulting back to my own Office 365 account. I have discovered that I can log in to the Power BI service with the service account but I'm not sure if that is the correct method.

What route are people using for this?

Thank you in advance for your help.

[v-jiascu-msft]

Hi @Anonymous,

As far as I know, we only need an account that has enough permission. What're your concerns?

Best Regards,

Dale

[Anonymous]

We want this report to update every night - it doesn't need any intervention once that is in place. I'm a contractor so we can use my credentials but once I leave it won't work, which seems not ideal (and the same can be said of anyone that uses their credentials).

Typically we would access SQL Server data using a service account or an AD group (or combination of both), but I can't see how that model applies here.

I was playing around yesterday and did discover that I can log in to Power BI with the service account itself, which could be a solution.

[v-jiascu-msft]

Hi @Anonymous,

Maybe you can use an independent account that belongs to no one. Or just a database account.

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft

That's what we're working towards. We have set up a service account on our network and that is now linked to an Office 365 account. So I'm trying to use service_account@companyname.com to access the data. We know that it works because we've logged in using SSMS and it is successful.

At the screen below, when I enter the service account details it always defaults back to my credentials.

[v-jiascu-msft]

Hi @Anonymous,

Seems good in my test. Though it shows the "Windows" authentication, I still can refresh. I will check out tomorrow to see if there are any issues. 

Best Regards,

Dale

[Anonymous]

Brilliant, thank you. It could be how we've provisioned the accounts in the background.

[v-jiascu-msft]

Hi @Anonymous,

The refresh seems fine. That means the credential is stored successfully. I consulted the Product team. I will share it with you when I get the answer.

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

This is by design. The filed will be reset when the auth dialog is loaded.

Best Regards,
Dale

[Anonymous]

I should have mentioned that this is importing the data, due to the complexity of some of the measures (which aren't supported via DirectQuery).