Using the SharePoint 2013 REST user resources API in InfoPath 2013

I recently did a DB attach migration from SharePoint 2007 to 2010 to 2013 for a customer, which requires many steps to account for all the changes in SP along the way. One of the changes that I chose to make was to replace some of the older SOAP web services calls, like GetUserProfileByName, in InfoPath forms converted from 2007 to 2013. I started with the approach outlined in John Liu’s post here. John’s post takes care of the on-the-fly changes often required to REST URLs to use them for a specific query without resorting to a whole set of Data Connections for each required query. The other necessary information is in the SharePoint REST API spec, specifically here for the User resource specifications.

Essentially the URL for this query takes the form of

http://<web app URL>/_api/siteusers(@v)?@v='i%3A0%23.w%7Cdomain\userid

For example, for an on-premise 2013 installation, the URL might look something like this:'i%3A0%23.w%7Cmicrosoft\rramirez'

Invoking this query will result in a ATOM-XML feed returning that can be used in InfoPath to select particular fields of interest. Below is an example of such an XML document.

 <?xml version="1.0" encoding="utf-8" ?> 
 <entry xml:base="" xmlns="" xmlns:d="" xmlns:m="" xmlns:georss="" xmlns:gml="">
 <category term="SP.User" scheme="" /> 
 <link rel="edit" href="Web/GetUserById(1209)" /> 
 <link rel="" type="application/atom+xml;type=feed" title="Groups" href="Web/GetUserById(1209)/Groups" /> 
 <title /> 
 <name /> 
 <content type="application/xml">
 <d:Id m:type="Edm.Int32">1209</d:Id> 
 <d:IsHiddenInUI m:type="Edm.Boolean">false</d:IsHiddenInUI> 
 <d:Title>Russ Ramirez</d:Title> 
 <d:PrincipalType m:type="Edm.Int32">1</d:PrincipalType> 
 <d:Email /> 
 <d:IsSiteAdmin m:type="Edm.Boolean">true</d:IsSiteAdmin> 
 <d:UserId m:type="SP.UserIdInfo">

The data of interest in one particular case was the value within the <Title> element; this is equivalent to the PreferredName field returned using the GetUserProfileByName() web method. Within InfoPath then, you just need to do the following:

  • Define a Data Connection to a simple REST URL like
  • Create a Form Load rule that will
    1. do a Change REST URL Action
    2. do a Query of the REST data connection (it will use the new URL just modified)
    3. set the value of the field of interest; e.g. a Prepared By field

The XPath for the Change REST URL action of step 1. will look like this for our example:

concat("'i%3A0%23.w%7Cmicrosoft\", my:UserId, "'")

The escaped characters in the variable @v are i:0#.w| which you should recognize, and the my:UserId is a field in InfoPath initialized to substring-after(xdUser:get-UserName(), “\”) so the user id part (rramirez in this example) is stored for use by the modified REST URL. If you try this URL from a browser (turn off Feed view in IE), you will get the XML document above with your pertinent information. Step 3. above, setting the Prepared By field, uses the value of the Title element as I mentioned. Here’s what the REST service schema looks like in InfoPath 2013: RESTschema Once you have done all of this, you should see your user’s preferred name/title looking like this: prepared by field

Posted in GetUserProfileByName, InfoPath 2013, REST, SharePoint 2013 | Leave a comment

Databases in compatibility mode (Business Data Catalog DB)

The prescribed cure for the BDC database not upgraded after the application of a CU is to run the Powershell cmdlet below. However, I have found that this does not always permit the operation to run and errors as shown in red.

PS C:\Users\spsetup> (Get-SPDatabase | ?{$_.type -eq “Microsoft.SharePoint.Busin

Exception calling “Provision” with “0” argument(s): “Cannot open database
“SP2013_BusinessDataCatalog” requested by the login. The login failed.
Login failed for user ‘DOMAIN\spSetup’.”
At line:1 char:1
+ (Get-SPDatabase | ?{$_.type -eq
“Microsoft.SharePoint.BusinessData.SharedService …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

The trick to getting past this error is to STOP the BDC service first, run the cmdlet, then START the BDC service back up. Obviously this stop/start can also be done in PS on either side of the snippet above.

Posted in Uncategorized | Tagged | Leave a comment

Loading .mobi files into Kindle (device or app) easily

I buy a fair amount of reference books I like to have with me. Several publishers offer .mobi file format, but you have to take an indirect route to get the book into your Kindle library.

A really simple way to do this on a device is to use Dropbox, which you probably already have. Upload the file, Let’s assume from a PC, then on your device, navigate to your Upload folder in Dropbox, or wherever you uploaded to, and then select ‘Export’. From here your device will list the possible destinations, and Kindle will be a choice if you have the Kindle reader app installed on your device.

In Android devices, the Kindle sideloader is already there with the Kindle reader and Dropbox has provided a direct path to export your mobi, PDF, etc files to Kindle. That’s right, you also get to choose the device you want the exported book ‘pushed’ to, so after a few minutes in most cases, the exported books will appear on your Kindle app carousel.

Posted in Miscellaneous | Tagged , , | Leave a comment

What is the SharePoint best practice?

There’s no one contextual framework that the phrase “best practice” fits neatly into because the word best is ambiguous.

I get asked this question all the time; in nearly every sales meeting I have with customers.

It is like some many other similar questions dealing with systems performance or availability for example because your first response often necessitates asking several questions yourself to understand what’s important to the client.

For example, is a workflow solution or an event receiver or a timer job definition the best practice for a given set of requirements? Your answer will depend upon what the client thinks best really means; i.e. which combination of cost, schedule, no-code, etc. is important to them.

However, in the consulting field, it frequently is not as simple as asking what is important to them if they expect you to tell them what’s important. I have found that getting in front of this question helps whenever there is an approach that you think might have a slight edge for that situation.

You can certainly assume that minimizing cost and risk are important to them. But there are plenty of cases for example where the custom coded solution is clearly better than the OOTB solution and you are going to go with that approach first; even when it’s an up hill battle.

If time permits, and I sense the client wants the “tell me like it is” approach, then I explain some of this to them and in all of those cases they immediately get why can be a hard question to answer off-the-cuff. Unfortunately that does not mean someone will not have declared very specific best practices beforehand, but you can demonstrate your depth of knowledge and experience by explaining why that may or may not be right for them.

Posted in Miscellaneous, SharePoint 2010, SharePoint 2013 | Leave a comment

Active Directory Import (ADI) in SharePoint 2013

Many of us are glad to see the return of the AD direct import function for creating and maintaining user profiles in SharePoint 2013. In my own experience, it enabled a quick and clean setup of profile sync that currently has the pleasant side-effect of allowing one to apply the recent March (KB 2767999) and April (KB 2726992) CUs without FIM issues.

Using Spencer Harbar’s great blog, and doing some digging, I discovered that the FIM bits in SP 2013 RTW were once again (remember UPS in 2010) not real current. Indeed, I found that is was not easy or possible maybe to get the User Profile Sync windows service running once again after the application of the March/April CUs.

Hum…where have I seen that before…

Suffice it to say that the 2013 farm installation I was using was hosted on Server 2012 rather than 2008 R2, and I am now quite certain this was part of the root cause of the issue.

Except for the few cases where FIM might actually be required, this is a moot point for anyone who can use ADI to create and update profiles. If you need FIM to work correctly, wait a while and it may happen. However, presenters at TechEd said that FIM was probably going to be deprecated from SharePoint at some point.

Back to ADI itself, a nice addition to the profile sync timer job executing is that ULS entries are created as a trace of what transpired during the operation; these entries are prefixed with this text below.


Use can just search the ULS log file for this string and find all the entries – nice! In addition, if you look at the Usage Log settings in Central Administration, you should see that an entry for User Profile ActiveDirectory Import Usage has been checked for inclusion in the Usage logs. Unfortunately, this cannot be reported via Usage Reports (read this post by Joel Oleson), nor is any detail about the ADI sync readily available.

Is it asking too much to have a log of detailed ADI results for debugging the import process?

Posted in SharePoint 2013, Today's Disenchantment | 2 Comments

Configure Access Services in a two server SharePoint 2010 farm

This subject is a good example of when not having good information in one place, with some context around it, will lead to failure every time.

The background for this post is that we have a customer where the use of Access Services made very good sense. I have lived through situations where people decided that using SharePoint as a database for dummies was a good idea; only to discover later how bad an idea it really was.

Access Services is not in high demand in our marketplace, but it has a place for cases where all that someone really needs is a controlled environment within which to create simple databases and reports. This approach is especially attractive when compared to hiring someone to do the work in SQL Server directly, as the resident staff might have the Access skills, but are by no means SQL developers. This is the small office scenario that Access was intended for.

There are a few things you need to know concerning the configuration of Access Services.

  1. Access Services 2010 really does require SQL Reporting Services configured for Integrated Mode
  2. SSRS Integrated Mode for a stand-alone Developer installation is not the same as it is for a Production Farm
  3. The Access Services configuration, as of SharePoint 2010 SP1, needs to be tweaked a bit in order for the Reporting web parts to work correctly.
  4. Kerberos authentication is not required for a two-server configuration (but likely is for separate SSRS application server topologies, i.e. 3 or more servers)

The easy part is setting-up the Access Services service application; start here:

The most relevant set of starting point instructions I found for SQL Reporting Services Integrated Mode in a multi-server Farm were in MSDN here:

Referencing the section “Two server deployment, example 2“, you will see the topology I was aiming to setup. Note that as it says, both SP servers need to be at the same build level. Access Services is part of the Enterprise edition of SharePoint 2010 and the SQL 2008 R2 edition used was also Enterprise.

The Installation Order section of this MSDN article is OK, but basically, since this was an add-on to an existing SharePoint farm, what was needed on the Reporting/SQL Server (Computer 2) was to:

  1. Install SharePoint 2010 Enterprise SP1 (or the same bits as your WFE)
  2. Run the Configuration Wizard to join the Reporting Server (SQL Server) to the Farm

You will also need to install the SSRS SharePoint add-on if you have not already done so. When I do new SP farm installations, I add SSRS using the choice to install it and configure it later.

Now you need to configure reporting services. There are two ways to do this, but you may as well go the conventional route (called Remote mode) that enables all integrated SSRS reporting within SharePoint.

Lydia Bronze has a post here that describes in detail how to run through the SSRS Configuration Manager, as well as the final steps in Central Administration you will need to perform to complete the integration of SSRS for SharePoint.

Update 1: For the two server case presented here, Step 13 of Lydia’s post needs to be modified as I have done below in red. Some have had this work by selecting Windows Authentication in a single server model, but the two server model absolutely requires it as far as I can tell.

13. In the Report Server Web Service URL provide the URL we have created in section 10. Choose Trusted Account for the authentication mode and User credentials. Choose whether to activate the feature on all existing site collections or in specified site collection. Click on OK.

OK, at this point if you created an Access Database site using one of the stock site templates such as the Issues Database, you need to do one more thing before you’re successful.

Adam McEwen has these steps documented here and it includes a PowerShell script (Issue 3) to correct the default SSRS configuration (on SQL Server 2008R2 in my case) settings so that Access Services can use SSRS web parts for report rendering (at least with the SP1 bits). If you need to manually update the rsreportserver.config file for SQL 2008R2 to get past Issue 3, then that guidance is here.

Update 2: Note that following the documented guidance linked to above for Issue 3, is incorrect as literally written. The text stating “..under the <Extension> node..” should be omitted, i.e. the change is made immediately under the <Data> node. Shout out to Steve for pointing this out.

I suppose like anything, Microsoft’s documentation, often done by MVPs and partners, is only first-rate when the demand is high enough for that to be the case.

Posted in SharePoint 2010, SSRS Integrated Mode, Today's Disenchantment | Leave a comment

How Many Devices Do You Need?

Attendees of the TechEd North America 2013 conference were offered the sale of a Surface RT or Surface Pro tablet. After looking over the RT specs, $99 was too good of a deal to pass on. I went for the RT even though I already had a Samsung Galaxy Skyrocket II, a new Nook HD, and a two year old HP laptop.

Are 4 devices too many?

I suppose some people of a certain age might not think about this much; and I would not have either way back when. When I was starting in electronics in the 70’s, I would have given anything for any one of these devices.

From a personal use and work perspective, here are a few good and bad points of each.

The Samsung phone is of course small and light and almost always can access the web in addition to my email accounts, calendar, maps, etc. It is a pretty good phone with Ice Cream Sandwich on it; it was just OK with Gingerbread IMO.

The Nook HD is lightweight and large enough to read for extended periods of time. I find the Nook is easy on my eyes and it performs very well. I previously had a Kindle Fire and liked it, but it was heavy.

The Surface RT is fast, relatively light (1.5 lbs), has a 16:9 format display and has or can run most of the apps one would use say 95% of the time. The touch keypad did not take long to get used to, but I think it’s better on a hard surface than on your lap – but it’s supposed to be :-) It can also do remote desktop, which is a major plus. This device has the best battery life of all the ones I own. Outlook and other apps are coming with the Windows 8.1 upgrade.

The HP laptop weighs in at about 5 lbs, is a quad core unit with 16 GB of RAM and a 630 GB hard drive. It can run Visual Studio 2012, run any server under visualization software, and has a full keyboard and 15″ 16:9 format display. This guys has the worst battery life, but not terrible or out of spec. This is the 3rd HP laptop I have owned, and have liked all of them.

Considering my work has taken me to powerful servers, Azure and Office 365 + SharePoint Online, and other Cloud platforms, the laptop is the one device I could actually get along without. I can see where a 13″ Surface device, with its software keyboard, could be one of the last nails in the coffin of laptops; though the ultra-books are kind of there already.

For a guy that started in IBM mainframe product development, this feels like a pretty post-PC era to me.

Posted in Uncategorized | Leave a comment