Implementing Anonymous Access and Client Integration without Credential Prompting for Office Files in SharePoint

For those who want the best of both worlds and are trying to use anonymous access for certain content within a single web application while keeping Integrated Windows Authentication and Client Integration enabled for internal users, you may notice that anonymous users will be prompted for credentials when opening Office files in a document library configured for anonymous access.

From my research, I have learned this annoying piece of functionality is actually by design in SharePoint, and the vast majority of bloggers have implemented the most common fix of disabling Client Integration on the web application and denying the OPTIONS verb in the HTTP Verbs tab of Request Filtering on the site in IIS. However, I decided not to implement this most common fix because I discovered it makes all Office files open by default in read-only mode and prevents publishing of InfoPath forms to sites in that web application.

Instead, I opted for the following solution:

1) Download jQuery to your WFE servers in C:\Program Files\Common files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\jQuery.

2) Open the site in SharePoint Designer.

3) Under Master Pages, make a copy of the v4.master page, rename it appropriately, and edit it.

4) Add the following lines to the section of your master page:

 <script type="text/javascript" src="/_layouts/jQuery/jquery-1.8.2.min.js"></script>
  <script type="text/javascript">
  function setDownloadHyperlinks()
  {
  $("a")
  .each(function()
  {
if (this.href.match(/.doc$/i) || this.href.match(/.docx$/i) || this.href.match(/.pdf$/i) || this.href.match(/.xls$/i) || this.href.match(/.xlsx$/i))
  if (this.href.indexOf("/_layouts/download.aspx?SourceUrl=") == -1)
  this.href = "/_layouts/download.aspx?SourceUrl=" + this.href;
  });
 }
 </script>

5) Save master page and Set as Default Master Page and Set as Custom Page for the site.

Should now have the best of both worlds.

Append Created Date Column to Document Name using SharePoint Workflow

I recently had a request to change the names of documents in a document library based on a certain time interval. The requirement was to simply append the document creation date to the original name of the document, preceded with an underscore. To accomplish this need, you must first create a calculated column that converts the Created column format to a text format:

Next, create the Workflow using SharePoint Designer. In the drop-down list of Actions, select to Set Workflow Variable. Give the variable a name, and select the String Builder button. Add the Name column, not the Name (for use in forms) or Name (linked to document with edit menu) columns, type an underscore (_), and add the newly created calculated column for creation date.

Next, in the drop-down list of Actions, select to Set Field in Current Item. Set the field to the Name (for use in forms) column and the value to the variable source, which can be seen by selecting Workflow Variables and Parameters as the data source. Lastly, add logic to stop the Workflow.

Save and Publish the Workflow.

Incoming email attachments not triggering Workflow in SharePoint 2010

Quick Tip: If you are experiencing an issue of email attachments not triggering a Workflow on an email enabled document library in SharePoint 2010, run the following STSADM command in your environment:

stsadm -o setproperty -pn declarativeworkflowautostartonemailenabled -pv true

Should fix the issue.

SharePoint 2010 sites experience ‘An unexpected error has occurred’ upon connecting to a User Profile Service Application published from another farm

Issue

Upon establishing a User Profile Service Application Proxy connection to a User Profile Service Application published from another farm, your SharePoint 2010 sites crash with the dreaded unexpected error message:

Upon reviewing the ULS logs, you will notice there is also a login failed error related to the database associated with the User Profile Service Application on the publishing farm:

System.Data.SqlClient.SqlException: Cannot open database "" requested by the login. The login failed. Login failed for user ''.

SqlError: 'Login failed for user ''.' Source: '.Net SqlClient Data Provider' Number: 18456 State: 1 Class: 14 Procedure: '' LineNumber: 65536 Server: ','

I have noticed this issue occurs primarily when using SQL aliasing.

Solution

1)      Create a SQL alias on the consuming farm that matches the alias on the publishing farm. For example, if you have an alias name of SQLDev on the publishing farm, create an alias with the name of SQLDev on the consuming farm, both pointing to database server for the publishing farm.

2)      Restart IIS on each server in the consuming farm. iisreset /noforce.

3)      Restart the Timer service on each server in the consuming farm. net stop sptimerv4 and net start sptimerv4.

PowerShell: Enumerate and Export User Profiles and Properties in SharePoint 2010

Good stuff for this latest blog post from me.

I recently had a request to validate the value of a particular set of properties for all user profiles in SharePoint 2010. PowerShell to the rescue! Save the following script to a .ps1 file.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

# SharePoint site URL
$site = new-object Microsoft.SharePoint.SPSite("http://site"); 

$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);   

$ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)

$AllProfiles = $ProfileManager.GetEnumerator()

write-output "Display Name; AccountName; Supervisor"

foreach($profile in $AllProfiles)
{
$DisplayName = $profile.DisplayName
$AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value
$Supervisor = $profile["Supervisor"].value
write-output "$($DisplayName); $($AccountName); $($Supervisor)"
}

write-output "Finished."

$site.Dispose()

In my example above, I am getting the value for three properties: Display Name, Account Name, and Supervisor.

For the list of PropertyConstants members, see the following link:

http://msdn.microsoft.com/en-us/library/microsoft.office.server.userprofiles.propertyconstants_members.aspx

Export the output of this script to a CSV file by appending > filename.csv to the command. Therefore, the entire PowerShell command would be:

.\filename.ps1 > filename.csv

Filter Announcements List by Month in SharePoint

To filter an announcements list by month on a MOSS 2007 or SharePoint 2010 site, I like to create a new calculated column based on the Created column in the list, and then create filtered views for each month.

1) Create a new calcluated column named Month, and use the following formula:

=TEXT(Created,"yyyy - ")&TEXT(Created,"mm")&TEXT(Created," (mmmm")&TEXT(Created," yyyy)")

The resulting column output will look like this: 2012 - 05 (May 2012)

2) Create a new view for each month.

On the list, go to Settings, Create View, Standard View. Give it a name. Configure your filter options as shown here:

All done.

User Profile Service Redundancy in SharePoint 2010

For those looking to implement load balancing of the User Profile Service on two application servers within the same SharePoint 2010 farm, while providing failover in case one server goes down or needs to go offline, it’s a fairly easy thing to configure. Simply start the User Profile Service on a second application server in the farm. If the first application server goes down, or if you manually stop the User Profile Service on the first application server, the second application server will assume hosting operations for the User Profile Service. The User Profile Synchronization Service, however, can only be started on one application server within a farm at any given time. Therefore, in the case of a failover to the second application server, the User Profile Synchronization Service must be manually started on the second application server to continue profile synchronization. No biggie really.

"Excel Services is unable to process the request. Wait a few minutes and try performing this operation again."

On a SharePoint 2010 site, when attempting to open Excel files with a .xlsx or .xlsm extension, you receive the following error message:

You will also notice that legacy Excel files with a .xls extension open just fine.

Resolution

1. Ensure you have an Excel Services service application established.
2. Ensure that your web application is associated with the Excel Services service application. In Central Administration, go to Application Management, Configure service applications associations. Ensure Web Applications is selected from the View drop-down menu. Click on the appropriate Application Proxy Group (more than likely default), ensure the Excel Services service application is checked, and select OK.