Get data from cloud Azure Dataverse into on-premise SSRS

Question

I try to get data from my cloud azure Dataverse tables (Azure SQL Database) into my On-Premise SQL server reporting services (SSRS).

Thus, dataverse must be added as an additional datasource to SSRS reporting.

• SSRS is running on an On-Premise System (SSRS version 2016+ would be possible)
• Dataverse is running on Azure cloud
• The users are managed in Azure Active Directory - i.e. the users who have access to Dataverse.

Problems:

• SSRS authentication with Azure AD
• SSRS does not support Azure AD MFA
• How can I generate the connection string or how should the connection string look like to connect from SSRS to dataverse

Is it possible to get dataverse as an addional datasource into SSRS?

Answer 1

Hi,

Thank you for posting the question to the Q&A forum.

Yes, it's possible, maybe this will give the answer to your problem: https://stackoverflow.com/questions/73442878/add-dataverse-as-additional-datasource-to-sql-server-reporting-services

I hope the above information can help you.

If the ANSWER is helpful, please click "Accept Answer" and upvote it. Thanks

Answer 2

Hi @kkonstantin ,
SSRS supports SQL authentication and windows authentication, but not AAD authentication; Azure SQL Database supports SQL authentication and AAD authentication, but not Windows authentication (integrated security). If you attempt to connect to SQL Database using Windows Authentication an error occurs. For more information, please refer to: Azure SQL Connection Type (SSRS).
Therefore, when you select Azure SQL Database as the data source in SSRS, you can choose SQL authentication. Please refer to the link attached by @Martin Dimovski for detailed steps to add Azure SQL Database as data source.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @kkonstantin ,
A SQL data connection is available on the Microsoft Dataverse endpoint. As Joy said, I think you can try SQL connections. For more details about procedures and authentication, you can refer to this link: dataverse-sql-query.
Best regards,
Aniya