Get data from cloud Azure Dataverse into on-premise SSRS

kkonstantin 21 Reputation points
2022-08-23T07:36:34.343+00:00

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?

Azure SQL Database
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,235 questions
Accepted answer
  1. Martin Dimovski 1,551 Reputation points
    2022-08-23T11:07:01.59+00:00

    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

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,381 Reputation points Microsoft Employee
    2022-08-24T02:27:50.02+00:00

    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.

    1 person found this answer helpful.
  2. AniyaTang-MSFT 3,131 Reputation points Microsoft Vendor
    2022-08-24T06:30:01.58+00:00

    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

    1 person found this answer helpful.