Moving SQL Server Reporting Services from Native Mode to SharePoint Integrated Mode

Historically, many of our customers started out deploying their Microsoft Dynamics GP reports to SQL Server Reporting Services (SSRS) in Native mode, meaning that they used a Report Manager site to access their reports.  With the high adoption rate of Microsoft SharePoint Server, many of our customers would like to know how to switch their SSRS instance to SharePoint-integrated mode.

This process is broken up into main steps:

A) Install SharePoint 2010
B) Backup any custom or modified SSRS reports
C) Configure SQL Server Reporting Services to use SharePoint Integrated Mode
D) Setup the integration between SharePoint 2010 and SSRS
E) Create a SharePoint Document Library to house your reports
F) Redeploy the Dynamics GP SSRS reports to your new site

You can review the following steps to perform the switch to SharePoint Integrated Mode:

1) First, you will need to be sure that you have installed SharePoint.  For this blog I'm going to be referencing SharePoint Server 2010 and SharePoint Foundation 2010.  We will also be using SQL Server Reporting Services 2008 R2. 
a. NOTE: in order for SSRS SharePoint Integrated Mode to work correctly you must have SharePoint installed on the same server as SSRS.

2) Part of the SSRS mode change process will involve redeploying the Dynamics GP reports.  If you have any custom or modified reports you'll want to save or export those at this time.

3) The next step is to configure your SSRS instance to use SharePoint-integrated mode
a. Click on Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > Reporting Services Configuration Manager
b. When the window opens connect to your SSRS instance
c. Click on the Service Account tab and verify that the SSRS service is running under a domain account rather than a local one (e.g. NETWORK SERVICE)

d. Click on the tab called Database on the left

e. Click Change Database

f. Click Create a new report server database and click Next

g. Verify your database server name and authentication details

h. Provide a name for the database.  This should be unique (e.g. ReportServer_SP)
i. Mark SharePoint Integrated Mode and click Next

j. Specify the credentials used to connect to the report server database. And click Next

k. Click Next again to create the database

4) When you configure SharePoint you'll want to be sure that you install the Reporting Service Integration.  This should have been added automatically when you installed the prerequisites for SharePoint 2010.

5) You then need to configure SharePoint to integrate with SSRS
a. Click on Start > All Programs > Microsoft SharePoint 2010 Applications > SharePoint 2010 Central Administration
b. Click on General Application Settings

c. Under the Reporting Services section click on Reporting Services Integration

d. Enter your Report Server Web Service URL.  This can be found under the Web Service URL tab of the Reporting Service Configuration Manager window.
e. For Authentication Mode select Windows Authentication
f. Enter credentials of a domain account who is an Administrator on the server where SSRS is installed, then click OK to create the link between the two applications

g. For in-depth information on configuring SSRS in SharePoint-integrated mode you can review the following MSDN article:
http://msdn.microsoft.com/en-us/library/bb326356.aspx

6) Next, you need to create a SharePoint Document Library for your reports to be stored in
a. Open Internet Explorer and navigate to the SharePoint site you want the reports to reside on
b. Once you're on the site where you want the document library to appear click on All Site Content in the navigation pane on the left

NOTE: if you're using a SharePoint site where you've installed Business Portal for Microsoft Dynamics GP you may want to use the GPReports site that was created for this purpose

c. In All Site Content click on the Create link on the top

d. Select the Document Library type and name it something like Reports and click Create

e. Once this is created you will be taken into that Document Library.  Note the URL that it has been given (e.g. http://servername:port/GPReports/Reports)

7) Once you've configured SSRS to use SharePoint-integrated mode you will need use Dynamics GP 2010 R2 (or later) to redeploy the reports:
a. Log into Dynamics GP 2010 R2 as 'sa'
b. Click on Microsoft Dynamics GP > Tools > Setup > Reporting Tools Setup
c. Under the Reporting Services select SharePoint Integrated for the Report Server Mode
d. Enter the SharePoint site (e.g. http://servername:port/GPReports)
e. Enter the Report Library name (e.g. Reports)
f. Use the Deployment Status section to select the companies you want to deploy for
g. Mark Redeploy all reports for selected companies
h. Click OK to run the deployment

NOTE: If you are prompted for credentials or receive an error while attempting to deploy your domain user may not have sufficient rights to the Report Library you created.  You'll need to add your user to the library:
a. Open your Report Library in SharePoint
b. Select Library in the ribbon
c. Click on Library Permissions in the ribbon

d. Click Stop Inheriting Permissions, then click OK on the pop-up window

e. Finally, click Grant Permissions so you can add your domain user and mark the Full Control permission, click OK

f. At this point you can run the deployment from within Dynamics GP 2010 again.

I hope these steps help make your jump to SharePoint Integrated SSRS go all that much more smoothly!

Lucas M.