We are just a few hours away from the release of SQL Server 2012 RTM so I guess this article will be quite handy.

The integration between SSRS and SharePoint 2010 has been overhauled and SSRS is now a Service Application. The benefit is that your SSRS instance is no longer “abducted” by SharePoint and you can execute the service application and SSRS native service on the same box.

I have only tried this with SharePoint 2010 SP1 + Dec 2011 Cumulative Update and I am sure the latest CU will have better support for SQL Server 2012 and SSRS. Also note that at this point, SharePoint supports SQL Server 2012 Database Services with the October 2011 CU (or something around that) at a minimum. For SSRS integration only, you must install the SharePoint 2010 SP1.

It doesn’t matter in what order you deploy SharePoint and SSRS as long as they are both on the same box, and joined up to the rest of the farm. To install SSRS 2012. Start the SQL Server Setup and select “SQL Server Feature Installation” at the “Setup Role” step. Click next and select the following components:

  • Reporting Services – SharePoint
  • Reporting Services add-in for SharePoint 2010 Products (a previous version of this component has been deployed as a prerequisite for SharePoint 2010, however this is an updated version.

In the “Reporting Services Configuration” step  you should have only one option available: “Install Only”. Now lets complete the installation.

All components are deployed and now we need to make SharePoint aware of the new service application. In the SharePoint 2010 Management Shell (Elevated) enter the following:

  • Install SPRSService
  • Install-SPRSServiceProxy

We should now be able to see a new Service in Central Admin: “SQL Server Reporting Services Service”. Make sure that this service is started on the box where SSRS 2012 was deployed via System Settings > Services on this Server.

As a next step, we need to provision a service application in Central Admin. Simply create a new service application of type “SQL Server Reporting Services Service Application” and ensure you are using a unique account (for security) and a dedicated application pool (for performance and stability). I always perform an IISRESET after I provision a service application as they tend to act funny sometimes.

You can associate a Web Application to the SSRS SA now during provisioning, by selecting the target web apps in the provision dialog, or you can skip that step for now, and do it later on by selecting the service application and clicking the “Properties” button.

Opening up the Service Application itself, provides additional configuration options;

  • System Settings: allows you to configure behaviour settings for SSRS.
  • Manage Jobs: allows you to manage any background jobs SSRS is currently executing.
  • Key Management: allows you to manage your SSRS encryption key.
  • Execution Account: allows you to specify an account which will be used to access data. It is a good security practice to specify a dedicated account for this. That should allow you to have one read only account lets say to your analysis cube, instead of granting all portal visitors direct access.
  • Email Settings: unfortunately SSRS does not pick up the farm settings so you must re-configure manually here for alerts.
  • Provision Subscriptions and alerts: This enables an SSRS 2012 feature which allows end users to subscribe to data alerts on reports.

I guess the only thing left to do now, is allow our end users to subscribe to data alerts. To do this you will need dbowner on a lot of databases, so you might as well execute this as a sysadmin. The way alerts work is that the SQL Agent processes user subscriptions and notifies users based on the desired frequency and options. Delivery methods include email, document library and file share (SMS or push notifications would of been a nice addition).

To enable them, ensure that SQL Agent is running on the box with SQL Database Services which hosts the SSRS SA databases  and then go to “Provision Subscriptions and alerts” in the SSRS SA and enter the username and password of the account which has access to the application databases,  MSDB and master databases. The account can be SQL or windows but requires that you select “Use as Windows Credential” in the configuration page if so. Alternatively, you can press on “Download Scripts” to get the T-SQL script and execute it on your own.

As a design consideration keep in mind that you are most likely going to need Kerberos authentication if your SSAS or data sources are on other machines and Windows Authentication is used to access them.