Access Denied after performing database attach upgrade from SharePoint 2010 to SharePoint 2013

After performing a database-attach upgrade from SharePoint 2010 to SharePoint 2013, you may discover that your users are getting the Access Denied message.

If so, look no further than the default claims-based authentication functionality in SharePoint 2013. By default, claims-based authentication mode is used for web applications created in SharePoint 2013. In fact, you cannot create a new web application using classic-mode at all from within the SharePoint 2013 Central Administration site.

Microsoft recommends two primary methods of migrating from classic-mode to claims-based authentication in SharePoint 2013. See article below.

http://technet.microsoft.com/en-us/library/gg251985(v=office.15).aspx

1) Convert SharePoint 2010 classic-mode web applications to claims-based authentication in SharePoint 2010, and then migrate to SharePoint 2013.

2) Migrate SharePoint classic-mode web applications to SharePoint 2013 classic-mode web applications, and then convert the SharePoint 2013 classic-mode web applications to claims-based web applications.

I chose the second option because the first option just seemed too risky. Once finished, all users were able to successfully logon. Of course, it required me to delete the existing SharePoint 2013 claims-based web application and recreate it in classic-mode. To do this, you must create the web application using PowerShell.

New-SPWebApplication -Name "My Company Portal" -ApplicationPool "My Company Portal" -AuthenticationMethod "NTLM" -ApplicationPoolAccount (Get-SPManagedAccount "CONTOSO\jdoe") -Port 443 -URL "https://www.myportal.com"

PowerShell: Update a column value for a subset of documents in a document library using a CSV file

I recently had the need to update the column value for a subset of documents in a document library. I was given a CSV file with the Title of each file that needed the updated value. The column in the document library that needed updating was named DeleteFlag.

After some googling, I came across the following blog post that would serve as the basis for my solution. Once again, PowerShell to the rescue!

http://technology-23.blogspot.com/2012/08/update-sharepoint-library-from-csv.html#!/2012/08/update-sharepoint-library-from-csv.html

Here are the steps to my solution.

1) Add another column in the CSV file to match the column you want to update in the SharePoint library. As indicated above, the column name for me was DeleteFlag. Be sure to also add the value(s) as needed for the new column. In my case, I had a single value for all items. Copy the file to the SharePoint sever where you will run the PowerShell script.

2) Create a new .ps1 file (using Notepad) and copy/paste the following.
$url = "http://site/subsite" function Update-SPList() { $data = Import-Csv "D:\csvfilename.csv" $OpenWeb = Get-SPWeb $url $list = $OpenWeb.Lists["Library or List Name"] foreach($row in $data) { $spQuery = New-Object Microsoft.SharePoint.SPQuery $spQuery.ViewAttributes = "Scope='Recursive'"; $camlQuery = '<where><eq><fieldref Name="Title" /><value Type="Text">'+ $row.Title +'</Value></Eq></Where>' Write-Host $camlQuery $spQuery.Query = $camlQuery $listItems = $list.GetItems($spQuery) foreach($item in $listItems) { Write-Host "Updating" $item["DeleteFlag"] "with" $row.DeleteFlag Write-Host "Updating DeleteFlag" $item["DeleteFlag"] = $row.DeleteFlag $item.Update() } } if($OpenWeb) { $OpenWeb.Dispose() } } Update-SPList
Change the $url, $data, and $list variables to reflect your environment. As you can see, the script matches the Title in the CSV file with the Title in the document library, and then updates the DeleteFlag column for each item in the document library with the value in the DeleteFlag column for the matching items in the CSV file.

3) Copy the .ps1 file to the same SharePoint server where you saved the CSV file.

4) Run the .ps1 file and site back and watch the magic happen!

SharePoint Designer: Conditional Formatting using a Sum Calculation

Just a quick tip I wanted to pass along --

In SharePoint Designer, if you have a need to apply conditional formatting to cells in a List column based on the value of a sum calculation in another cell, follow these steps:

1.  In Design view, click the td.ms-vb tag to select the entire cell that needs conditional formatting, select Conditional Formatting in the ribbon, and choose the Format Selection option. In my example below, the cell that I selected is in the Cost Score column.

2.  Click the Advanced button in the Condition Criteria dialog box.

3. For the XPath expression, ensure you include the ../Row in the sum calculation. In my example below, I am applying conditional formatting to each cell if its Cost Score value is less than the average of all cells in the Cost Score column.

@Cost_x0020_Score < sum(../Row/@Cost_x0020_Score) div count(../Row/@Cost_x0020_Score)

4. Select OK and Set Style as you desire.

Hope this helps!

Automating User Profile Photo Import in SharePoint 2013

To automate the User Profile Photo Import process in SharePoint 2013, please use the following sequence.

Prerequisites: User photos must be added to Active Directory and the thumbnailPhoto attribute must be mapped to the Picture property in the User Profile Service Application.

1) User Profile Incremental Synchronization. In the User Profile Service Application, select Configure Synchronization Timer Job. Define a recurring schedule. I scheduled mine to run Daily at 10:00am.

2) Create and schedule a PowerShell script for running Update-SPProfilePhotoStore.

- On one of the application servers in the farm, open Notepad. Copy and paste the following and save it to a desired location as PhotoImport.ps1.

Update-SPProfilePhotoStore -CreateThumbnailsForImportedPhotos 1 -MySiteHostLocation http://mysiteurl

- Open Task Scheduler on the server. Select to Create Task.

General tab:

Triggers tab: (NOTE: Ensure the time is scheduled after the User Profile Incremental Synchronization schedule above, and ensure you schedule enough time for the User Profile Incremental Synchronization task to complete. 30 minutes should be sufficient.)

Actions tab:

Program/script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Add arguments (optional): -psconsolefile "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\CONFIG\POWERSHELL\Registration\psconsole.psc1" -command "D:\Scripts\PhotoImport.ps1" 

- Leave the defaults for the Conditions, Settings, and History tabs. Select OK and type in the logged on user password when prompted.

3)  Incremental Crawl of People Content Source. In the Search Service Application, Edit the Content Source that contains the people address (sps3://mysiteurl). Create an Incremental Crawl schedule as needed. Ensure the time is scheduled after the scheduled task in step 2 above.

Each time the automated 3 step process above is run, it will update the photos in each user’s profile page and in the People search results.

Short URLs for SharePoint using HTTP Redirect in IIS 7

A quick and easy way to create a short URL for a SharePoint location is to use the HTTP Redirect module in IIS 7.

1) Open IIS Manager.

2) Expand the Site where you want to add the short URL. You will see the virtual directories.

3) Right-click on the Site and select to Add Virtual Directory. Type in an Alias to be used as the URL suffix. For example, if your root Site URL is http://teamsites and you use an Alias of "forms", the short URL will be http://teamsites/forms. For the physical path, I recommend browsing to the C:\inetpub\wwwroot\wss\VirtualDirectories directory and creating a new folder there with the same name as the Alias. DO NOT create the new folder under the same directory as the root Site (e.g. C:\inetpub\wwwroot\wss\VirtualDirectories\teamsites). This will cause the root site to be redirected as well. Select OK to create the new virtual directory.

4) Select the new virtual directory under the Site. In the Features View, double-click the HTTP Redirect icon.

5) Check to Redirect requests to this destination, and enter the long URL. For the Redirect Behavior section, only select to Redirect all requests to exact destination (instead of relative to destination). Leave the Status code as is and click Apply in the Actions pane on the right.

Perform all steps on every WFE in your farm.

When done, upon browsing to the short URL you will be automatically redirected to the long URL.

Project Server 2010: Prompted for SQL Server Login upon editing Business Intelligence Center reports in Excel client application

While working on a PWA site, you attempt to edit reports in the Business Intelligence Center using the Excel client application and receive a SQL Server Login dialog box, as shown below.

To resolve this issue, ensure the Active Directory user or group has the db_datareader role on the PWA reporting database. When editing reports, the Excel client application connects directly to the PWA reporting database using the credentials of whoever launches it, so they must have the correct permissions in SQL.

Also, keep in mind that the Excel Web App uses the Secure Store Service for opening and refreshing reports in the Business Intelligence Center, not the logged on user credentials as in the Excel client application. To see who can open and refresh reports in the Excel Web App, go to Central Administration -> Manage service applications -> Secure Store Service. Click on the Target Application ID, click Next twice, and see the Members. Add the Active Directory users or group as needed.

Create link to open an InfoPath form in client application

For those ever needing to create a link to open an InfoPath form in the client application (not the web browser), use the following:

<a href="Javascript:createNewDocumentWithProgID(escapeProperlyCore('http://site/library/forms/template.xsn', true), 'http://site/library', 'SharePoint.OpenXMLDocuments', true)">Image or Text</a>

Of course, you need to modify the site and library to fit your environment.

InfoPath 2010: Calculate AVG and COUNT on multiple fields

A couple more calculations for InfoPath 2010:

To get the average of multiple Number fields, including a repeating table Number field, use the following:

avg(Field1 | Field2 | Field3 | Field4 | Field5 | RepeatingField1)

Likewise, to get the total count of multiple fields that contain data, including a repeating table field, use the following:

count(Field1 | Field2 | Field3 | Field4 | Field5 | RepeatingField1[. != ""])

More calculations will be posted as I deem necessary.