How to set up Microsoft SQL Azure connection in SSRS Manager Data Source

Question

Hi Friends,

I am having SSRS report manager with 2008R2 Version. we already our all reports in this server. Now I am creating new report In Visual Studio 2019. Report data source is Azure SQL.

Connection: Microsoft Azure SQL Database

Authentication: Active Directory Password Authentication

I am querying Azure synapse with using Openrowset Query. its working fine and giving exact results .

i have deployed the report to SSRS Manager. I am unable to authenticate Data source using above credentials. in fact im not sure what options to choose.

Failing to understand why my report is not having any connection problem in the visual studio where as facing problem in ssrs manager.

I have tried to authenticate uploaded report in the manager then unable to authenticate.

i tried creating new data source in the manager not able to create.

Can you please help me in this

--Connection String---

Data Source=ondemand.sql.azuresynapse.net,1433;Initial Catalog=;EncXYZrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password"

Answer 1

Hi @MSBI_Developer_1990

You can check out this official documentation: https://learn.microsoft.com/en-us/sql/reporting-services/report-data/sql-azure-connection-type-ssrs?view=sql-server-ver16. It describes how SSRS connects to external data sources of the Azure SQL connection type.

You can also refer to this link which introduces the process of creating SSRS report from Azure SQL Database: https://www.sqlservercentral.com/articles/creating-an-ssrs-report-from-an-azure-sql-database.

Best regards,

Aniya

Answer 2

Hi, @MSBI_Developer_1990

The 2008 R2 version of SSRS (and SQL Server) are completely out of support, so I recommend that you upgrade to at least SSRS 2014 which is the oldest version still within support (for one more year). However, even SSRS 2014 does not support connecting through the Azure SQL connection type with Windows authentication (see SQL Azure Connection Type (SSRS) - SQL Server 2014 Reporting Services (SSRS) | Microsoft Learn).

you will need to use SQL authentication if you choose to stay on older versions of SSRS like 2014.

If you want to use Windows authentication, you will need to upgrade to SSRS 2016 or later.

I hope this information helps.

Regards

Geetha