Sunday, October 13, 2013

SharePoint 2013 SSRS Integrated Mode Installation Issues

Installing SPS 2013 isn't a very smooth a matter of fact neither was 2010 in the early days. But that said, it isn't too difficult to get by using PowerShell to correct or instantiate missing service applications. I've found that SSRS integration is a major pain in the butt. PowerPivot will install fine, with almost any edition and has an advanced configuration tool that report's accurately what is missing. SSRS is just a shot in the may not work and the error if you get one will be obscure. There were enough got-ya's that writing this post became a must.

This post led me to create this article:

The SQL Server 2012 SP1 installation media is broken and only installs RTM

You will need to install the SharePoint Reporting Services add-in for Service Pack 1 from Microsoft and apply the SQL Server 2012 SP1 on top of the SQL installation in order to correct the version mismatch. SP1 is required to run 2012 in integrated mode with SharePoint 2013.

For Development we used SQL 2012 Developer Edition and need to remember to use SQL 2012 Developer Edition media to setup SSRS.

For Prod we used SQL 2012 Enterprise Edition and needed to remember to use SQL 2012 Enterprise Edition media to setup SSRS otherwise you will receive the following error:

The feature: "The Database Engine instance you selected is not valid for this edition of Reporting Services. The Database Engine does not meet edition requirements for report data sources or the report server database. " is not supported in this edition of Reporting Services.

Also all editions must match up - best place to check is under Control Panel - Add/Remove Programs and check the version numbers match up. But in my case, we needed SQL Server + SP1 + CU6, three separate installers needed to be applied to the SQL Server and BI Server.

Using BI edition (incl SP1) did not work at of Nov-2013 this media has issues, well the version from MSDN anyways.

Once installed you can also install the SSRS add-in from the media should be fine as long as it is upgraded otherwise you need to make sure that the add-in is also the SP1 Version....again emphasizing that the SQL Box = SQL Media (DBEngine) + SP1 + CU6 and the BI Box = SQL Media (SSRS) + SP1 + CU6.

Once you are done with this you can create the SSRS service application...a simple PowerShell script to do this:

# Create service application and service application proxy
$appPool = Get-SPServiceApplicationPool “SSRS AppPool”
$serviceApp = New-SPRSServiceApplication “SSRS Service Application” –ApplicationPool $appPool
$serviceAppProxy = New-SPRSServiceApplicationProxy –Name “SSRS Service Application Proxy” –ServiceApplication $serviceApp

# Add service application proxy to default proxy group.  Any web application that uses the default proxy group will now be
# able to use this service application.
Get-SPServiceApplicationProxyGroup –default | Add-SPServiceApplicationProxyGroupMember –Member $serviceAppProxy

# Grant application pool account access to the port 80 or https for port 443 web application’s content database.
$webApp = Get-SPWebApplication “http://sitename”
$appPoolAccountName = $appPool.ProcessAccount.LookupName()

Once provisioned you will need to ensure that 4 things exist.
1. The Directory for the SSRS web service at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting. If this does not exist it indicates the SSRS add-in did not install properly.
2. Under IIS, the SecureTokenServiceApplication is missing, this is usually the result of a faulty installation and a repair/reinstall will be required - re-provisioning did not re-create this Application under 'SharePoint Web Services'.
2. A corresponding IIS Web Site exists - sometimes the IIS web site will not be created. If you are in CA -> "Manage Service Application" - when you click "Manage" on the SSRS service application you will receive a HTTP 503 Service Unavailable. In the address bar the GUID will be provided and if the reporting folder exists in the hive you can create a new IIS Application underneath "SharePoint Web Services" and map it to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting. Remember to strip the "-" dashes from the GUID when naming the application.
3. That the "SQL Server Reporting Services" is started in CA under "Services on Server". This one got me when I was getting the error: