Reporting Services Upgrade from SSRS 2008 to SSRS 2012 Lessons Learned

Let me preface this article. Sperry Hope is my SharePoint + BI engineer. He’s awesome. He pulled off some amazing feats in our Reporting Services upgrade this last week. I asked him to summarize his lessons learned. He’s included the blogs he used as resources as well as videos and downloads.

High level Details

  1. SQL 2012 SSRS requires SP1+ or W2K R2 or Above (The error was quite descriptive here.)
  2. Cannot communicate with web service: It was the new port communication requirements (blocked by firewall between SharePoint and SQL SSIS servers.)
  3. Cannot convert Claims to Windows Token: Special Rights required including local policies as noted below

 

Here’s an overview of the lessons from Sperry in his own words…

"I wanted to get this out before the end of day today to document what happened during the SSRS Upgrade to 2012. We were finally able to get the SSRS install working in a multi-server farm environment with Kerberos authentication. 

Here are a few things we ran into and how we managed to get around them.

Installing the SSRS SharePoint integrated bits on the SQL 2012 Server worked great.  After running the PowerShell commands install-SPRSService
and install-SPRSServiceProxy
and starting the service with get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance everything looked fine.  I then went to add the SSRS Service Application to SharePoint via Central Admin and it didn’t show up in the list of service applications available to install.  I figured we would need to install the SSRS SharePoint bits on every server in the farm so I hooked up the SQL CD to the WFE server and went to run the install.  ERROR – Install requires SP1 on Windows 2008R2 or above to run.  This is my developer’s primary box and I didn’t want to run windows updates on him while he was on vacation so I started digging and found I could download the rsSharePoint.msi directly and install without the full SQL install running.  This worked like a charm and the SQL Report Server Service Application was now available to install on the farm. I figured since the Service App was only going to be running officially on the SQL server that met all OS level patches then I would probably be OK. 

 

When we went to install the Service Application we wanted to try and upgrade our 2008R2 SSRS databases so we pointed to them during the New Service Application settings.  It found the databases and told us they would be upgraded so we thought we were good to go.  After a couple of minutes we got an error saying the upgrade on the ReportServerTempDB failed.  We decided to point to a new database instead of the existing database and the Service Application finally installed.  Knowing that the report files were safe in SharePoint we knew we would lose some history and subscription information but felt like that would be OK to get us up and running.

 

We loaded up a test report and Data Source and got an error. Cannot communicate with web service at http://ssrsserver:32843….  That is the SharePoint Web Service Port which ssrs is now running as a service application so I checked to make sure the IIS pieces were running and then hit the full URL of the service app on port 32843 from the SSRS Server and it was working fine but from my front end server it was not.  Turns out the SSRS Server was in our Database network zone and the front end was in our Application network zone.  Firewall request in and applied and now we are talking to the web service for the Service Application. 

 

Went to test the Data Source link and got the error Cannot convert claims identity to windows token.  Started digging around on the web and found numerous articles and blog posts about making sure the Claims to Windows Token Service (C2WTS) is set up and running in SharePoint.  We had set this up for performance point in our production environment but not in our test environment.  Got out the notes and set up a new service account in test to run the service.  Made sure it was given the correct rights on the SSRS server of act like a part of the OS and able to impersonate another identity.  Set up the delegation to the SQL server we were trying to hit and started the service.  Still got the error Cannot convert claims identity to windows token.  Went through this video and tried to verify we had everything set up correctly.  Got Microsoft Support on the case and they started looking at the issues as well.   I finally found this blog and started focusing on the bz71 error which I found.  Tying the bz71 error to the Process ID I noticed that the User Account running the process was the SSRS Service Account.  I thought the process of converting a claims identity to a windows token would be handled by the user running the C2WTS and the account associated with that service.  It appeared that the process doing that conversion or at least the process throwing the error is actually the service account running the SSRS Service Application.  I then set the permissions for the SSRS Service account in the local policy on the SSRS Server to be the same as the C2WTS Account.  I restarted the Service Applications via central admin and things started working. 

 

Summary: 

  1. rsSharePoint.msi needs to be run on every machine in the farm even if they won’t be running the SSRS Service App.
  2. SSRS Service Application now runs in IIS as a SharePoint Service on port 32843 not port 80 or 443 like in 2008.
  3. SSRS Service Account needs to be granted the same rights on the SSRS Server as a typical Claims to Windows Token Service Account.  (Local Administrator, Act as part of the Operating System, Impersonate a client after authentication, Run as a Service, member of WSS_WPG)
  4. You can use separate accounts for SSRS and C2WTS as long as each have the above rights.
  5. Restarting application pools with IIS manager doesn’t always pick up new settings.  Make sure you stop/start things via Central Admin."

 

Sperry Hope

The #SPC12 BUZZ has begun… and it’s all about SharePoint 15!

"I’ve registered for the SharePoint Conference 2012, November 12-15 in Las Vegas and it’s all about SharePoint 15! http://www.sharepointconference.com #SPC12"

It’s great to see Microsoft has embraced the buzz and momentum, they’ve already announced some of the parties at the conference. If you’ve been waiting to see who will be be at SPC, you don’t have to wait to see. Twitter is lighting up with a who’s who of SharePoint… simply watch the #SPC12 hashtag. That early bird price isn’t going to last. It is not date based, it’s based on # of registrations… As well if you’re a vendor the window for getting a booth is closing. There are only few left as of writing this.

I was excited to see the registration login info integrated with Facebook. That will be nice for return logins and managing registration and signing up for sessions, etc…

Cancellation and Rename Details

I was interested to see that SharePoint Conference supports your ability for a full refund minus a $50 processing fee

– You have till July 1st to cancel and get full refund

– After July first to August 26 you can get 50% refund

– Name changes are allowed till Sep 23 (That’s cool, so if you want to send one ops guy but he may be someone different by then… no worries, lock in the price.)

Breakfast and Lunch are provided by the conference every day, and hint hint… I’ll be filling you in on where the parties are and many of those will be having food and finger foods. You can be sure there will be many a #SharePint and #ShareFood

EVENING EVENTS

 

One thing you can tune into this blog for is evening events. I’m going to keep track of all of the parties from both a Conference perspective, and Vendor perspective. Especially those that require a wristband.

 

 

You indicated your interest and participation in the evening events as follows:

Welcome Reception – Sunday, November 11 from 6:00pm – 9:00pm:

Club SPC – Monday, November 12 from 6:00pm – 8:00pm:

Evening Event – Tuesday, November 13 from 7:00pm – 11:00pm:

Enable Multilingual SharePoint 2010 UI Without A Drop of Code

It’s amazing to me how much better multi lingual support has gotten in 2010, and I think many don’t even realize just how easy it is to setup to support. When language packs first came out, we worried we’d need to have support people for each language pack we rolled out. This is NOT the case today. With the ability to have users simply pick their preferred display language someone supporting someone’s site could simply change the language display to their own preference. A site simply displays by default in its language and supports alternates with a simple click of a checkbox after the language packs have been installed. I was amazed how easy it was to setup.

How to Enable Multilingual SharePoint Site UI in SharePoint Without a Drop of Code, and Simply Without Variations:

  1. Determine the languages you want to support. I did some research on TOP 10 Languages of your Intranet; feel free to take advantage of my research or follow the steps to do your own. You could even support the 6 official languages of the UN.
  2. Download and install the Language packs you chose in step 1. Choose the language from the drop down on change language and choose download. You do not need to download the language for the product you’ve installed. English servers don’t need English language pack installed. These packs apply to SharePoint Server. If you are using SharePoint Foundation you can download those language packs. "Language packs enable creation of sites and site collections in multiple languages without requiring separate installations of SharePoint Server 2010, Project Server 2010, Search Server 2010, and Office Web Apps 2010. Multiple language packs can be installed on the same server. Application of a Language Pack will not change the language of the installed Microsoft server product, or the language of the administrative features." Reference: Deploy Language Packs

 

Recommendation: You should be committed to supporting these not just this version, but going forward. Since people will be installing a site in Spanish, the site is still Spanish even though you can view it in English if the relevant language pack is installed… You cannot change an existing site, site collection, or Web page from one language to another by applying different language-specific site templates. After you use a language-specific site template for a site or a site collection, the site or site collection will always display content in the language of the original site template. Removing a language pack that has sites that were based on it will result in a 500 error. For this reason I do NOT recommend installing all of the language packs. Understand your user base then install those most common languages.

  1. After the Languages have been installed. Go into the site settings, site actions, and in the section on site administration click Language settings.

  1. From here you’ll see what site the language of the site was created in plus additional alternate languages the site should support. Simply check the boxes. This allows users navigating to the site to be able to change the display language of the site to any of the checked languages! As well you can overwrite the translations if you want to force the title in a specific language if desired.

Congratulations. Your site is now multi lingual.

  1. Go back to your site collection, click on your name in the top right corner and select a display language

  2. View your site and voila! You’ve got much of the out of the box stuff translated, but many of your own labels are not translated, much of your nav, menus for third party products, some libraries, columns, and so on. SharePoint does have a mechanism for supporting resource files that provide the translation mapping. I recommend referring to the MSDN article on localizing the User Interface. This translation is not perfect as you can see in the screenshot below. All the images, and much of the custom labels need work.

 

So that’s what you get out of the box for enabling it on one site… Now you’re saying… I’ve got thousands of sites, can I add it to all of them? Yes.

A clever guy in the community setup a powershell script to support Multilingual UI to step through all of the sites in a web app or farm to enable them for alternate languages (UICulture)

 

 

If you’re a dev: "You can use the SharePoint Foundation object model to do the same thing by getting a reference to a SPWeb object, setting its IsMultilingual property to true, and then calling the AddSupportedUICulture method to add support for one or more alternate languages"

Understanding the Multilingual User Interface (MUI)

Walkthrough: Localizing a Web Part

Walkthrough: Localizing Columns, Content

 

Third Party Enhancements

 

This post is sponsored by IceFire Studios, creators of PointFire a third party component which provides additional multilingual capabilities and attempts to simplify MUI development and reduce the need for Variations. Here is some information from them on their product.

  • "With PointFire, those simple instructions are simpler still – the alternate languages you want are always there by default, and users can change languages with or without a personal menu
  • The SharePoint MUI translates about 90% of the UI, but PointFire covers 100%, including templates that don’t support MUI, all out-of-the-box content, and web parts.
  • PointFire ensures user always see content in their language, letting you set the language of items, documents, web parts, and pages. The page URL is the same for all language versions.
  • PointFire manages all multilingual content without needing to use Variations"

You can download a free trial of PointFire for SharePoint Multilingual including enhanced Language Packs from their site.

 

 

SharePoint 2010 File Name, Length, Size and Invalid Character Restrictions and Recommendations

While some things haven’t changed much, I felt it was important to revisit these classic file and folder restrictions. Today I was looking at my blog post from 2008 on file names, lengths, size and so on and noticed it was the most popular post on my old blog. I haven’t revisited that topic in a long time. I’ve updated it here, and added some additional recommendations.

Site, Folder and File name restrictions

KB Article 905231 I’ve added some additional symbols and recommendations.

  • Site Names
    • Not Allowed: | # { } % & <TAB>" ~ + / : * ? " < >
    • Avoid starting sites with an underscore (_) or with the period character.
    • Site names can cause confusion and corruption if they have periods, apostrophes or commas
    • They should not have consecutive periods or end with a period.
      • You cannot use the period character at the end of a site name
    • Many other symbols are not recommended such as $^()-_=+[]`! (other international currency symbols and international symbols should be avoided in site names, but some are more acceptable in file names. Ascii is preferred when possible.
  • Folder Names and File Names
    • Do not use: " # % & * : < > ? / { | } ~
    • File names cannot be longer than 128 characters
    • Do not use the period character consecutively in the middle of a file name.  For example, "file..name.docx" is invalid.
    • You cannot use the period character at the end of a file name
    • You cannot start a file name with the period character
    • Many other symbols are not recommended such as $^()-_=+[]`! (other international currency symbols and international symbols should be avoided in site names, but some are more acceptable in file names. Ascii is preferred when possible.
  • In addition, file names and folder names may not end with: (Many of these are international symbols)
    • .files, _files , -Dateien , _fichiers , _bestanden , _file ,_archivos ,-filer,_tiedostot ,_pliki ,_soubory ,_elemei ,

                   _arquivos ,_dosyalar ,_datoteke ,_fitxers,_failid 

                  ,_fails ,_bylos ,_fajlovi,_fitxategiak

Examples of Legal File Names

  • AllItems.docx
  • Dept_1234.doc
  • Long.Name.With.Dots.txt

Examples of Illegal Site Names

  • Intranet/sites/People & Process
  • Intranet/sites/My Math is 10% Here
  • Intranet/sites/_foobar
  • Intranet/sites/#FAIL
  • Intranet/sites/Jimmy+Bean

 

Examples of illegal file names:

  • HailCæsar.wav.
  • Wow…BadStuff.txt
  • 揵.htm
  • Roger&Hamerstein.mp3
  • WhatsUp?.doc

Note that word may let you save this, but uploading it to SharePoint will be your challenge. Sometimes you’re even allowed to upload it through the explorer view, and it will block you from the web interface.

 

File and Folder name lengths

  • Link list items are restricted to 256 characters and will truncate links to SharePoint documents (or anything else) with lengths longer than this.
  • When storing files the structure and files (entire path including sites, folders, and file name) cannot add up to more than 260 characters or they will see an error message or form validation error with the explanation around the URL length. 

 

When using multi file upload interface: Make sure the total size of all your files is not greater than the upload limit set for your web application. 

 

File Size Maximums

Max File Size Default: 50MB Maximum setting: 2GB (per file)

List data: Max of 8kb of data

100 MB is what I recommend for most typical environments as a great compromise, but don’t recommend more than 500MB for most environments without External or Remote Blob storage.

(If you set it to 0 or 2047 MB, it will be the same as 2GB file.) Realistically a few hundred MB is really all you can get, unless you have fast LAN access. Even with LAN access there are cases where upload can fail… Out of memory errors in SQL, Out of memory on web front end, and various places where timeouts may occur. Server admins see the appendix.

NOTE: I understand that even if you use RBS or Remote Blob Storage and most third parties, you still can’t support files over 2GB, but you’ll have to check with your vendor.

 

Note:

I have heard of people who have had the issue of URLScan applied to the web server. It has a default limit of 30mb set in its .ini file. Once that file size is hit it was just dropping the transfer

 

 

As well there are other things that you can find that will block your files:

Each Web Application contains a list of blocked file types that is based on file name extensions. For example, you can block files that have the .exe extension. By default, many file types are blocked, including file types that are treated as executable by Internet Explorer. Files, whose names include braces, (for example, filename.{doc}) are also blocked by default.

 

Blocked File Type Reference (The Outlook 2010 blocked file resource file list is a great reference, and for understanding the files is actually even better than the default SharePoint Blocked File List)

Here are a few file types I typically remove from the blocked file list below: URL, LNK, CHM, HLP

I often add .MP3, PST, OST, and sometimes MP4. Some will add .ZIP, but I recommend allowing the .zip, arj, rar, etc… file as a common mechanism for transporting larger files.

 

File extension

File type

.ade

Microsoft Access project extension

.adp

Microsoft Access project

.app

Application file

.asp

Active server page

.bas

Microsoft Visual Basic class module

.bat

Batch file

.cer

Internet Security Certificate file

.chm

Compiled HTML Help file

.cmd

DOS command file, Microsoft Windows NT command script

.cnt

Windows Help contents file

.com

Microsoft MS_DOS program

.cpl

Control Panel extension

.crt

Security certificate

.csh

Script file

.der

DER Encoded X509 certificate file

.exe

Executable file

.fxp

Microsoft Visual FoxPro compiled program

.gadget

Windows Gadget

.grp

Windows program group file

.hlp

Help file

.hpj

Help project file

.hta

HTML program

.inf

Information or setup file

.ins

Internet Naming Service

.isp

Internet Communication settings

.its

Internet Document Set file

.jar

JAVA archive file

.js

JavaScript source code

.jse

JScript Encoded script file

.ksh

UNIX Shell script file

.lnk

Windows shortcut

.mad

Microsoft Access module shortcut

.maf

Microsoft Access shortcut

.mag

Microsoft Access diagram shortcut

.mam

Microsoft Access macro shortcut

.maq

Microsoft Access query shortcut

.mar

Microsoft Access report shortcut

.mas

Microsoft Access stored procedures

.mat

Microsoft Access table shortcut

.mau

Media attachment unit

.mav

Microsoft Access view shortcut

.maw

Microsoft Access data access page

.mcf

Manifest configuration file

.mda

Microsoft Access add-in program

.mdb

Microsoft Access program

.mde

Microsoft Access MDE database file

.mdt

Microsoft Access data file

.mdw

Microsoft Access workgroup

.mdz

Microsoft Access wizard program

.msc

Microsoft Common Console document

.msh

Windows PowerShell script helper

.msh1

Windows PowerShell script helper

.msh2

Windows PowerShell script helper

.mshxml

Windows PowerShell script helper

.msh1xml

Windows PowerShell script helper

.msh2xml

Windows PowerShell script helper

.msi

Windows Installer package

.msp

Windows Installer update package file

.mst

Visual Test source file

.ops

Microsoft Office profile settings file

.osd

Open software description file

.pcd

Microsoft Visual Test compiled script

.pif

Windows program information file

.pl

Perl script file

.plg

Developer Studio build log

.prf

Outlook profile file

.prg

Program source file

.ps1

Windows PowerShell

.ps1xml

Windows PowerShell

.ps2

Windows PowerShell

.ps2xml

Windows PowerShell

.psc1

Windows PowerShell

.psc2

Windows PowerShell

.pst

Microsoft Outlook personal folder file

.reg

Registration entries, Registry data file

.scf

Windows Explorer command file

.scr

Windows screen saver

.sct

Windows script component

.shb

Windows shortcut to a document

.shs

Shell Scrap object file

.tmp

Temporary file or folder

.url

Uniform Resource Locator (Internet shortcut)

.vb

Microsoft Visual Basic Scripting Edition (VBScript) file

.vbe

VBScript Encoded script file

.vbp

Visual Basic project file

.vbs

VBScript script file, Visual Basic for Applications script file

.vsmacros

Microsoft Visual Studio .NET binary-based macro project file

.vsw

Microsoft Visio workspace file

.ws

Windows script file

.wsc

Windows script component

.wsf

Windows script file

.wsh

Windows Script Host settings file

.xbap

XAML browser application

.xnk

Microsoft Exchange public folder shortcut

Appendix:

To set the upload size to 200 megabytes, use the following syntax:

stsadm -o setproperty -pn max-file-post-size -pv 200

To view the current setting of the maximum file post size property, use the following syntax:

stsadm -o getproperty -pn max-file-post-size –url http://server_name

 

For Admins you’ll need to make sure you increase your timeouts and settings:

IIS Connection Timeouts

  • Tune the Microsoft Internet Information Services (IIS) connection timeout setting.

The default timeout for connections in IIS is 120 seconds (2 minutes). Depending on your maximum file size and how long it takes for the file to be uploaded, you may not need to change this setting. If, however, IIS is timing out when you upload large files, you can change this property to ensure that larger files can be uploaded successfully.

  1.  
    1. Click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
    2. Right-click the virtual server you want to configure, and then click Properties.
    3. Click the Web Site tab.
    4. In the Connections section, in the Connection timeout box, type the number of seconds you want IIS to wait before timing out.
    5. Click OK.

 

For Admins you can modify:

  • Central Admin – Security – Define blocked file types – Web Application menu, click Change Web Application. Add blocked file extensions you want to block or remove those you want to remove.

 There are some settings for increasing your maximum timeout values including

Web.Config

ExecutionTimeout value to web config in the layouts folder and the virtualDirectoryFolder:

 

layouts dir

Existing code

 <location path="upload.aspx">

    <system.web>

      <httpRuntime maxRequestLength="2097151" />

    </system.web>

  </location>

   

Replacement code

 <location path="upload.aspx">

    <system.web>

      <httpRuntime executionTimeout="999999" maxRequestLength="2097151" />

    </system.web>

  </location>

   

virtualDirectoryFolder dir

Existing line

<httpRuntime maxRequestLength="51200" />

Replacement line

<httpRuntime executionTimeout="999999" maxRequestLength="51200" />

   

   

Default chunk size for large files:

Sharepoint default chunk size is set to 5MB, this means that if a client tries to open a document of 50MB and the chunk size is 5MB, the document is divided and retrieved in 10 chunks. Each chunk will be loaded into the memory of both the WFE handling the request and the SQL Server. Note I looked for a powershell setting and couldn’t find a reference in the 10 minutes I looked for you J By the way… don’t use smiley’s in your file names either J

   

stsadm -o setproperty -pn large-file-chunk-size -pv 1073741824

(Note: Requires IISReset) 

   

   

 

 

 


 

10 Requirements for SharePoint Mobile

While Windows Phone may come with a nice little app for working with SharePoint, the reality is most other devices don’t have that luxury, and actually not sure you’d hear that the Windows Phone experience is "awesome" with SharePoint either. In this post I want to dissect the SharePoint Mobile requirements and what you should understand when it comes to navigating your requirements. Often it’s important that you first determine what you really care about, so you can pick the correct set of requirements. Different third party apps focus on different functionality.

Looking at Common SharePoint Mobile Requirements — What is important to you?

  1. Sync Files and Folders – out of the box there is no way to sync the files from SharePoint to your iPhone or iPad. You need an app for that.
  2. Edit and Save – While you may be able to download a file and find a viewer on your device, most will have an impossible time getting that file back up to SharePoint.
  3. Upload – Uploading a document to SharePoint simply does not work on most mobile devices through the browser interface. The mobile browser and the upload functionality don’t recognize the file system and doesn’t support it as a result. Some of the interfaces are ActiveX and simply don’t work anyway.
  4. Forms and Simple Web Apps – A lot of people who need to work with SharePoint from a mobile device simply need to fill out specific forms, or take specific actions. You’ll find some third parties focus on building very simple interfaces with big buttons for making this an easy to use solution. They focus on getting in and getting out.
  5. Rich UI & Web parts – If you only focus on documents you’re missing the boat. There’s a lot of organization in the SharePoint UI. But also think about it…. All those configured SharePoint Web Parts that display membership, recent or related documents or links, the dashboards are completely in the UI. Don’t forget all the work you’ve put into custom Dataviews.
  6. Social Data & Profiles – I’ve seen some applications that focus on making sure you’ve got your contact data on the go. Searching for people including skills, phone numbers, and more are really important on the go. Wouldn’t it be great to save a profile as a contact?
  7. Search – If all you’ve got is a list of files, what are you missing? Is search important? You should be thinking about that slick ability that SharePoint has in being able to search. In out of the box SharePoint for Mobile you’re losing all sorts of things from the UI including the all-important search box.
  8. Rich Interfaces & Views – Each third party has their way of addressing the needs of the UI. I find that the difference between the tasks, calendars, document navigation, setting up my sync or saving my favorites for quick access is a key differentiator.
  9. Use SharePoint, Maybe I can get away with a simple built a Mobile Masterpage – I’ve been waiting for the ultimate mobile masterpage. A masterpage designed for many mobile devices and also viewable on the desktop. I saw a vendor at Denver SharePoint Saturday that had built a masterpage that was designed for the iPad. It was pretty slick. I’ve thought about designing a very simple UI for SharePoint, but haven’t gotten to it. It’s something to consider in your strategy for sure. It still doesn’t address the upload scenario, but maybe with the right control it would. Check out Kyle Schaffer’s HTML5 Responsive Masterpage.
  10. I know you don’t like the mobile interface – Kill the SharePoint Mobile Interface – So in effect there is really no helpful built-in mobile support at all; just the ability to view lists with a mobile device.

 

While this post is written with many products and requirements in mind…

This post sponsored by Infragistics SharePlus, a SharePoint mobile application for iPad, iPhone and Android. Get your free lite copy in the App Store or from http://www.shareplusapp.com. The lite version includes connecting to SharePoint, navigating sites and lists, reading documents, and sorting and grouping items. The Pro version includes content management, document management, automatic synchronization with offline support, media recording, security, Wifi sharing, and local file management. Enterprise management is also available by contacting Infragistics. Check out the links below for your mobile of choice: