With the release of SQL 2012, Reporting Services got a remake specifically for SharePoint 2010. Reporting Services can now be configured as a service application specifically to allow for scaling within your infrastructure. To enable this in SharePoint you must first have installed the SharePoint component from SQL 2012. You can find this by running the installer for SQL and selecting the following:

Once this is done you need to load the “SharePoint 2010 Management Shell” and run the following command:

This will install the core services into the SharePoint Farm. Once done, run the following command which will provision the service proxy.

You should now be able to run the following command and see a provisioned instance.

This allows us to actually create the Service Application as we would with any of the other out of the box ones. Simply access, “Central Administration > Manage Service Applications > New > SQL Server Reporting Services Service Application”.

As with all service applications you need to provide some base configuration.

Once the top section is complete you are then able to associate it directly with web applications, in my case I do not have any as this is a “vanilla” virtual machine.

Now it is time to watch the magic wheel, spinning away J

Once it has finished it will present you with a completion message and then a link to some further configuration, which will present a message letting you know if the SQL Server Agent service is running.

Once done, you can refresh the page and it should show the correct running status.

In order for the service application work as expected certain permissions need to be assigned to the application pool account. Click the “Download Script” command to get a dynamically generated script that you must then run in the SQL management studio.

The script looks as follows and is simply adding the “RSExecRole” and adding permissions within various databases to the application pool account.

This needs to and should complete successfully.

SQL Reporting Services needs to access the SQL Agent through an account, you must now set this.

Once you have it all completed, I found that I needed to reboot the server to stop myself getting the following error:

“The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.”

Once it was rebooted I could then access the service application by going to “Central Administration > Manage Service Applications > SQL Server Reporting Services”

So now we have our service application created we are ready to use it within our SharePoint Web Applications, I will cover this in a further post.

This article was first published by MVP Liam Cleary. Check out our resource centre for more SharePoint content from Liam and other SharePoint specialists!

Stay tuned for more SharePoint content by joining our community or by following us on twitter or facebook.