In this post I’m going to cover installing SQL Server Reporting Services on a client Operating System.
In my previous post I showed how to use a service principal to call the Azure Billing API without any user interaction. I used the results from that scheduled API call to populate a local database that I’ll be using for reporting. Technically you could call the API every time passing it the required date range and report on that but it is much quicker to do it locally. Secondly I wanted a way to schedule the delivery of the chart/report using SSRS instead of browsing to the report every day. The goal for all of this, is to catch any upwards trend in my Azure usage instead of waiting for my Azure spending limit to kick in.
Side Note: You could use Billing Alerts, it is currently in preview but it is a bit simplistic, you can only set alerts on a specific amount not an amount for a specific interval other than your billing period. For example, I create an alert for $1000 since my average monthly usage is $900 and one of my team members forget to shut down a few VMs while testing. If I used alerts a few days would pass before I pick it up and the $1000 limit is reached. It would be useful if I could set a daily limit which would be based on my average usage plus a bit, this way if it goes over for example $50 per day I will get an alert.
Anyway back to the actual topic of this post. It has been a while since I used SQL Server Reporting Services and it has been even longer since I installed it. I decided to install SSRS on my dev machine to test the scheduling of my Azure usage report. I ran into a few problems that I want to document for future use. Bear in mind this was a Windows 10 pc not a server, you won’t have most of these problems if you are using a server OS. Most of the solutions around permissions was covered already thanks to this post.
- First problem occurred at the very end of the installation, I had an existing SQL instance and I added SSRS to it.
I was using the local administrator account to run the installation and the user had permissions to modify the Temp folder. Turns out you can’t do much about it at this point just continue with the installation and fix the other errors related to this later on.
- Second problem was the SSRS service wouldn’t start, I got “Access Denied” error messages when I start the service. To fix this give the account running the SSRS service permissions to the:
“C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting `
Services”
folder. - Not really a problem but keep in mind when you configure SMTP settings SSRS needs an account name with access to your SMTP server, you can’t use Gmail, Hotmail etc, which makes sense, this will usually be used in an enterprise environment. If you want to test emailing on a schedule and you don’t have a valid account I recommend smtp4dev or setup your own SMTP server in IIS.
- I also saw the following errors in the event log
“Report Server (MSSQLSERVER) cannot load the TERADATA extension.”
and
“Report Server (MSSQLSERVER) cannot load the SQLPDW extension.”
These won’t prevent you from working but it does spam the eventlog, making it difficult when you are trying to troubleshoot real problems. This link will explain how to disable the plugins if you don’t need them or how to install the software components if you need them. - You will also have trouble loading the SSRS website as administrator, this link explains how to fix that and also administrator access for SSMS.
Side Note: The SSRS version you get with SQL Express doesn’t support emailing and scheduling reports. I did a quick google and found some free solutions that email Crystal Reports on a schedule. I didn’t try it myself but it is something to keep in mind for home use. I also tried to use PowerBI but it doesn’t have the ability to schedule and email reports.
