SP2010: InfoPath, UserProfileService and 401 Unauthorized

The last few days I was working on a problem reported by a user using InfoPath Service. A browser based form had to connect to the UserProfileService.asmx webservice in order to read some profile data to populate a form. This was working untill the web application was switched to claims based authentication. After that change, the webservice stopped working and (401) Unauthorized errors occurred in the logs.

It took me quite some time to find a stable solution, and you need to check for several things. I though I’d share this solution for others to use.

First, this is how our environment is configured:

  • InfoPath Services run browser based forms (problem does not occur client side).
  • The WebApplication is a claims based app.
  • Kerberos is configured as authentication mechanism.

Now here’s what you got to do to get this to work:

  • First, configure a Secure Store entry. If you don’t have a Secure Store setup, you need to get one, there’s enough info out there on how to do that.
  • Configure the entry as type Group with a Windows username and Windows password.
  • Set the credentials for the entry, make sure your user group has access to the entry. The account you configure will need rights to the user profile service application, so you’ll probably want to use a (dedicated) service account.
  • The account you specified needs rights to be able to actually use the webservice. This might be confusing, because all authenticated accounts have enough rights to open the WSDL. But that’s not enough when you want to use the service.
  • To set the correct rights, go to your Service Application management page, click the User Profile Service instance and choose ‘Administrators’ in the ribbon. Add the previously configured account. I chose to give it full rights, but you could fiddle a bit with which rights you actually need to perform the tasks requested.

 

  • Now create your data source in InfoPath and point it to the UserProfileService.asmx webservice (/_vti_bin/UserProfileService.asmx?WSDL). In our case, the GetUserProfileByName method was used to get the users’ details.
  • Because the service will be called as the SSO user, you need to delay loading because you need to set the username as a parameter. So don’t check the checkbox which asks if the data should be loaded as soon as the form loads.
  • Instead, go to the Data ribbon tab and click “Form Load”. Now configure two new actions, first “Set a field’s value”. Choose the username parameter for the webservice method and set userName() as the value (this is a function found under the User function category.
  • As second action, set “Query using a data connection” and set the form to query the webservice.

 

  • Return to your form and click “Manage Data Connections” in the Fields toolpane.
  • Convert your datasource to a data connection file. This file needs to be stored in SharePoint in a Data Connection Library. If you don’t have one yet, create it as you would create a normal document library (but choose the data connection library type instead, obviously).
  • The file will be created as a UDCX. The last step is to open up that file and look for this line:
    <!--udc:Authentication><udc:SSO AppId='' CredentialType=''/></udc:Authentication-->

    Without authentication set, InfoPath will perform the call as an anonymous user, which results in the (401) Unauthorized exception.

  • Replace the above line with:
    <udc:Authentication><udc:SSO AppId='YourSSOAppId' CredentialType='NTLM'/></udc:Authentication>

    Now you’re telling InfoPath it needs to query the configured SSO service application, retrieve the credentials stored for application ‘YourSSOAppId’ and use them to query the webservice. And because you’ve configured an account which you added to the User Profile Service Application, this account will have enough rights to perform the request.

  • When you don’t use claims and you want to use Kerberos to delegate your user credentials, you need to check two more things. First, make sure you’ve configured local loopback checking and disabled it (either completely, or per URL).
  • Secondly, avoid double-hop issues by making sure the web front-end servers point to themselves. Do this by editing the hosts file and add records for 127.0.0.1 (hence the local loopback check) for the DNS addresses you use.
  • And to be complete; of course you need a valid Kerberos setup in that case. But you won’t be able to access your web application at all when you don’t, so that’s a different topic alltogether.

That’s it! With these steps you should be able to use the built in UserProfileService.asmx webservice in a claims (or classic) based web application from within InfoPath forms. Good luck!

 

SP2010: Whoops! I mirrored all of my databases…

So you’re building a SharePoint farm which has to have a high percentage of availability. And to get that, you decide to make your databases highly available by the techniques offered by SQL Server. Think mirroring, or AlwaysOn if you’re using SQL2012. Which technique you’re using doesn’t really matter; you have to realise that this is not supported for all databases!

The good news; the databases which don’t support mirroring aren’t vital to keeping your sites up. It’s these three:

  • Usage and Health Data Collection service application Logging database (typically called “WSS_Logging”)
  • User Profile service Synchronization database (Typically known as “Sync DB”)
  • Web Analytics Staging database (typically called “WebAnalyticsServiceApplication_StagingDB”)

Also note that search databases don’t have to be mirrored, because search has it’s own high availability mechanism. It’s doesn’t matter though, when you do have then mirrored. It’s just not nessessary.

Ok. But you didn’t consider this when you were building your farm. Eventually, you’ll run into problems; most likely when applying updates to your farm. These will fail with the message that mirroring is not supported for your databases. So then what?

Well, just use this Powershell script below and you’ll be able to change the alias for the databases which don’t support mirroring. Make sure that you first create the alias on all of your farm servers!

$alias = "sql-alias"

$uh_name = "Usage and Health Data Collection"
$uh_database = "SP2010_UsageAndHealthDB"
$wa_name = "Web Analytics Service Application"
$wa_database = "SP2010_WebAnalyticsStagingDB"
$up_name = "User Profile Service Application"
$up_database = "SP2010_UPSSyncDB"

Write-Host "Changing the database server for $uh_name"
$uh_app = Get-SPServiceApplication |? { $_.Name -eq $uh_name }
Set-SPUsageApplication -Identity $uh_app -DatabaseServer $alias -DatabaseName $uh_database

Write-Host "Changing the database server for $wa_name"
$wa_app = Get-SPServiceApplication |? { $_.Name -eq $wa_name }
$stagingDbs = ""
Set-SPWebAnalyticsServiceApplication -Identity $wa_app -ListOfStagingDatabases $stagingDbs

Write-Host "Changing the database server for $up_name"
$db = Get-SPDatabase |? { $_.Name -eq $up_database }
$db.ChangeDatabaseInstance($alias)

Source for non supported databases: http://mmman.itgroove.net/2012/03/some-databases-are-too-ugly-for-the-mirror/

Update 24-5-2013: it seems the above script leaves you with a database reference for the WebAnalyticsStagingDB. This causes exceptions with health monitoring rules which check these references for good database health. This can be easily solved by running Get-SPDatabase, selecting the correct one and issuing a Delete() command on it. This will remove the reference, making sure the health rules won’t check it again. This only happens for that single database, it seems the other ones are being cleaned up properly.

Breaching security, the easy way

Today I was fiddling around with Azure once more, this time exploring the capabilities of the Service Bus Relay functionality. That sounds more difficult than it actually is.

The scenario comprises of an internal webservice and the wish to make that available through the internet. Many developers will know the troubles this usually gives you. You have to get a DNS name and talk to the guys in charge of firewalls. And those guys usually don’t like opening ports, especially ports from the outside. So they say they won’t do it, you have to go to your manager who goes to their manager. A lot of meetings and time go by and eventually the port is opened up because the business really needs this functionality and has no other option.

Well that other option: it’s here now! Here’s what you do:

  • Create a service (interface and class, simple) and host it in a process (console app or windows service). This service connects itself to Azure and registers as an endpoint.
  • Now you can create a client which connects to the service, but it does that via Azure. So no direct connection is needed and no ports have to be opened up, how great is that.

It’s really easy to set up, check the instructions here.

But that’s also what kind of worries me, cause security loopholes become quite hard to spot with technology like this. From the perspective of a security guy, I wouldn’t be too enthoused about people creating all kinds of webservices and making them available through the entire internet without me knowing.

There’s one thing to mention though; you cannot just call your webservice. You’ll need the namespace of your service bus instance created in Azure, and you’ll need the access key to connect to the servicebus. That’s some relief. As long as you keep those two strings to yourself, you’re good. So make sure you don’t just store them in plain sight where someone else might get to them. Don’t deploy your apps with app.configs where those things are set in plain text. Give it some thought, because your security officer will not like you when he finds out… ;-)

SP2013: Rethink your thinking

These last few days I attended the SharePoint Connections conference in Amsterdam. Okay, it’s not Vegas, but is was cool anyway. Of course there was a lot of cloud, Offfice 365, app model and more of what we already learned from Vegas.

But one really big thing was a statement made by multiple speakers. SharePoint began as a product and slowly developed itself as a platform. Especially the 2007 and 2010 versions were seen as application platforms. We developers were creating things on top of and in between SharePoint bits and bytes. And although that didn’t (doesn’t, actually) always worked out too well, it was the way we rolled. That made the “SharePoint Developer” a breed of it’s own. You either loved it (like I do) or hated it (like a lot of .NET people do). That’s because of the learning curve, the workarounds you need to do, all the stuff that isn’t so transparant. On one hand that’s good, since it means SharePoint devs get a job real easily because of the enourmous demand. On the other hand that’s bad since there wasn’t that much interaction between SharePoint land and the rest of the world, at least not in Microsofts view.

Now with 2013, things will change. Because SharePoint isn’t seen as a platform any more. It’s an application again. An application with an extensive API, but still an application. And although that might not strike you as a big deal, it really is. I’ll try to explain why.

In the old model, developers were trying to get a lot of things into SharePoint. Solutions made use of SharePoint lists to share data, hooked into event handlers and did all kinds of funky stuff on the web where they were deployed. Sometimes good things, sometimes not so good things, but that’s not really the topic I want to discuss. The point is; the solutions really integrated with SharePoint and were built on top of it. Now with the new app-model, you’re going to create apps which are loosely coupled with SharePoint. You’re code won’t run inside of SharePoint any more. And allthough you still have the options to create lists and such; you’ll do so in the app web, not in the place where you’re app was deployed. I’m not saying you can’t do that; I’m saying the model tells you not to.

That means apps will become disconnected again, and developers and architects will have to rethink their way of working. Will I store data in a SharePoint list? Why would I? If my app is on Azure, why wouldn’t I just use a Windows Azure SQL database? Why bother with storing the data in an appweb where the user isn’t touching it anyway? Because let’s face it; storing data in a SharePoint list comes with it’s challenges too. Pretty valid questions if you ask me, and questions I don’t (yet) have an answer to.

In one of the sessions I attended, the speaker showed us how easy it was to link a web shop kind of app to SharePoint. That link consisted of the user automatically logging in with his Office 365 ID and the e-mail address box being prefilled, and an automatic post to the activity feed once an order was placed. Nice, but not that thrilling. It’s just a webshop, that’s not a real big deal these days.

My point is; apps are web applications again. And installing an app into SharePoint is basically nothing more than pointing a link to a URL and doing some nice OAuth stuff in the background. And the loose coupling means the app doesn’t depend on SharePoint, and neither does SharePoint depend on the app. I’m not saying I dislike the model or that I prefer the old way. I’m saying I have to redo my thinking when it comes to architecting SharePoint solutions. And what to do with old solutions? Yes, they will work in 2013 as well. For now. But if you want to migrate your WSP-style solution into an app, that’s going to take a lot of work depending on the size of your solution. If you don’t, you’ll end up with functionality being inside of SharePoint and apps being outside of SharePoint. How will your users react to that?

A lot of questions to be answered in the upcoming months. One thing is clear: things are going to change!

Denkt u om uw privacy?

Even een kruising tussen een normale column en m’n techtalk vandaag. Want vandaag wil ik het met u hebben over “The Cloud”. Het is dé hype term van afgelopen jaar in IT land. En zelfs de postbode weet waarschijnlijk wel waar ik het over heb. Want nu tegenwoordig Google in the cloud zit, Apple een iCloud heeft en Microsoft al uw Windows data graag in the cloud wil opslaan, kunt u er niet echt meer omheen.

Goed. De cloud dus. Dat klinkt spannender dat het is. Als je het erg eenvoudig uitlegt is de cloud gewoon wat de meeste mensen beschouwen als “het Internet”. Waar staat je mail als je G-mail hebt? Op het internet. Waar upload je je foto’s naartoe als je Picasa gebruikt? Naar het Internet. Goed, maar het Internet is geen makkelijke marketing term omdat die inmiddels al veel teveel ingeburgerd is. Dus heeft men het nu over de cloud. Kortom: datacenters, volgepakt met computers en harde schijven: dat is de cloud.

Nu zijn er een hoop mensen die vinden dat je voorzichtig moet zijn met de cloud. Je moet er zeker niet zomaar al je gegevens naartoe kopiëren. Daar zijn verschillende argumenten voor zoals “je weet niet waar het staat”, maar nog belangrijker: “je weet niet wie er meekijkt”. Onder ieder nieuwsbericht wat over cloud diensten gaat zie ik diezelfde mensen weer reageren: “Pas toch op mensen! De geheime diensten kijken mee!”.

Ik ken deze mensen niet persoonlijk, toch heb ik ernstig het idee dat ze zichzelf zwaar overschatten. Ze verwachten namelijk dat, wanneer ze hun privé gegevens in de cloud parkeren, de overheid meekijkt bij alles wat ze doen. Daar zijn ze blijkbaar belangrijk genoeg voor. Ik vraag me op mijn beurt af: als dat al zo zou zijn, wat ik ten zeerste betwijfel, wat mogen ze dan niet vinden van je? Ik kan met recht zeggen dat het me geen zak zou uitmaken als de AIVD of wat mij betreft de CIA of FBI in mijn gegevens zouden zitten. Ik zou me ten eerste afvragen waar ze in hemelsnaam naar op zoek zijn, want zo interessant ben ik nu ook weer niet. Daarna zou ik me bedenken wat dat wel allemaal niet kost, van mijn belastingcenten. Tja, zo ben ik dan ook wel weer.

Mensen die heel hard roepen dat je niemand moet vertrouwen, die hebben blijkbaar wat te verbergen. Maar wat dan in hemelsnaam? Bewaar je de liefdesbrieven naar je vroegere vriendin zonder dat je vrouw dat weet? Of heb je wel eens sexy foto’s van je vrouw bewaard en die op Picasa gezet? Vind ik sowieso risicovol. Of staan er echt belastende foto’s op van jouzelf in adamskostuum terwijl je de kunst der zelfpleziering uitoefent? Tja ik gok maar wat mensen, want dat soort content heb ik dus niet.

Het vervelende is dat door deze hele discussie alles wat de cloud heet wordt beschouwd als onveilig. Zo ook hele goede initiatieven zoals het patiëntendossier. Wat is het nu makkelijker dan 1 centraal dossier waarin alle artsen kunnen lezen over jouw gezondheid? Maar nee, het zou zomaar eens kunnen gebeuren dat een arts meekijkt in je dossier terwijl die helemaal niets met jou te maken heeft. Waarom hij dat zou doen is een tweede, misschien regende het te hard om naar de golfbaan te gaan?

Volgens mij gaat het de meeste mensen helemaal niet om het feit dat anderen hun gegevens kunnen inzien. Je wil misschien alleen graag weten WIE dat dan zijn. Daar kan ik me wat bij voorstellen. Als de AIVD 2x per maand door mijn SkyDrive bestanden bladert; prima, maar dan wil ik dat op zich best weten. Want dan kunnen we eens Kamervragen laten stellen over wie die onzin gaat betalen. Als een dokter uit Groningen mijn patiëntendossier graag wil inzien dan mag dat wat mij betreft, maar dan wil ik het wel graag weten. Zodat ik eventueel eens aan de beste man kan vragen wat hij daar te zoeken had. Dat maakt het al een stuk prettiger om je gegevens neer te zetten. En geloof me: dat is technisch allemaal prima mogelijk. Of denkt u defensie niet weet wie hun top-secret bestanden leest? Oké, dan tel ik de vergeten USB-sticks even niet mee.

SP2013: Offline install fails on prerequisites

For a testfarm, I was preparing a SharePoint 2013 install script today. Because the farm shouldn’t have internet access,  I opted for an offline install. This means you need to download the prerequisites beforehand so they can be installed by the script. So I did and I ran the installer, but it failed on me.

Looking in the logs, I noticed that there was an error installing the WCF Data Service 5.0 prerequisite. In the logs it said things about certificate validation errors, or more specific:

“A certificate chain could not be built to a trusted root authority”.

I ran across this post on TechNet: http://social.technet.microsoft.com/Forums/en-US/sharepointitpropreview/thread/fc3aec98-5c42-4ef8-8d99-64aa8e3213b6. It says you need to download the root certificate updates for Windows Server 2008. I downloaded something which looked like it, but that didn’t fix my problem.

After a lot of downloading and retrying, I decided it was probably easier to allow the server to go online for a little while. And it was. I ran the installer manually after providing the machine with an Internet connection and it suddenly ran without any problems. It seems the certification process autodownloads new root certificated when needed. With those, the checks succeeded and the installer was able to run. Now everything is installing fine again. Cool :)

De automaat

Deze week zat ik op een flexplek, achter mijn laptopje te werken. Het leuke aan flexplekken is dat je iedere keer op een andere plek kunt gaan zitten en weer andere mensen tegenkomt. Dat werkt afwisselend en inspirerend, iedereen heeft zo weer zijn eigen nieuwtjes, ideetjes en gewoonten.

Mijn plek bevond zich dit keer vlak bij de automaten. De gratis automaat met koffie, thee, chocolademelk en soep. En twee betaalde automaten; één voor blikjes en een voor snacks en tussendoortjes. En die betaalde automaten, die trokken al snel mijn aandacht. Want wat misschien wel heel logisch is verbaasde me toch; de gemiddelde automaatbezoeker is echt fors zwaarder dan mijn gemiddelde collega.

Het meest bezoek diende zich ‘s middags aan. Ik begon me in te denken hoe dat moest zijn. Rond een uur of 2, inmiddels alweer even na de lunch, toch een beetje een knagend gevoel krijgen. Onrustig op je stoel zitten, de afweging maken of je toch maar weer moet gaan lopen of misschien beter toch niet. Onrustig kijken naar het klokje rechts onderin het beeld, nog lang geen 5 uur. Uiteindelijk dan toch maar opstaan. Een verlichtend gevoel omdat je eindelijk de stap gemaakt hebt. Een schuldig gevoel met dezelfde reden.

Voor het glas staan, zoveel keuze. Chocolade, of zoet? Proberen om toch nog gezond te doen, of gewoon het lekkerste nemen? Je goedgevulde portemonnee uit de broekzak halen om vervolgens alleen de chipknip maar nodig te hebben. Een keuze maken, zonder goede rede maar met gevoel. Het ijzeren spiraal zien ronddraaien waarmee de snack naar keuze zich langzaam naar voren beweegt. Hopen dat hij niet weer blijft hangen, dat is iedere keer zo’n gedoe.

Een zachte plof in de bak. In het donker graaien om in de hoek de plastic verpakking te voelen. Daar is dat gevoel van opluchting weer. Scheuren, geuren. Die hap waarvan je weet dat hij een korte periode van genot inluid. Jummie.

En dan vanavond weer braaf gaan sporten he? Blijkbaar niet.

Hey Google, hou eens op.

Al eerder schreef ik over Google’s poging om u het Google+ netwerk op te dringen. U weet wel, de tegenhanger van Facebook waar zoveel van verwacht werd. Maar waar Facebook laatst wist te melden dat er inmiddels 1 miljard actieve gebruikers zijn… 1 miljard… da’s 1 op de 7 mensen ter wereld. Afijn, daar blijft Google dus nog een beetje mee achter.

En dus is de zoekgigant op zoek naar manieren om ons naar Google+ te lokken. En dat doen ze op slinkse wijze blijkt. Het begon natuurlijk met de introductie van de +1 knop in de zoekmachine. Klikken terwijl je ingelogd bent = score voor Google, want dan krijg je meteen de mogelijkheid om je accountje aan te maken.

Daarnaast hebben ze die prachtige balk bovenin het beeld bij alle programma’s van Google. Daarin staan linkjes naar allerlei handige applicaties die je misschien ook wel wil gebruiken. Nee, Google. Dat wil ik helemaal niet. Ik wil gewoon fijn zelf bepalen welke programmaatjes ik wel of niet gebruik. Dwing me het niet op aub.

Goed. Het mooiste was toch wel afgelopen week. Ik zat op YouTube naar een filmpje te kijken en werd toen gevraagd of ik misschien mijn profiel wilde vervolmaken. Hartstikke mooi, zou op YouTube mijn volledige naam gebruikt worden, fotootje erbij, hatsjee. En ach, ik had wel even 5 minuten, dus ik vul dat formuliertje in en klikte op “JA!”. Nu moet ik toegeven dat er wel 2 “ik ga hier mee akkoord” vinkjes aangeklikt moesten worden waar wel erg veel tekst bij stond. Maar ik lees die teksten net zo goed als jij dat doet, dus vinken en gaan.

De volgende dag werd ik verrast door de mededeling dat een bekende me had toegevoegd op Google+. Huh? Op Google+? Zit ik op Google+ dan? Het duurde even voordat het kwartje viel, maar toen was de link natuurlijk snel gelegd. Een van die vinkjes op YouTube betekent blijkbaar: “ik knal mijn profiel op Google+ en dan mogen jullie dat fijn gebruiken om mijn fotootje op YouTube te laten zien”. En daar komt nog eens bij dat je dan met ieder YouTube bezoek ook meteen een actieve Google+ gebruiker bent. Handig voor de statistieken natuurlijk.

Bah. Daar hou ik dus niet van. Gelukkig is het ook heel eenvoudig om je profiel weer te verwijderen. Wel even op de vinkjes letten, want als je de verkeerde keuze maakt gooi je niet alleen je Google+ profiel weg, maar ook al je linkjes uit Google Reader en je foto’s uit Picasa. Hartstikke handig dat dat allemaal aan elkaar vastgeknoopt zit. Niet dan?

My new job!

As found on my website, I recently switched employers. After working for 5 pretty good years at Datamex Breda (in the south region of Holland), I decided it was time to get moving. After all, 5 years at your first company seems to be a pretty long time in todays world, and there’s definitely more out there.

So, what then? I needed some real change. Not a similar company doing similar things, but something really completely different. And I found that at AtoS. For those of you who don’t know AtoS, it’s a pretty huge company. Around the globe I’ve got about 75.000 colleagues. So sending an e-mail “to all” isn’t a real good idea any more. And even when you don’t know us, there’s a good chance you used one our services like credit card or online payments and watching the Olympic Games. Indeed, that’s us.

It won’t be a big surprise I’m still focussed on SharePoint. But instead of just focussing on development, I’m now titled an architect. The definition of an architect is kind of vague (in general that is), but it boils down to being the person (or part of a team) thinking and designing solutions instead of building them hands-on. Although I have some good years of SharePoint experience, I still lacked a bit of infrastructure knowledge. So last month I’ve been studying hard to earn SharePoint certifications. Passing the last one today, I can now proudly list the following certifications:

  • 70-573: SharePoint 2010 Application Development
  • 70-576: PRO: Designing and Developing Microsoft SharePoint 2010 Applications
  • 70-667: SharePoint 2010 Configuring
  • 70-668: PRO: Microsoft SharePoint 2010, Configuring

And that sums to MCPD and MCITP!

Besides that, I want to keep a good eye on what’s happening with Windows Azure and Office 365 / SharePoint Online. There’s a lot of movement going on in that world and I am convinced that migration and hybrid scenario’s will become more and more mainsteam.

So that’s that. While writing this I’m in my 5th week and I’m very satisfied with my job thusfar. I’ll keep on writing my blogposts, nothing will change there. The only thing which might change in the near future is the location of these blog posts. I’m looking for a good way of migration all of the content into my main website. But only when I can also create automatic redirects for existing content, so rest assured; all links will keep working!

Cheers!

SP2010: Saving yourself a full SQL license.

As I’m studying for Microsoft SharePoint certifications, I came across the “Remote BLOB Storage” topic. It’s an interesting technique, allowing you to store BLOB files (documents, videos, images, etc.) outside of SQL Server. The main reason why you’d want to do that is performance. SQL doesn’t handle those BLOB things that fast and they fill up your database. So you turn on RBS, files are stored on disk and everyone is happy again.

But there’s another opportunity RBS gives you. At the moment, my job involves the larger enterprise farms. But I have seen a lot of single server SharePoint installations as well. Many companies starting off with SharePoint Foundation out of budgetary reasons: keep listening. SQL Express is the “light” version of SQL. It’s pretty well featured, it’s 100% supported running SharePoint on top of SQL Express. One disadvantage is the 10GB database size limit. You reach that, you need to pay.

But using RBS / FILESTREAM in SQL Express, your files won’t be stored in the content database any longer. And the documentation clearly states:

SQL Server Express supports FILESTREAM. The 10-GB database size limit does not include the FILESTREAM data container).

That stretches the limits on the SQL Express scenario’s quite a bit! And it only takes a little bit of configuring. So when you want to postpone buying those expensive SQL licenses a bit more; check out RBS! For a normal intranet SharePoint farm in a small company, your databases won’t reach 10GB very soon. The only one you probably need to watch when storing a lot of documents is search.

Should you feel like sending me a small percentage of the money you just saved yourself, you’re quite welcome to ;-)