Model Refresh with Azure SQL and Report Server

[chris2]

Hi,

I am trying to connect to an Azure SQL Database from an on-prem report server (sep 2021). I would like to use "Username and Password" as authentication methode (https://learn.microsoft.com/en-us/power-bi/report-server/data-sources), but I don't know how to configure it.

I had no problem to connect to the Azure SQL Database via Power BI Desktop.

I used the "Microsoft account" option to authenticate using Power Bi Desktop. This option is not available when configuring the reporting server.

Thanks for your help!

[chris2]

Hi @d_gosbell.

thank you for your reply. Using a SQL Login is working. I tried this already. But for security reasons I would like to use the Azure AD - Username and Password option as shown here (screenshot is from the SQL Management Studio): 

[d_gosbell · Accepted solution]

---

@chris2 wrote:

... for security reasons I would like to use the Azure AD - Username and Password option

---

Sorry, but you cannot do this. AzureAD is based on OAuth authentication and this is not supported with Report Server (see the first sentence in this section Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn)

You can only use Azure AD authentication with the cloud service.

[d_gosbell]

---

@chris2 wrote:

Hi,

I am trying to connect to an Azure SQL Database from an on-prem report server (sep 2021). I would like to use "Username and Password" as authentication methode (https://learn.microsoft.com/en-us/power-bi/report-server/data-sources), but I don't know how to configure it.

---

To do this you will need to make sure that SQL logins are enabled on your Azure SQL instance and then create a SQL Login with a Username/Password. You would then use this username/password combination in Report Server