D365 SSRS reporting from Azure DB

(0) Share

Report

Posted on by awalters

3,079

Still looking into our Online reporting solutions. Looking like we're going to have to go with doing SSRS/SQL reports off a Azure DB (populated by Data Export Service) for a particular subset of reports, as we're just finding Fetch too limited for our needs.

I've found some good step by step guides on setting up the Data Export Service, so that part is now working fine. We've reviewed our security needs, and decided that the reports that are complex enough to need SQL aren't really the ones where we need to worry about security on a record or field level, just "do the right people have access to these reports". So while the lack of filtered views in the Azure DB is annoying, it shouldn't be a big issue if we just have to put user-level security on the reports. It would be great if we could tie these to roles in CRM somehow, but if there isn't a way then it's not a huge deal.

I can also see that we can add the report to D365 via the "Link to Webpage" report style, which is fine.

However, the piece where I haven't been able to find anything in the way of guides is in the middle - specifically, where Reporting Services lives and how it connects to the Azure DB. It looks like an Azure DB does not run SSRS, so Reporting Services has to live somewhere else. Either a totally separate (and therefore needing to be paid) Azure VM, or an on-prem server if you only need them accessible internally, I guess? Is there anything out there on how to set this up? I'll just keep hammering away, but if there's something out there it would be great. I see some previous questions here on the forum but no answers. :-(

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

awalters 3,079 on at

Like (0)

Report

I'd say I'm making progress on this, if you can call "finding out what doesn't work" progress. Having this work with only a standalone SSRS installation and the Azure DB does not work. Not only does SSRS not run on the Azure DB, you can't even store the Report Server databases there. Report server dbs are created with recovery model Full, which is apparently not supported on Azure DB. (social.msdn.microsoft.com/.../cant-configure-ssrs-with-sql-server-running-on-azure)

So looking like a full SQL server is required, in addition to the Azure DB. Either that or theoretically replace the Azure DB with an Azure SQL full VM, though I've only seen hints about this working so far.

One thing I was concerned about was the fact that for on-prem we needed a different report server instance for each CRM instance, since the CRM Reporting Extensions can only point to one MSCRM_Config database. However, I'm noticing that with the Data Export Service there isn't an MSCRM_Config database. Does that mean that 1. we don't need the Reporting Extensions, and 2. we could point to multiple CRM instances from the one reporting server (assuming we stick with 2016, since they removed the ability to multi-instance in SSRS 2017)? Are we losing any functionality by not having the CRM Reporting Extensions?

Was this reply helpful? Yes No
Suggested answer

Abby Kong 6 on at

Like (0)

Report

Hi Allison,

We did the same!

You are right, you need to have your own SSRS, as Azure DB does not have SSRS.

Unfortunately, SSRS need to be installed with a SQL server, so need to be prepared to pay for a full sql server license even though you may only use Reporting service part of it. MSSQL&SSRS can be installed on your on-prem server, and of course, you can choose to use Azure VM.

After that, also make sure to white list the IP of where your SQL server is through Firewall setting of azure DB, this can be done through Azure portal, https://www.sqlshack.com/configuring-the-azure-sql-database-firewall/

The data source server name will be something like YourDB.database.windows.net. You can choose to create a sql user just for the purpose of running reports (similar to the sql user created to export dynamics 365 db).

----------------------------------------------------------------------------------------------------------------

1. we don't need the Reporting Extensions
-- If you mean SrsDataConnector, I think CRM Reporting Extensions is for on-prem instance of Dynamics, it does not make difference by having it or not having it installed on-prem if the instance is online.

The report can be linked to your Dynamics by linking webpage as you mentioned.

If you don't want to expose your report server URL to dynamics users, this is optional and what we did is to use ReportViewer Control in a web project with S2S configuration set up. This way, there is some level of control over report security against dynamics users.

2. we could point to multiple CRM instances from the one reporting server (assuming we stick with 2016, since they removed the ability to multi-instance in SSRS 2017)? Are we losing any functionality by not having the CRM Reporting Extensions?

-- Yes, one reporting server can link to multiple data sources. Even one report can have datasets coming from different data sources.

Regards,

Abby

Was this reply helpful? Yes No
awalters 3,079 on at

Like (0)

Report

Fantastic - this was super helpful! Thank you! I'll work on it today and see if any further questions come up, but think it should all be good. :-)

Was this reply helpful? Yes No
Abby Kong 6 on at

Like (0)

Report

That's great. Glad its helpful.

Was this reply helpful? Yes No
Elijah Stownton 192 on at

Like (0)

Report

As an alternative, you can use a 3rd party tool to connect SQL Azure and Dynamics 365. Try Skyvia - it is a no-code data integration product with a freemium model.

Was this reply helpful? Yes No