topic Re: Model Refresh with Azure SQL and Report Server in Report Server

Model Refresh with Azure SQL and Report Server

chris2 — Tue, 27 Sep 2022 05:37:40 GMT

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!

Re: Model Refresh with Azure SQL and Report Server

d_gosbell — Tue, 27 Sep 2022 08:09:17 GMT

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

Re: Model Refresh with Azure SQL and Report Server

chris2 — Wed, 28 Sep 2022 05:06:59 GMT

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):

Re: Model Refresh with Azure SQL and Report Server

d_gosbell — Wed, 28 Sep 2022 05:51:33 GMT

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.