Life is Just a Bowl of SharePoint – Part 5: Installing SQL Server Reporting Services and Configuring for SharePoint Integrated Mode

Guest Author: Joan Resnick Ehrlich

The Installation Process

The process for installing SQL Reporting Services (RS) is the same as the DB Engine, starting from the Installation section of the SQL Server Installation Center.

The first part of the setup process, the setup files rules check, setup files installation, and setup support rules check, breezed through those with no errors.  Next up was Installation Type.  As I wanted to install RS into the same instance as the DB Engine, I selected “Add features to an existing instance of SQL Server 2008 R2”. The Default Instance MSSQLSERVER, which I had selected for the DB Engine, was preselected. If other instances of SQL Server were running on this machine the drop down arrow would present them as possible selections too.

Under Feature Selection, next, I checked Reporting Services. Note that the DB Engine and Shared Features are shown checked and greyed out (not selectable), having previously been installed for this instance. The Shared Feature directories are also not configurable.

The installation rules check and check for adequate disk space passed with no issues. It was then time to select the service account to be used for the RS service. I created a separate account named sqlrservice for this purpose. I had the same validation error as during the DB Engine installation and had to reselect the account from the drop down box before I was allowed to continue.

Up next, the Reporting Services Configuration screen provided three options: Native Mode Configuration, SharePoint Integrated Mode default configuration, and Install but do not configure the report server. The first two options were greyed out (not selectable) because I was installing RS after having already installed the DB Engine. If RS is not installed at the same time as the DB Engine only the third option is available. I did not know this and as I wanted the second option I was perplexed – what did I do wrong, oh no! Thankfully, a search on [what I thought was an issue] brought me to the explanation in the BOL. Who would have thunk?

The Error Reporting and Installation Configuration Rules check followed. The rules check completed with no errors. Finally, a chance to review the selections made, correct any errors if necessary, and finish the installation, which then completed successfully:

I did get this warning in the Application Log:

I had in the past come across the same warning for a different WMI Provider. I can’t recall which application but the solution was to leave as is. WMI by design produces a warning when a WMI provider runs under the Local System account, as described here at EventID.net, which I must mention is a great source for information and real world experience about Event Log events. KB 891642 provides more insight into the WMI Provider subsystem. I could not find any documentation instructing me to change the account for the ReportingServicesWMIProvider, therefore I left as is.

Report Server Configuration Manager

I now needed to configure RS for SharePoint Integrated Mode. The gist of the entire process is to configure RS for SharePoint integration in the RS Configuration Manager, install the RS Add-in on the SharePoint 2010 server, and complete the SharePoint 2010 configuration via Central Administration. I still have some reading on the finer points of the SharePoint 2010 side to do.

RS Configuration Manager is found in the Programs Menu under SQL 2008 R2 November CTP, Configuration Tools folder:

When launched, it provides a “Connect to a report server instance” dialog box:

Logically, as I only have one instance, that was the instance I wanted. I then proceeded down the left-side menu in order:

1. Under Service Account, “Use another account” was selected and the Account and Password boxes were prepopulated with the service account information I had specified during RS installation:


2. For Web Service URL the defaults were what I wanted. I am not using SSL. I clicked Apply. I then remembered that I had seen a video that showed the Web Service URL being configured after the database was created. So I created the database, went back to this screen and clicked Apply again. Just in case. Sometimes in IT stuff the order matters and sometimes it doesn’t and sometimes the documentation will say so and sometimes it won’t. Sometimes we get in trouble, sometimes we get in trouble no matter in which order we do it, and sometimes we don’t.


3. Under Database, the settings were blank as I had not yet created a database. I selected Change database. A Change Database wizard launched. I selected Create a new report server database:



Next, I chose to connect to the database server using my credentials (as the current logged in user):




Next, I selected SharePoint Integrated Mode and decided on SharePointRS for the database name:




Lastly, I accepted sqlrservice as the account credentials to be used to connect to the Report Server database:




That completed the wizard and the database was created. The main page then displayed the Current Report Server Database and Database Credential as I had configured via the wizard:



4. Email setup is next but I was not ready to configure that yet, so I skipped over it.


5. I also skipped over the Execution Account setting, assuming that if I need to configure this for RS integration in SharePoint 2010 the information will be noted in the corresponding SQL and SharePoint 2010 documentation, and I can do so at that time:


6. I backed up the encryption key. No way was I skipping this:


7. There will be only this one Report Server deployed, so the Scale-out Deployment setting was not applicable:

This completed the RS configuration for now. A quick check showed the RS service was running. But there were two errors in the Application Event Log post-RS configuration. The first:

I made a copy of the RSReportServer.config file to investigate with and found that the UrlRoot value is empty. The default value is http: //servername/ReportServer and that is what I configured. I have not yet read the finer details for configuring SharePoint 2010 with RS so I left as is for now.

The second error was “Failed to compile” dtsinstall.exe. A delayed error from DB Engine installation; likely, the compilation process was still occurring in the background during the RS installation. Add that to the list of “Can’t find solution”. However, I did find that DTS (Data Transformation Services) is deprecated and replaced by SQL Server Integration Services (SSIS). SQL Server 2008 setup does not install support for DTS (See Data Transformation Services (DTS) in BOL) but provides optional 32bit support (See Support for Data Transformation Services (DTS) in SQL Server 2008 in BOL).

The next article will walk through the SQL Server Analysis Services installation.

Guest Author: Joan Resnick Ehrlich

Joan Resnick Ehrlich has been in the IT industry for 15 years and is Corporate IT Administrator for a mid-sized company on Long Island, NY. Prior to entering the industry Joan was a business researcher, and she enjoys combining her research skills with IT work. In addition to SharePoint, her primary responsibilities include Windows Server, Active Directory, Exchange Server, and SQL Server.