Add Dataverse as additional DataSource to SQL Server Reporting Services

Question

I try to connect from On-Premise SSRS 2016 (SQL Server Reporting Services) to Dataverse (Azure SQL Database).

Dataverse must be added as an additional data source to my SSRS reporting.

The company does not use Power BI.

As far as I know, SSRS does not support Active Directory MFA authentication out of the box. I already started a thread here: Connect TO Online SQL Azure DB FROM On-Premise SQL Server SSRS

In Azure AD Portal I deactivated the Multi-Factor-Authentication on my AD user, but still I'm getting the error:

Cannot use 'Authentication=Active Directory Integrated' with 'User ID', 'UID', 'Password' or 'PWD' connection string keywords.

My connection string looks like this:

Server=myServer.dynamics.com,5558; Authentication=Active Directory Password; Database=myDb; User Id=myUser@myDomain.com; Password=myPassword;

So far I haven't found any way to connect from SSRS to Dataverse, do you have an idea if that's possible and how?

Answer 1

To connect from On-Premise SSRS 2016 (SQL Server Reporting Services) to Dataverse (Azure SQL Database).

• First give the name to DataSource, then select connection type as Microsoft Azure SQL Database, then click on Build....

Sample connection string: Data Source=ServerName;Initial Catalog=DatabaseName; Encrypt=True;

ServerName: Microsoft Azure Server Name DatabaseName: Database Name

• Add your Microsoft Azure Server Name , As document suggest it only support SQL Authentication Select Use SQL Server Authentication Enter Username and Password , then Select Database you want to, click on Test Connection then Click on Ok

• It will Automatically Generate the connection string, Click on Test connection, Enter Username and Password, Click on Ok

• And your data source is created Successfully.