SharePoint Excel Services and Office Web Apps Server: The following connections failed to refresh

When working with Excel services and external data when you also have Office Web Apps server installed in your farm, you may get the following error when trying to refresh your Excel file:

If you have setup Excel Service unattended service account in secure store and have setup access to your data sources properly and are still getting this error, it may because your Excel files are opening using Excel Web App which doesn’t support external data refresh. You can determine if Excel Web App or Excel Services is being used for opening the Excel files by looking at the URL: if it contains WopiFrame.aspx, it means the file is being opened using Excel Web application and if it has xlviewer.aspx, it means the file is being opened using Excel service application. From the Microsoft site:

https://technet.microsoft.com/en-us/library/ff431685.aspx

Differences between Excel Web App and Excel Services in SharePoint

---

Excel Web App and Excel Services in SharePoint have a lot in common, but they are not the same. Excel Services is available only in the Enterprise edition of SharePoint Server 2013. Excel Web App is available in SharePoint Server 2013 and SharePoint Foundation 2013. Both applications enable you to view workbooks in a browser window, and both enable you to interact with and explore data.

But there are certain differences between Excel Web App and Excel Services in SharePoint. For example, Excel Services supports external data connections, data models, and the ability to interact with items that use data models (such as PivotChart reports, PivotTable reports and timeline controls). Excel Services provides more business intelligence functionality than Excel Web App, but Excel Services does not enable users to create or edit workbooks in a browser window.

For details about the differences between the Excel Web App and Excel Services, see Overview of Excel Services in SharePoint Server 2013 and Comparing Excel Services in SharePoint to Excel Web App.

If your organization decides to use Excel Services instead of Excel Web App to view workbooks in the browser, you can use the Windows PowerShell New-SPWOPISuppressionSettings cmdlet to turn off Excel Web App for Excel workbooks. For more information, see New-SPWOPISuppressionSetting.

 

In order to change the default behavior so that Excel files are opened using Excel services, you will have to suppress Office Web Apps for view operations using the following command followed by IISRESET:

New-SPWOPISuppressionSetting –Extension “XLSX” -Action “view”

This should fix the external data refresh issue.

SharePoint Apps: Custom List Form – Ribbon buttons are greyed out

When working with custom list forms using Visual Studio, by default, you will notice that the ribbon buttons are greyed out. 

You can resolve this issue by adding the <SharePoint:FormToolbar> control under PlaceHolderMain placeholder.

<SharePoint:FormToolBar runat="server"/>

Web performance and load testing SharePoint 2010 and 2013 applications

This article talks about using Visual Studio for performance and load testing of SharePoint 2013 and SharePoint 2013 applications.

 

https://msdn.microsoft.com/en-us/library/jj710162.aspx 

 

Microsoft Exams Free Second Shot for 2015

From the Microsoft Learning site:

 

“Take any Microsoft Certified Professional (MCP) exam between January 5, 2015, and May 31, 2015. If you don't pass, get a free retake!”

More information can be found at https://www.microsoft.com/learning/en-us/second-shot.aspx.

 

Good luck to test takers!

OWSTIMER.EXE Crashing

The OWSTIMER.EXE can crash when the SharePoint services or service application configuration is corrupted. This can happen during patch installation and server configuration updates. When OWSTIMER.EXE crashes, you will notice error messages in the event log and SharePoint logs such as:

• “The SharePoint 2010 Timer service terminated unexpectedly.  It has done this XXX time(s).  The following corrective action will be taken in 30000 milliseconds: Restart the service.”
• Failed to open the file 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Resources\XXXXXX.en-US.resx'.
• <nativehr>0x8000ffff</nativehr><nativestack></nativestack>
• SharePoint Web Services Round Robin Service Load Balancer Event: Initialization Process Name: OWSTIMER
• Various round robin endpoint failures for SharePoint services

You can try the following to correct this issue:

• Make sure that the appropriate services are started on the frontend and application server.
• Make sure that an antivirus is configured properly on the SharePoint servers and it is not causing the OWSTIMER.EXE to crash.
• Make sure that the domain account running the services and service application is valid and has a correct password.
• Reset IIS
• On the central administration server, re-provision the service applications by issuing the following command:
  
  Get-SPServiceApplication | ForEach-Object {$_.Provision()}
  
• Restart the application and frontend servers.
• Run the SharePoint Configuration Wizard on all the servers.

Hope that helps!

LINQ to SharePoint, RunWithElevatedPrivileges, and Anonymous Users

The LINQ data context class that you generate using SPMetal will not work if you try to access a list from this class which is not publicly accessible. This issue is explained really well in the following article and is very helpful in the scenarios when you need to access a list from pages that doesn’t require users to login such as login or sign-in pages. The workaround is to tweak your code to use HttpContext instead of SPContext which generally runs under the current user account. This is explained in the following article.

http://blogs.msdn.com/b/sowmyancs/archive/2010/09/19/linq-to-sharepoint-and-runwithelevatedprivileges.aspx

 

I am providing the code from this site for reference here:

using System;

using System.ComponentModel;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.Linq;

using System.Collections.Generic;

using System.Linq;

namespace LinqWebPart.WebPart1

{

    [ToolboxItemAttribute(false)]

    public class WebPart1 : WebPart

    {

        protected override void CreateChildControls()

        {

            string strUrl = SPContext.Current.Web.Url;

            HttpContext backupCtxt = HttpContext.Current;

            try

            {

                // if there is a SPContext make it is as null so LINQ won’t execute under current context

                if (SPContext.Current != null)

                    HttpContext.Current = null;

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    using (SPSite oSite = new SPSite(strUrl))

                    {

                        using (SPWeb oWeb = oSite.OpenWeb())

                        {

                            // creating a new HttpContext under elevated permission and setting it as current control context web, because of this the code will be running under elevated permission.

                            HttpRequest httpRequest = new HttpRequest("", oWeb.Url, "");

                            HttpContext.Current = new HttpContext(httpRequest, new HttpResponse(new System.IO.StringWriter()));

                            SPControl.SetContextWeb(HttpContext.Current, oWeb);

                            using (DemositeDataContext dc = new DemositeDataContext(oWeb.Url))

                            {

                                var q = from list in dc.Announcements

                                        where list.Title == "My Announcment title"

                                        orderby list.Id

                                        select new { DisplayName = list.Title };

                                //remaining code goes here...

                            }

                        }

                    }

                }

               );

            }

            catch (Exception ex)

            {

                //Use your favourite form of logging to log the error message and exception ....            

            }

            finally

            {

                // make sure that you are setting the actual SPContext back after your operation and make SharePoint happy J

                if (SPContext.Current != null)

                    HttpContext.Current = backupCtxt;

            }

        }

    }

}

Forms Authentication Timeout

The security token in SharePoint forms authentication is issued by Security Token Service or STS. The token is valid for ten hours by default which can be a security concern. You can view or change the security token timeout value by using PowerShell and issuing the following commands:

$sts = Get-SPSecurityTokenServiceConfig

$sts.FormsTokenLifeTime //this will show you the current settings

//Update the timespan

$sts.FormsTokenLifeTime = (New-TimeSpan -minutes 20)

$sts.Update()

//Verify new timespan

$sts.FormsTokenLifeTime 

SPMetal does not Generate EntityList or EntityRef Fields for Lookup Columns

SPMetal can generate three types of fields for lookup columns:

• EntityRef: This type of field is generated for lookup columns that accepts single value.
• EntitySet: This type of field is generated for lookup columns that accepts multiple values.
• LookupList: This type of field is generated for lookup columns for lists that are not available to SPMetal in case they are hidden, for example.

In my case, I imported the lists to a standalone “scratch” site with no content by saving list as templates and importing lists to the new site. When I tried to generate the entity class using SPMetal, it created LookupList field for lookup columns instead of generating fields of type EntityRef or EntitySet. When I further looked into this issue, I found that the lookup columns are not being populated from the source lists. One of the drawbacks of saving list as template and importing it to another site is it looses reference to the lookup columns so those fields were being referenced by SPMetal as hidden fields. I then found a tool called “SharePoint Content Deployment Wizard” which allows you to export SharePoint libraries and lists and can also import any dependencies for lookup columns on those lists. You can also import the list to the target site using this tool keeping the object ID and locations so that the relationship is not broken. After doing that, SPMetal generated the fields correctly.

SharePoint 2010 Full Farm Backup using PowerShell

You can use the following code to take full backup of SharePoint 2010 farm. I found couple of different scripts on the Internet and combined all of them to the following,so thanks to the authors! This script also send an e-mail about backup success or failure.

   1: Add-PsSnapin Microsoft.SharePoint.Powershell

   2: Clear-Host

   3: $Error.Clear()

   4:  

   5: $FromAccount = '<specify from e-mail address to be used in e-mail message>'

   6: $ToAccount = '<specify to e-mail address to be used in e-mail message>'

   7: $smtpServer = '<specify SMTP server>'

   8: $today = (Get-Date -Format yyyy-MM-dd)

   9:  

  10: #if you want to always backup to one directory, remove $today from the following line

  11: $BackupDir = '<specify backup path>\$today'

  12:  

  13: Write-Host -f green "Creating backup folder..."

  14:  

  15: #Comment the following line if you are always backing up to the same directory

  16: [IO.Directory]::CreateDirectory($BackupDir)

  17:  

  18: Write-Host -f green "Initiating backup..."

  19: Backup-SPFarm -Directory $BackupDir -BackupMethod Full -BackupThreads 3 -Force -ErrorAction SilentlyContinue

  20:  

  21: IF($Error[0] -ne $null)

  22: {

  23:     Write-Host -f green "Backup encountered error(s)..."       

  24:     $xmldata = [xml](Get-Content ($BackupDir +'\spbrtoc.xml'))

  25:     $Node = $xmldata.SPBackupRestoreHistory.SPHistoryObject | Where-Object {$_.SPErrorCount -gt '0' -and $_.SPStartTime -gt (Get-Date -DisplayHint Date)} 

  26:     $FailureMsg =  $Node[0] | % {$_.SPFailure}

  27:     $Att = ($Node[0] | % {$_.SPBackupDirectory}) + 'spbackup.log'

  28:     $msgBody = 'An Error occurred while trying to backup your SharePoint Farm. Details : ' + $Failuremsg + 'Attached is the Error Log for additional reference.'

  29:  

  30:     $SMTPClient = New-Object Net.Mail.SmtpClient($smtpServer) 

  31:     $SMTPClient.Send($FromAccount, $ToAccount, 'Error Occured in SharePoint Backup', $msgBody)

  32:      Write-Host -f green "Failure e-mail sent" 

  33: }

  34: Else

  35: {

  36:     $msgBody = "Backup completed successfully on "+"$today"

  37:     $SMTPClient = New-Object Net.Mail.SmtpClient($smtpServer) 

  38:     

  39:     $SMTPClient.Send($FromAccount, $ToAccount, 'SharePoint Farm Backup was Successful', $msgBody)

  40:      Write-Host -f green "Success e-mail sent" 

  41: }

  42: Write-Host -f Green "Operation Complete"

50K+ Views of Nauman Ahmed’s Blog

I am very happy to achieve the 50K+ visits of my blog today! I am glad posts on my blog have helped a lot of IT Professionals. I will keep updating this blog time to time. If you need help or have comments, please feel free to contact me.

 

Thank you!

SharePoint Cumulative Updates Best Practices

You can stay up-to-date on Microsoft SharePoint 2010 Cumulative Updates by regularly going to the SharePoint TechNet site and checking if an update is available. The following guidelines will be very helpful in making sure that the cumulative updates process goes smoothly:

1. Never install a cumulative update without testing it on your development/staging/test servers.
2. If your SharePoint server is setup in a virtual environment, ask your system administration to take a snapshot of all of your servers in the SharePoint farm. This is helpful when things don’t go right and the only way to resolve an error is to wipe out the server and set it up from scratch.
3. Never install an update on production servers during regular business hours. This sounds funny but I had seen junior SharePoint Administrators making this mistake over and over. Ideal time to install updates in production is on the weekend, sad I know :(
4. You must first check what is the current version of SharePoint configuration database by going to System Settings –>   Manage Servers in the Central Administration web site. When you look at the details of the cumulative update on the TechNet site for e.g.; March 2012 Cumulative Update, look for “Download Information” section to find the “File Version” of this update. The file version becomes the SharePoint Configuration Database version after the upgrade process is complete.
   
    
5. The phrase “cumulative update” is very misleading for SharePoint updates. The cumulative update “MAY NOT” contain patches from the previous release. In my experience, previous updates are never included in the current cumulative update package. After determining the version of your configuration database, start with the least recent update package that needed to be install on your servers.
   
6. Download the patch at a network location so that you do not have to download the update for every server in the farm.
7. Verify the patch you are installing by first opening the e-mail from Microsoft and making sure that this is the patch you want to install.
   
   
8. Install the patch in order on every server of your farm. Complete the upgrade process by running the SharePoint Products Configuration Wizard on every server of your farm.  DO NOT install the next cumulative update package without completing the upgrade process.
9. Once the cumulative update package is installed, reset IIS by issuing the IISRESET command in the command prompt or SharePoint Management Shell on every server of your farm. In addition, run Get-SPProduct cmdlet in SharePoint Management Shell on every server with the “local” flag. This will prevent the missing patch error if the SharePoint Products Configuration Wizard fails to upgrade the farm.
   
   Get-SPProduct -local
   
   
10. If SharePoint Products Configuration Wizard fails, close the wizard and run it again. If you get a missing patch error in the second run, exit the wizard and follow step #5.
11. Run the SharePoint Products Configuration Wizard on every server of your farm as it does update some local resources on the server as well.
    
12. After installing the update, go to System Settings –> Manage Servers and verify that the upgrade process is complete on every server. This page will also tell you if you need to run the SharePoint Products Configuration Wizard on a server to complete the update process.
13. Verify that the SharePoint services including the SharePoint Timer Service is running by going to Administrative Tools –>  Services. You may need to update the login information in case the service is not running. If you cannot start a SharePoint service, verify that the password is correct and the account is not locked.
    

Restart your servers just to be on the safe side after the update process is complete.

Perform random testing of your SharePoint web applications and site collections. If you are using InfoPath forms, make sure to test these forms in the InfoPath Client or in the web browser.

Check the Windows Event Logs and SharePoint logs in the 14 hive to make sure that there are no errors after the upgrade. Look for the keywords “error” or “exception” in the log.