Long Live SSRS SharePoint Integrated Mode

On 11/17/16 the SQL Server Reporting Services Product Team announced that starting with SQL Server v.Next, there’ll be only one installation mode for Reporting Services: “Native” mode. Check out the blog here – https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/17/simplifying-our-sharepoint-integration-story/

I’ve blogged about SSRS a little bit here and there so making sure everyone is aware of this as you look into future deployments of SSRS!

-AJB

SharePoint SSRS – Orphaned SQL Agent Jobs Causing Subscriptions to Fail

Weird/crazy issue recently. Subscriptions were getting bogged down/failing in a SSRS SharePoint Integrated Mode environment. This issue seemed sporadic..some days subscriptions would fire off at the scheduled times and sometimes they’d get stuck processing for a few hours before users would receive the subscriptions. After some troubleshooting/digging into this issue we noticed something off. We ran a query in SQL to compare the SSRS Subscriptions with the SQL Agent Jobs on the server. These numbers did not match..There were around 70 additional SQL Agent Jobs on the server..and they were not attached to a subscription. Opening one of them up shows that it was a SQL Agent Job created by SSRS for a subscription..but no subscription was associated. Disabling these jobs fixed the issues.

Here’s the SQL script (This renames all SQL Agent Jobs to have a prefix of ZZZZ_ and disables the job):

SSRS Migration – Do not change ReportServer database names

IMPORTANT: Do not rename the ReportServer database. This is unsupported according to Microsoft per:

This is the “official” SSRS migration for SharePoint document (Doesn’t say anything about database renaming..I’m writing this article for the people who probably didn’t see the links above):
https://technet.microsoft.com/en-us/library/hh759331(v=sql.120).aspx

This is why we run always recommend “dry runs” for all migrations! 🙂 There’s a few reasons why it’s unsupported, but I was able to do some digging and found where is hard-coded and how to fix it if needed. In the end your best bet is to revert back to the original database name (ReportServer most likely), but it’s always nice to know and could potentially help someone if they have their heart set on a rename and understand it is unsupported.

  1. This is because the ReportServerTempDB database is referenced in dbo.schedules >Triggers > Schedule_UpdateExpiration
  2. There are 83 stored procedures that reference the ReportServerTempDB database http://sql-articles.com/reporting-services/how-to-rename-your-existing-report-server-database/ 
    • NOTE: As part of the SharePoint service application creation process (Only when you are upgrading the ReportServer database), SharePoint actually goes through and updates all of these stored procedures. This wasn’t needed in this case, but good to know!
  3. Whenever you create a new subscription it creates a SQL agent job. Most SQL Agent jobs (Existing ones. New ones will be fine) have an entry pointing to the original reporting services database name. You could use the following script to update. Or just keep the database name and save yourself some work. I’m showing you this in case the damage is already done. Here’s a sweet SQL script to fix this:
 

SSRS Migration – Permissions Issues Causing “Object Reference Not Set to an Instance of an Object” Errors

SSRS SharePoint Integrated mode has changed throughout the years. In the “olden days” you would manage SSRS (In SharePoint Integrated Mode) through the Reporting Services Configuration Manager tool (There were a few small hooks in Central Admin, but most of the config was in the RSConfig Manager tool). Starting with SharePoint 2010/SQL 2012 this service had a major overhaul. SSRS is now a service application..reports load up way faster..Mostly good things. Mostly..

With this new setup SharePoint requires some additional security. Check these out:

The important thing to note here is you need Edit Items added to read permission level. I actually found this information false. I did NOT need to add the Edit Items permission to the read permission level/create a custom permission level. Users were still able to run reports. Without edit items they could not create a new subscription. BUT with edit items they could find/edit data sources..not so good.

The next thing to note is that many reporting services environment have complex security models. As a SharePoint admin I usually cringe when thinking about folder/file-level permissions, but reporting environments sometimes are the exception. This specific upgrade has a DEEP folder structure. Each level had unique permissions all the way up the tree. The folder above each report dictated who had read access to the reports within it. This is great and worked perfect in SP2007/SSRS 2008 R2, but one IMPORTANT thing to note is that you need at least read access from the top down. Meaning if you have read access to Report A, you need read access to every folder above Report A or else you will get my favorite error ever: “Object Reference Not Set to an Instance of an Object.”

SSRS Migration – Subscriptions don’t work if owner no longer exists

After an SSRS Integrated mode migration (From SharePoint 2007/SSRS 2008 R2 to SharePoint 2013/SSRS 2014) there was an issue with some subscriptions not working. In the past (Back in the day when Reporting Services was not a SharePoint Service Application) you were able to create a subscription as the sharepoint\system account and the subscription would run as that user. In SharePoint 2013 this will NOT work…just like workflows can’t be published using the system account..either can SSRS subscriptions. This is actually a known “issue” with SharePoint-Integrated SSRS in general. If the owner of a subscription is disabled/removed from AD..or no longer has permissions to the report/site/etc. the subscription will no longer work. There is a PowerShell script that updates the ReportServer catalog to change the owner from sharepoint\system to someone else..maybe a sweet new service account..hint hint: http://kitmenke.com/blog/2014/11/25/powershell-script-to-change-ssrs-subscription-owner/

You could accomplish the same using a query against the ReportServer database. I strongly prefer the PowerShell route, but figured we’d cover all our bases. I haven’t found any “official documentation” stating if this is supported or not.

 

SharePoint 2013/SSRS 2014 Trace Logs (and SSRS 2012 too)

ReportServer trace logs (Described here: https://technet.microsoft.com/en-US/library/ms156500(v=sql.120).aspx) can get quite huge during an upgrade. This might be a good candidate to get to a secondary drive along with IIS Logs, ULS Logs, etc.

These logs are stored in the following location (And according to a friend at Microsoft this is the only place you can find the build version of SSRS your SharePoint environment is running): C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\WebServices\LogFiles

Note: They are in this location for SharePoint 2010 (In 14 Hive) and SharePoint 2013 w/ either SSRS 2012 or 2014 running in SharePoint Integrated mode. If this is a native SSRS instance and you somehow found my blog everything still applies. Just look here for the trace logs instead:

C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles

The default max logfile size is 32MB and the default retention is 14 days. These can be tweaked at the following location:

C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\WebServices\Reporting\rsreportserver.config

You could potentially add a custom property called Directory and set it to your secondary drive. Here are those default values I talked about:
<RStrace>
<add name=”FileName” value=”ReportServerService” />
<add name=”FileSizeLimitMb” value=”32″ />
<add name=”KeepFilesForDays” value=”14″ />

SharePoint 2013/SSRS 2014 – Error Activating Reporting Services Integration Feature

I was contacted about a BI site without SSRS content types the other day. I sent them this document and we went through everything on it: https://msdn.microsoft.com/en-us/library/bb326289(v=sql.120).aspx

When trying to deactivate and reactivate the Reporting Services Integration Feature we got the following error..Bwomp

The content type with Id 0x010100C3676CDFA2F24E1D949A8BF2B06F6B8B defined in feature {e8389ec7-70fd-4179-a1c4-6fcb4342d7a0} was found in the current site collection or in a subsite.

First, we tried using brute force with some PowerShell magic:

This successfully activated the feature, but still no content types.

I then went on a PowerShell-ing magical journey to see if I could find if SharePoint was lying to me. It was…

*This script searches the entire web app to see if it can find a content type with ID 0x010100C3676CDFA2F24E1D949A8BF2B06F6B8B. The first line looks to see if the Reporting Services Integration feature is enabled anywhere else.

Since this returned nothing (And I did a few manual checks to make sure PowerShell wasn’t lying to me too. Trust issues..I know) I did some searching online and found some recommended fixes:

  • Most blogs state to use the -Force command like I states above. Even though it does successfully activate the feature…still no content types
  • Tried clearing the SharePoint Config Cache
  • Tried repairing the Reporting Services Add-In on ALL servers
  • Did a SharePoint rain dance..just kidding..maybe

Then I found this awesome official Microsoft article on the Reporting Services Add-In Installation: https://msdn.microsoft.com/en-us/library/aa905871.aspx

There is an area of this article that talks about using a two-step install to troubleshooting issues. This was the Golden Ticket…A normal repair didn’t work, but this two-step install/repair did the trick. I only needed to do the following steps on the SharePoint server running SSRS (This specific farm had 2 servers and these commands did not need to be run on the other server).

I fired up the command prompt (As Admin) and changed directories to the rsSharePoint.msi file (SSRS Add-In install file..You can get this right out of the SQL installation files or go here and grab the appropriate one: https://msdn.microsoft.com/en-us/library/gg426282.aspx#bkmk_sql11sp1)

Msiexec.exe /i rsSharePoint.msi SKIPCA=1

This popped up the Reporting Services add-in installation wizard. I clicked repair as I did before and it completed successfully. The SKIPCA=1 parameter skips installing the Reporting Services Custom Actions and puts another Install file in the %TEMP% location or C:\Users\<your name>\AppData\Local\Temp

With the same command prompt window opened I changed directories to this location and ran the following command:

.\rsCustomAction.exe /i

This is what it should look like on your end..

PowerShellforRS

After that I checked out the BI site’s site content types and look at those sexy beasts:

Content Types

SharePoint 2013/SSRS 2014 – HTTPException Request Timed Out

Here’s the scenario – SharePoint 2013 with SSRS 2014. This is a small 3 tier farm – 1 App (Running SSRS), 1 Web, and 1 SQL server. This farm had been running smoothly for quite some time and started sporadically receiving HTTPException Request Timed Out errors. This seemed to only be affecting 1 specific report (Largest/Most used report in the farm) as I was able to run other reports when the 1 report was acting up.

The end users would just see the typical SSRS loading screen until the 110 second timeout kicks into effect and then the use is presented with a “Request Timed Out” error with a correlation ID. In the eventvwr application log I could see this:

Process information:

   Process ID: XXX

   Process name: w3wp.exe

   Account name: Domain\App Pool Account

Exception information:

   Exception type: HttpException

   Exception message: Request timed out.

After some digging I noticed that the page file on the system had been modified to a static size of 4GB. After changing this to system managed everything started working perfectly (Note: You could also use the Microsoft recommendation of 150% RAM on the system – http://blogs.msdn.com/b/chaun/archive/2014/07/09/recommendations-for-page-files-on-sharepoint-servers.aspx). Recently, I have also seen where search crawls stop working (Running continuously for 10+ days) due to switching the page file to a very low value. Moral of the story – make sure you’re page file is large enough!

-AJB

SQL GDR Update Breaks SharePoint 2013/SQL 2014 SharePoint-Integrated SSRS

The other day the following patch was applied to a SharePoint server running SQL Server Reporting Services 2014:

clip_image002

Information about this GDR: https://support.microsoft.com/en-us/kb/3045324

This was all fine and dandy until we tried to run a report and got the following error:

  • An unexpected error occurred in Report Processing. (rsInternalError)

· Could not load file or assembly ‘Microsoft.ReportingServices.ProcessingObjectModel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. Access is denied.

After seeing an access denied error message my gut was to run the PowerShell command to re-secure resources: Initialize-SPResourceSecurity

This didn’t fix the issue..I ended up coming across the following forum post..Apparently this issue also happened in SQL 2012:  https://social.msdn.microsoft.com/Forums/sharepoint/en-US/5a34109a-4792-4983-9242-8573575bb727/sql-server-reporting-services-2012-sharepoint-integrated-mode-error?forum=sqlreportingservices

The fix was the following:

  1. Backup encryption keys for the SSRS Service Application
  2. Note any other customizations (SMTP Server, Execution Account, Administrators, etc.) and WRITE THESE DOWN..or take screenshots. Screenshots are good
  3. Delete the SSRS Service Application (Uncheck the box to delete data associated..)
  4. Create a new SSRS Service Application. I used the same name, same Report Server database, same application pool, etc.
  5. Restore the encryption key
  6. Make any changes noted in step 2

 

Everything should be back up and running

SSRS 2012 Add-In for SharePoint 2010 Performance Testing

After reading my last post you were probably like “well that’d pretty cool, but why would I want to do that?” Well after finding a few sources on the internet indicating that this may speed up report page load times, I figured I had to see it myself. It definitely increased all page load times and the most notable performance increases was the largest report. In this testing I hit the page 5 times and took the average page load time.

Other Sources:

  1. http://spandps.com/2012/04/30/ssrs-web-part-performance-2008-r2-vs-2012-initial-results-sharepoint-sp2010-in/
  2. http://blogs.msdn.com/b/mariae/archive/2012/04/23/improving-performance-of-reports-for-reporting-services-2008-amp-2008-r2-integrated-with-sharepoint-2010-by-using-the-sql-server-2012-add-in.aspx

Again, the SSRS 2012 Add-In is available for free download on the Microsoft website (http://www.microsoft.com/en-us/download/details.aspx?id=29068). One important thing to note is that any SSRS web parts will need to be recreated in the environment as the 2012 Add-In updates the web part (Microsoft.ReportingServices.SharePoint.UI.WebParts.ReportViewerWebPart) from version 10.0.0.0 to 11.0.0.0.

Normal Report Page:

Row Count (From dbo.executionlog) 2008 R2 SSRS Add-In Page Load (In Seconds) 2012 SSRS Add-In Page Load (In Seconds) % Speed Increase
70066 5.3090059 3.8353092 38.424457
21320 1.4462438 1.3420621 7.7628104
16318 0.656880567 0.587642967 11.782256
6210 1.1072249 1.097880133 0.8511646

 

Web Part Page With SSRS Web Part (Same Reports):

Row Count (From dbo.executionlog) 2008 R2 SSRS Add-In Page Load (In Seconds) 2012 SSRS Add-In Page Load (In Seconds) % Speed Increase
70066 5.292353533 3.862166 37.030711
21320 1.4832806 1.4392445 3.0596678
16318 0.677473233 0.5520499 22.719565
6210 1.175019933 1.0670111 10.122559

*Note: Page load times were recorded using Fiddler2. When total times (Time Data Retrieval + Time Processing + Time Rendering) were recorded from database (dbo.ExecutionLog) the 2012 add-in sometimes took longer and sometimes took shorter. This is not a great indicator of report speed anyway as the numbers recorded were very inconsistent.

Also, I have successfully tested a rollback of the add-in from the 2012 version back to the 2008 R2 version and remember the web parts will need to be recreated and you will need to reconfigure Reporting Services Integration in Central Admin.