Deploying SQL Sentry Reports to SSRS
Within the SQL Sentry tools, we have a number of built-in reports that can be run. Either via the reports menu or as Quick Reports from the performance dashboard. Recently I have spoken with a number of customers, and the feedback is that their management really like the reports. However, they were asking if there was a way to let their managers run these reports themselves, without needing them to have access to the monitoring client. And to this, the answer is yes, and we can do it with SQL Server Reporting Services (SSRS).
From within the SQL Sentry client, it is possible to deploy these reports to SQL Server Reporting Services. And I am going to walk you through the process of doing this through the course of this blog post.
The Benefits of Deploying to SSRS
There are a number of benefits that you can gain from deploying our reports to SSRS, the the top three in my view are:
- No need for SQL Sentry Client to execute reports
- Improved Security via SSRS Data source permissions
- Ability to schedule report execution
By granting direct access to the reports via Reporting Services, to non-SQL Sentry users this can ease the burden on supplying reports to other teams and managers. By deploying to a Report Server, this means that they can then run them on demand and export the reports into their desired format.
Deploying SQL Sentry Reports to SSRS
The only prerequisites for deploying these reports to an SSRS server is that you have one, and have permissions to be able to deploy reports, data sources etc. This is because the process for deploying reports from the client will be done in the context of the account under which the client is run. The first task that we need to complete is to configure which SSRS Server we want to deploy our reports to. This is achieved by going to the "Reports" menu and selecting "Report Deployment Settings" as seen in the picture below.
This gives us the "Deployment Preferences" dialogue box, this is where we specify the SSRS server that we will deploy our SQL Sentry reports to. The format of the URL for the web service will vary by version, but there are examples included. Here I am going to deploy to the SQL Server 2014 SSRS Server.
Once we have all of the details set, we can then deploy the reports out to the SSRS Server. In order to do this, you will need to be running the client in the context of a user who has the permissions to deploy reports. When you are ready, simply select "Deploy Reports" on the "Reports" menu.
From here you will be presented with a view of existing folders on the SSRS Server. I recommend you create a folder for the SQL Sentry reports, this way the reports will be isolated from others on the server.
I normally add a folder called "SQL Sentry Reports", makes it nice and neat :-)
Now we have our folder structure, and it is time to hit "OK" to deploy the reports.
The deployment process can take a little while to run, depending on where your report server is compared to where you are running the client from. Once the process has completed you will get a message that the deployment worked fine with no errors. Now you are in a position to go over to Report Manager and configure your security for who can see run the reports.
Post Deployment Tasks
Once the reports have been deployed there are a couple of tasks that it is advisable to perform, these are as follows:
- Configure the appropriate security for the Data Source that is used by the reports.
- Create folders and setup linked reports which you can then use to restrict access to which reports users can see and execute.
Security Configuration
Depending on whether you are going to use Windows or SQL Server Authentication, the execution account or the credentials of the user executing the report to control access. The best way to control access to the data is via the Rights and Roles based security that exists within SQL Sentry. The security configuration you define will restrict which servers are visible to report users.
The Rights Based Security is used to define which targets can be viewed by a user and is configured within the client.
The Role Based Security component is configured within the SQL Sentry database. In general, using an Active Directory security group for access to the database is the option that provides the lowest management overhead when adding/removing users. In this case, the database user associated with the login should be placed into the allow all role and then, place the user to the roles that have the deny_* prefix.
Creating Linked Reports
When there are multiple groups of users who require different collections, or only a subset of the reports available. Linked reports can really add a lot of value for relatively little administrative overhead within Reporting Services. In this case, it would be an idea to create a folder on the report server and then Create a Linked Report for each of the desired reports, placing them in the appropriate folder. You can then grant access to the folder containing the linked reports.