SharePoint 2010 – SQL 2012 Reporting Services

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.
To start the SQL Agent access SQL management and right click the “SQL Server Agent” node and choose “Start”.
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.

032612_2308_SharePoint21

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

032612_2308_SharePoint22

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

032612_2308_SharePoint23

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

032612_2308_SharePoint24

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”.

032612_2308_SharePoint25

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

032612_2308_SharePoint26

032612_2308_SharePoint27

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.

032612_2308_SharePoint28

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

032612_2308_SharePoint29

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.

032612_2308_SharePoint210

032612_2308_SharePoint211

Modern approvals in Office 365 with Power Automate and Microsoft Teams
To start the SQL Agent access SQL management and right click the “SQL Server Agent” node and choose “Start”.

032612_2308_SharePoint212

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

032612_2308_SharePoint213

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.

032612_2308_SharePoint214

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

032612_2308_SharePoint215

This needs to and should complete successfully.

Reporting Services

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

Reporting Services

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”

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.

Share this on...

Rate this Post:

Share: