SQL 2012 SP1 ‘breaks’ Cross-Farm SSRS Service Application

Establishing SQL Server Reporting Services in SharePoint Integrated mode was something of a burden until the release of SQL 2012. With SQL 2005 and SQL 2008 the majority of the configuration steps were manual and implementing delegation for Kerberos, meant editing a number of Reporting Services configuration files – it was quite cumbersome and pretty much open to failure if you didn’t follow all of the details.

The introduction of SQL 2012 improved things dramatically as SSRS could now be implemented as a conventional SharePoint Service Application. Recently I was working with a customer who were trying to publish SSRS reports across two SharePoint 2010 Farms and experience having issues – so much so, that it eventually led to engaging Microsoft. And it’s the outcome of this engagement which is the pertinent point of this blog.

The two SharePoint Farms we attempted to instantiate in a Trusting/Publishing service application set-up were identical in terms of patch-level, while the Publishing Farm was running SQL Server Reporting Services 2012 SP1 CU4 in SharePoint Integrated mode. The Publishing Farm was only recently patched to SP1 CU4 to facilitate the introduction of Power View (this is the minimum patch level requirement).

Steps for the required configuration are captured in the following article which we attempted to follow:

http://blogs.msdn.com/b/joshger/archive/2012/08/23/implementing-the-ssrs-2012-service-application-in-a-sharepoint-2010-cross-farm-environment-part-1-of-2.aspx

http://blogs.msdn.com/b/joshger/archive/2012/08/23/implementing-the-ssrs-2012-service-application-in-a-sharepoint-2010-cross-farm-environment-part-2-of-2.aspx

Establishing a trust between the Farms was not an issue and we passed this part of the configuration with flying colours:

However, as soon as I tried to browse Cross-Farm to the library which contained the SSRS Reports on the Publishing Farm from the Consuming one, I was hit by an error. To reach this stage I would add the Report Viewer Web Part to the page (this Web Part is available once you’ve installed the SSRS Add-in to your Farm and activated the ‘Report Server Integration Feature’.

9

But as soon as a copied the direct link to the report in the ‘Report’ section, or even browsed for the Publishing Farm, I received the error below:

The item http://<publishingfarm>/site/report.rdl cannot be found –> Microsoft.ReportingServices.Diagnostic.Utilities.ItemNotFoundException

Interestingly, the inability to connect to remote SSRS reports was not evident if used the ‘Page Viewer’ Web Part – this worked absolutely fine. However, this web part is not really fit for purpose when trying to display SSRS Reports as it’s not dynamic in its resizing capabilities. For the business requirement, this wasn’t a workaround.

After spending a fair amount of time searching forums and hunting through the ULS Logs for a resolution, I turned to Microsoft for assistance and asked them to quantify whether any steps from the blog article were incorrect or missing?

As things transpired following a number of calls and WebEx sessions, MS came to the conclusion that the behaviour I was witnessing was caused by a code change introduced with SQL 2012 SP1. The Reporting Services product group have assessed the possibility of implementing a fix which should be addressed in the next SQL Service Pack.

So, the lesson to be learnt is stay with SQL 2012 RTM if you want to leverage SSRS as cross-Farm shared Service Application.

 

 

 

 

Export-SPWeb fails – ‘lack of disk space’

The ‘Export-SPWeb’ cmdlet provides a simple way of moving SharePoint content between sites. This command essentially exports all of your content into a series of .cmp files which can subsequently be re-imported to suit your requirements. If you want to dig deeper, you can even rename the files to .cab to expose the key Manifest.xml.

Last week I was exporting an entire site collection (don’t even ask why!) and the content database was around 85GB in size. The drive I was exporting too was 135GB but during the operation, it failed with Powershell reporting that there was ‘not enough space on disk’.

Having done some digging, I realised that the system drive was out of disk space due to that fact that during the export, all of the content appears to be copied to the ‘%USERPROFILE%\AppData\Local\Temp’ directory – which happened to be my system drive with only 40GB of space.

The fix is to remap the %Temp% variable to a drive with suitable space (restart is required)

And now I was able to successfully export the SharePoint content

 

Gantt Chart Web Part error

I recently came across an issue where I was unable to open ‘Site Actions‘ on one particular SharePoint 2010 page – I was halted by the following error:

An error has occurred with the data fetch. Please refresh the page and retry’

The page in question contained a number of Web Parts, and through a method of elimination, I soon discovered that it was the ‘Gantt Chart’ Web Part view which was causing what was seemingly a Javascript error message.

A quick, simple fix was to remove the Gantt Chart Web Part from the page, then re-add and the problem was solved. But what has caused this issue?

The SharePoint 2010 Farm I was working against was fully patched to February 2012 CU, but had recently been updated with the April 2013 SharePoint Foundation security update:

http://support.microsoft.com/kb/2810059

I raised this with MS who were unaware of this issue, but successfully replicated the problem to one of their test environments. (which is always a relief as you’re otherwise left pondering ‘why me’?)

Patching the SharePoint Farm to the full April 2013 CU patch level permanently resolves the problem.

The perils of the SharePoint Cumulative Updates

There has always been trade-off when it comes to applying Cumulative Updates to your SharePoint Farm. You want to keep your Farm stable, secure and up-to-date, but there’s always the concern that an over-aggressive CU may cause some functionality issues for your end user. The recommended stance when it comes to CU’s is to only apply them to your Farm if there’s a compelling reason to do so – it fixes something!

Generally, SharePoint Service Packs are released by Microsoft having undergone a far more vigorous testing cycle in comparison to CU’s, and if there’s anyone out there still running SharePoint 2010 RTM and not SP1 I’d ask the question ‘why’?

Patching SharePoint is pretty simple; download the patch, apply it to every SharePoint Server in your Farm (excluding SQL or FAST), and then run PSConfig (starting with the Server(s) which are running Central Administration).

Despite this simplicity, I recently came across a SharePoint Farm where the build version wasn’t what I expected. The Farm had been manually patched to February 2012 CU but the build version was displaying April 2013 CU – the culprit, a SharePoint 2010 Foundation patch had been deployed, mistaken as a Windows Security patch. (http://support.microsoft.com/kb/2810059). PSConfig had also been run as the databases had been showing the ‘Upgrade is required’ warning, and this has caused the Farm build version to iterate.

Unfortunately there is no roll-back option with SharePoint patches and therefore the only valid approach was to install what was essentially a year’s worth of SharePoint Server patches to bring the Farm comprehensively up to April 2013 CU (http://support.microsoft.com/kb/2775353). And this is where the fun (actually pain) begun.

When I attempted to run the April 2013 CU SharePoint Server patch, I was stopped in my tracks by the following message:


SharePoint believed that the patches were already installed, so the way to circumvent this was to change the following registry value (on all the servers) to reflect the former build version (in this instance, Feb 2012 CU)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0

14.0.06117 (Feb CU 2012)


 

Then, if I pass a switch below to the patch updater executable, we bypass the version check which is causing the update to fail:

ubersrv2010-kb2767793-fullfile-x64-glb.exe PACKAGE.BYPASS.DETECTION.CHECK=1


 

 

 

RSS Viewer Web Part failing after installing KB2810059

I was recently troubleshooting against a SharePoint 2010 Farm where users were suddenly reporting that the RSS Web Part was not loading when it was active on a page which also had the Content Editor Web Part deployed. So, what’s changed?

I soon discovered that although the Farm had only been patched from a SharePoint Server perspective to February 2012 CU, the build number was reflecting 14.0.6137.5002 – which is April 2013 CU. Further investigation led to me discovering that a SharePoint Foundation patch (ttp://support.microsoft.com/kb/2810059) had been deployed by mistake as it was perceived to be a Windows Security patch. Subsequently, any SharePoint site which had Publishing turned on was failing to load the RSS Viewer when it shared a page with the CEWP.

Having consulted Microsoft about this issue, I was pleased to hear that this has been recognised as a bug…….although it won’t be fixed until either June 2013 CU, or Service Pack 2 – whichever is released first.

The suggested workaround was to either ‘Disable Web Page Security Validation’ at the Web App level (OK, it works, but it’s not recommended due to the security concern)

Or, apply the following Javascript:

)      Page Level: Adding a Content Web Part to the page that is having the problem, and using a custom script with it.

a)     Save the following JavaScript to a .txt file and upload the file to any document library in the root of the site. 

<Script Type=”text/javascript”> 

function CustomUpdateFormDigest() 



if(window._spPageContextInfo != null) 



var $v_2 = window._spPageContextInfo; 

var $v_3 = $v_2.webServerRelativeUrl; 

var $v_4 = window._spFormDigestRefreshInterval; 

UpdateFormDigest($v_3, $v_4); 





CustomUpdateFormDigest(); 

</Script>

b)    Add a “Content Editor” web part from “Media and Content” web parts category to the page not rendering web parts.

c)     Edit the “Content Editor” web part and link its content to the .txt file path we created and uploaded in Step 1 using the “Content Link” field.

d)    Hide the web part by checking “Hidden” under “Layout”.

e)     Save and publish the page.

 

 


 

Excel Services & SharePoint 2010 = Confusion!

Over the past few weeks I have received some calls regarding issues with Excel Services and namely, rendering from the browser. Let’s not forget that Excel Services in SharePoint is a whole lot more than the ability to be able to publish and open a workbook without requiring a native client. Business Intelligence scenario’s which allow complex workbooks to connect with external resources and update dynamically, are now becoming fundamental in the corporate workplace and Microsoft have invested a lot of time and effort ensuring Excel Services is still a major reason for investing in SharePoint. (and there’s more offerings to come in SharePoint 2013)

The Excel Services SharePoint 2010 Service Application in its most basic, simplistic and perhaps convention role, allows users to open a workbook in a browser – no great shakes there. However, it doesn’t work OOTB, and therefore in this blog I will show you what to look for if you receive errors when you choose the ‘open in browser’ option.

  • Create ‘Excel Services’ Service Application (ESA)

This can be achieved via Central Admin or Powershell. Remember to start the service once created. If you don’t have an ESA provisioned, you will receive the following error when you attempt to render from a browser:

 

And if you haven’t started the ‘Excel Calculation Services’ from within ‘Manage Services on Server’, you receive the following:

 

  • Grant IIS App Identity SQL access to Content Database

As a best practice, the IIS application pool identity of your Excel Services Application should be a domain/service account. If this is the case (and it should be), this service account needs to be granted SQL ‘dbo’ permissions against the Content Database of your Site Collection(s). This setting is not applied automatically even when you provision Excel Services and determine which proxy group it becomes a member of. If you miss this step, you will see the following error:

Which corresponds to the following in the ULS logs:

Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: The workbook cannot be opened. at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     

Excel Services should now be your default option for open Excel workbooks, although this can be changed on a per Document Library basis if there is a requirement to use a local Excel client instead.

Document Library > Library Settings > Advanced Settings > Default open behaviour for browser-enabled documents: – ‘Open in the client application’


The screenshot above is the default OOTB option, although there is a SharePoint Feature which changes the third option ‘Use the server default (Open in the browser), to ‘Use the server default (Open in the client application) Once activated, all Document Libraries will open in the Client unless directed otherwise



  • And don’t get confused with the Office Web Apps suite!

Remember, if you want to use the Office ribbon to compliment browser rendering, you will have to purchase an ‘Office Web Apps’ license and provision as a convention Service Application:

 

 

 

 

 

The road to SharePoint 2013 MCSE

This week I took my first steps towards gaining the reinvented SharePoint 2013 MCSE by taking (and passing thankfully) the ‘Upgrading Your Skills to MCSA Server 2012’ exam. (70-417).

Microsoft have completely revamped the certification process and personally, I believe this is an astute decision. In order to gain your SharePoint 2013 MCSE, you first need to become a MCSA in Server 2012. So, what MS are essentially saying is that in addition to understanding the application layer, you now need to have an equal appreciation of the infrastructure it’s running on.

There are two routes towards gaining your MCSA 2012, one of which is a handy shortcut, if, like me, you already hold an MCITP (SharePoint 2010).

Route 1 (New to MCSA or hold an MCTS)

Prepare yourself for the long-haul! The Server 2012 MCSA consists of three exams and around 15 days of instructor led tuition (if you have the time and budget). The exams are as follows:

  • 70-410 Installing and Configuring Windows Server 2012
  • 70-411 Administering Windows Server 2012
  • 70-412    Configuring Advanced Windows Server 2012 Services

Route 2 (Upgrade your MCITP or MCSA Server 2008)

This is the shortcut route I mentioned earlier which allows you to earn your MCSA Server 2012 by virtue of sitting ONE exam. And you don’t have to hold the SharePoint 2010 MCITP, the same principle applies if you are certified in Lync, Exchange amongst others. The full list is available from the following link:

http://www.microsoft.com/learning/en/us/mcsa-windows-server-certification.aspx#fbid=aXGmuk-0q5u

Two SharePoint exams then have to be passed (70-331) and (70-332)

http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-331#fbid=aXGmuk-0q5u

http://www.microsoft.com/learning/en/us/Exam.aspx?ID=70-332#fbid=aXGmuk-0q5u

The pass mark for exam number ’70-417′ is 700 out of 1000 – I managed to scramble up to 833. My training consisted of 5-day instructor led tuition and then around 10 weeks of follow-up study using a number or low-spec Server 2012 machines in a test environment. The course itself covered all of the new features and functionality in Server 2012 and highlighted exactly what has changed since Server 2008.

As I don’t come from and Server Infrastructure background, I found the course very demanding as around 50% of the content was a completely alien to me – hence why it has taken me so long to sit the exam since attending the course. All of the other delegates were infrastructure bods with a solid understanding of Server 2008 (most held the Server 2008 MCSA). And they were happily sitting the exam on Day 5 of the course!

Additional learning resources I used consisted of the following:

http://www.amazon.co.uk/Exam-Ref-70-417-Upgrading-Windows/dp/0735673047/ref=sr_1_1?ie=UTF8&qid=1365091310&sr=8-1&keywords=70-417

http://www.microsoftvirtualacademy.com/ (search for ‘Server 2012’)

I will follow-up this blog post with a series of articles titled ‘How I passed Exam 40-417″. Hopefully the tips I can pass on can be beneficially to others who hope to sit this exam.