Azure hybrid solutions with on-premise SSRS Server

For the moment, SSRS (SQL server reporting services) is currently not an Azure component. Will it be available in the near future…? I don’t know. SSRS- functionalities become available in other dashboards and reporting tools like Power BI. Today, you’ll need to work with an on-premise installed SSRS server or you’ll have to setup one in an Azure VM. But essentially, these are the same situations, a standard native SSRS server installation. When building your HYBRID cloud platform, you can integrate on-premise SSRS server or VM with SSRS in your architecture. Not an easy task to do? Well, depending on … like always.

SSRS standard reports and reporting functionalities visualize business application data stored on a lot of different places. But they are accessible by defining the correct data source in your report solution. So far, no issues using data stored in Azure services. Some other SSRS features are a little complicated to tackle. Today I want to show you one method to use for the integration of PDF generation, defined in data driven subscriptions on your current reporting server installation.

Case definition:

  • On-premise SSRS 2017 installation
  • Existing data-driven subscription for generating PDF documents.
  • All generated PDF’s must be stored in Azure blob-storage as the archive
  • For some files, also send to a mail destination

Case purpose:

  • Use as much as possible from the existing processes.
  • Business functionality untouched
  • Generate new PDF on request (continued to store it in the archive and mail it directly)

Case solution:

A high-level overview of the necessary things to do when you want to achieve this scenario.

A short explanation of this, giving the step by step executed processing parts:

  1. Press button ‘print invoice’ – reopen current report/invoice with flag ‘button pressed’ on report server portal
  2. Report opens, and datasets are re-executed à procedure ‘Print Invoice’ on report server
    1. Insert print request data in application database by executing procedure ‘Print Invoice’ on azure
      1. Add print invoice request in a table on azure database
    2. Start subscription ‘Mail On Request’ by adding trigger to SSRS events database on report server
    3. Start subscription ‘Print On Request’ by adding trigger to SSRS events database on report server
  3. SSRS subscription event ‘Mail On Request’ is triggered and PDF is sent with PDF attachment using Database Mail on report server
  4. SSRS subscription event ‘Print On Request’ is triggered and PDF is created on File-Share$ on report server – record inserted in SQL File-Table ‘Printed Invoices’
  5. Trigger on SQL file-table ‘printed Invoices’ is fired
    1.  starts SQL-agent-job ‘flag print request’ on report server (executes as ‘SSRS execution account’ needed because switch security context not allowed to azure)
  6. SQL job-step executes stored procedure ‘Print Invoice Flag Request Done’ on report server
    1. Store pdf to Azure Blob by executing SQL procedure ‘Load Invoice To Azure Blob’ on report server
      1. Executes AZCopy statement using xp_cmdshell on report server
    2. Flag print request by using procedure ‘update Print Invoice Request’ on report server
      1. Update tale ‘Print Invoice Request’ on azure
    3. Delete PDF from file-hare$ on report server

A lot of  security parts where needed to set it up, but there are a lot of components involved too. Be aware that you can/may not always switch the security context at any time in the process. (specific to Azure)


I know it is possible to do this with the current available services and architecture models, but I must admit that the complexity increases. Nevertheless, it will not be blocking you for continuing your Azure migration.

Creating some additional Power-BI dashboards can assist you on maintaining this implementation.


Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...