Tuesday, April 09, 2013

SharePoint and SSRS (integrated mode) gotch-a

Hey People,

hit a doozie recently.  Set up SharePoint 2007 with SSRS 2008R2 (using the SSRS 2008 add-in) in Integrated mode, on the same server (accessing SQL 2008R2 DB and SSAS 2008R2 on another server).

Got all the Kerberos sorted (a mission as usual) and then found that somewhere between SharePoint and SSRS the users credentials were being cached, so that every user appears in SSRS as the first user to login (until the session timesout).

Turns out, there's a little flag in the rsreportserver.config file:

false

By default it's set to true and the documentation says to set it to false when you've got a proxy sitting between your user and the SSRS server. Actually, it gets worse than that. the rsreportserver.config file has EnableAuthPersistence with an E, however the documentation refers to EnableAuthPersistance with an A. (I've logged a community addition to the page to get this rectified)

So, while SharePoint technically isn't a proxy and the Kerberos should be taking care of the user context, turns out, you need to set EnableAuthPersistence to false and have every connect to the SSRS server authenticated, as SharePoint re-uses it's connections to SSRS and doesn't seem to force new credentials.  The down side: every request is now done twice, once anonymously and then again with the correct user credentials.

Thanks to Ning at Microsoft Premier Support for figuring this out for me and I look forward to hearing if this is a bug in SP, SSRS or Kerberos or "by design" :)

Oh, better check my SP2010 instances to see if they suffer the same issue.

Later'ish
Craig

Update: Ning wasn't really able to expand on the "why", other than to say:
"If we set “EnableAuthPersistence” to False, it means the reporting service will not cache the previous authentication result. So we can see 401 challenge happens in new http log, it is required by each new authentication, which is an expected behavior. Our user account should be delegated by service accounts rather than server, so even though we have SSRS and SP installed in the same server, the authentication will go through all identities of the services in server."

Monday, January 07, 2013

The Trains, The Trains!

[rant]
Oh dear, not even two weeks into 2013 and I'm having a second rant already... doesn't bode well...

OK, so today was my first day back at work, admittedly somewhat electively (wanted to get some things done during the day that would normally need out-of-hours outages. And I want to get along to the girls school camps later in the year.) and I knew the trains were going to be fun...

In case you hadn't heard, many of the Auckland stations are currently being upgraded (putting up awnings/roofs that actually might be waterproof... somewhat essential in Auckland) and they're also in the middle of finally electrifying the Auckland rail network (is it just me, or is it about time they linked all the electrified bits of the main trunk line?), so there's construction all over the place and busses replacing trains between Newmarket and Britomart.

So I get to my station early (Greenlane), just in time to watch a train pull out, doh! But another one arrives in about 5 minutes, ahead of what I was expecting (I thought it was supposed to be a Sunday timetable still), so I was stoked at the prospect of getting into work early.

The train pulls up at Newmarket and everyone's off, most looking find the busses into Britomart. Of course, getting out of Britomart requires swiping your AT (travel) card at the barriers, so you've only been charged for the trip to Newmarket, but then, they're inconveniencing us with busses, so that's fair. We sardine onto the bus, with most people left behind for the next bus. There's no ticket inspection, though I did proffer my AT card.

OK, so far so good, I'm easily on time and we're moving.
Then we hit Kyber Pass Road, and stop, and move forward a bus length, then stop. 35 minutes later!, we get to the Grafton Station (on the road past the hospital), where a) the bus driver asks if anyone wants to get off (no-one does) and b) we see two empty busses waiting to take people from the Grafton Station to Britomart. Turns out there's roadworks all over Kyber Pass and basically no-one is using Grafton Station (I guess most of it's customers would be from the Hospital and perhaps Uni), so that was a good choice of route, given that Uni is off for summer and I'd like to think it's relatively quiet at the moment.

Finally we get to Britomart, the bus dropping us off next to where we should catch it back home again, all-in-all a 45 minute trip and I'm late for work. BTW the train would take about 5 minutes...

OK, so then, later in the day, it's home time. I head off to catch the bus/train home, from the same place I'd been dropped off at, in the morning.
There's a huge queue of people and three busses, turns out they're using an normal bus stop and most of the people there are waiting for the normal bus, because a quick query to one of the half-dozen Auckland Transport staff reveals that the front bus is heading to Britomart, so I jump on, flashing my AT card (it was more than enough in the morning, so I figured it was the protocol for going home too) and take a seat (yup, not quite sardines this time).

As we head off to Newmarket (again via Grafton, where again no-one gets on or off) a ticket conductor springs out of no-where and starts working his way up from the back of the bus. Now you can't buy tickets on trains anymore (you have to have an AT card [which most people seem to have], or buy a paper printed ticket on the platform - or the manned booths at some of the bigger stations) and yet, this guy is wandering through the bus trying to get people to buy paper tickets, with cash. Every second person protests and tries to explain that all they have is an AT card (with either stored cash, or a monthly pre-pay), they're getting grumpy and so is he. Turns out, if you're using a cashed AT card, they expect you to go to Britomart, go down two escalators and swipe, but not go through, a gate, then come all the way back out of Britomart and off to the bus, where you say "yes sir, I have swiped my AT card at Britomart), otherwise you get a scowl and two attempts to extract cash before he moves along (monthly users only get asked for cash once).
The trips not too long, about 15 minutes, which is probably about what it should be for this time of year.

Then it's into Britomart, past the dozen or so AT Transport staff, sitting around the square, mostly smoking, swipe through the gates and down to the platform, where two trains await (both covering my stop) with the first to leave in a couple of minutes, the second about 5 minutes later. I get on the former and take a seat. About five minutes later, we haven't left and there's an announcement that the crew hasn't arrived for the train.
So I jump off and climb onto the other train, the doors close a moment later, to an announcement that the other train has been cancelled due to "operational difficulties". I guess the crews in the square didn't make it back from smoke-o in time...

I get off at my stop and swipe off the platform and get home about 25 minutes later than if I'd only had to catch the train, not too bad all things considered.

So where's this going? After all that's a lot of drivel I've put you through so far!
Today was the first day back at work for a lot of people, but next Monday will be way worse as most offices in the CBD will be properly open, if not fully staffed, but the busses will still be replacing trains. So unless Auckland Transport get their act together, there's going to be a lot of annoyed commuters.

And it's not like Auckland Transport have a good rep, with frequent delays and multiple outages of the entire rail network (caused by system failures in Wellington apparently) and we wont even talk about what happened during the Rugby World Cup....

But what can they do?
Set expectations?
Set up swipe posts at the bus stops (or better yet, get them on the busses, you're going to have to do it anyway when the systems finally merge) or give your conductors mobile units, don't expect people to have to revert to a system you've just spent months telling people is gone.
Use your stats to tell you where people are likely to be going, so you don't run your busses via overly long tedious routes.
Don't expect people to pay more for a worse experience.
If you're still expecting a single swipe-on and off for a multiple hop/vehicle journey, then you've got to provide a way to get from one mode of transport to the next. And that's just not happing currently.
[/rant]

Oh well, back to work again tomorrow...

Saturday, January 05, 2013

I want my UFB

[rant]
OK, so we've just finished our first full month on Telecom's 500gig plan.  Yes, that's 500gig, half a terrabyte, or in technical terms, oodles of data. (I'm aware of whole schools that have less, infact the firm I work for doesn't have a lot more...)


Now I've been with Telecom/Xtra since the beginning of broadband, when my ASUS ADSL router hooked me direct into work so I could work from home two days a week when Amber was born (that's back in July 2001).
And dispite Telecom/Xtra's less than stellar reputation (it took them a year and a case of beer to get my billing right) and everyone telling me I should switch to ISP X, I've stuck it out.  Not that I haven't been tempted...
Over the years, various plans (more gigs or more speed on unbundled exchanges) have been very tempting, but Telecom/Xtra have consistently come back with something that's been "enough" to stop me switching.


Disclaimer: I don't use Xtra for email and aside from the billing woes of my first year, you can count the number of times I've had to call the Telecom/Xtra helpdesk (for Internet) on one hand and still have some spare fingers...

So there I was on Telecom/Xtra's 160Gig plan, looking longingly at various 200Gig and Unlimited (well, none are truely unlimited, they all have a variety of constraints/restrictions, many of which aren't spelled out so you just know you're walking into a world of hassle) plans, thinking maybe it's finally time to switch, especially given that where we live is not on the 3 year (err... only 2 years of that left now I think) UFB roll-out plan.  Then along comes the new Telecom/Xtra 500Gig plan, for $6/month less than I'm paying for their 160Gig plan (how does that work?). Leap!

So, you're probably all thinking "Do you REALLY need 500Gig? Surely 160Gig is more than enough?", after all, most of you are probably on far smaller plans and they are working just fine for you.  OK, so first up, we have a number of Internet connected devices (TV, iPhone, iPad, two iPod Touches, PS2, MagicTV (FreeView recorder), AppleTV, three laptops, a desktop PC and two servers. That's more than some people, but I know a fair number that have more. The updates alone put a dent in our monthly quota of bandwidth (for the geeks: I used to run a proxy to try and cache things, but it generated almost as many hassles as it solved), plus my girls are into YouTube and Ngaire loves posting photos and videos for her friends.  Oh and I download a fair bit too.

So we've been banging up against our 160Gig limit fairly freqently, popping over it a number of times, dispite having a little guage on my laptop that tells me how much we've used...

But with 500Gigs I no longer have to worry about my quota, I would have to try seriously hard to actually use that up.  And to prove it, my first month came in at 112Gig (note: we were away for a week, which would have decreased it).  So now I not only have oodles of quota (who needs "unlimited" when your quota is more than you can use?) but I don't feel I have to use it up.

But what has all this got to do with UFB?
As I've mentioned, our part of our street is not included in the initial 3 year UFB roll-out (dispite being in an afluent area, two blocks from a primary school and two blocks from an intermediate and the Telecom "box" is right at the end of our driveway) so I'm unlikely to get UFB anytime soon, so I'm looking to make the best of what speeds I can get (18Mbit/s down, 940Kbit/s up according to my router today).  According to my crude math, if I could saturate my connection continuously, it would take about 2.5 days to use up my 500gig plan! Now that seems quite quick.


UFB comes in two speeds:
  • 30Mbit/s down & 10Mbit/s up
  • 100Mbit/s down & 50Mbit/s up.
Orcon offer UFB plans in 30Gig, 60Gig and "unlimited" but their "unlimited" fair use policy is based on the average usage of their customer base - the wording is sufficiently thin that I suspect it's not the "average" of customers on their "unlimited" plan  and it possibly includes non UFB plans!, so your potential is being pulled down by cutomers on 30Gig plans.
 
So anyway, 30Gigs at 30/10Mbit/s - 2.3 hours.
60Gigs at 100/50Mbit/s - 54 minutes!
Even 500Gigs at 100/50Mbit/s is 7.5 hours.
 
OK, so sustaining that level of bandwidth is unlikley, but I'm just trying to show how small the quota is when compared with the bandwidth given. After all, what if a "hacker" decided to flood your connection, it wont take them long to push you into extra cost or back to dial-up stone-age speeds.
 
What's my point?
For UFB to "work" for home usage, a number of things need to exist, content (HD videon [Netflix, Hulu, YouTube, etc], internet radio [Spotify, etc] and HD streamed games being the most obvious and bandwidth intensive available currently), services (online photo/video editing? HD multi-user video chat [e.g. Skype], being some obvious ones) and consumers to consume it ("build it and they will come"?).


Now the likes of the USA have most these things to some degree or another (though often not available to those outside of the USA) and if you're prepared to pay extra in NZ, you can even get some of the premium video (Sky has all the TV/Movies pretty much tied up, so no sign of Hulu or Netflix here).

But the key catch in NZ is the quotas on our Internet plans. 30Gig = ~ 30 hours of average quality 720p video or about 6 hours of average quality 1080p video. So the Internet is not going to replace your TV/DVD/BluRay in any great hurry (yes, various ISPs have deals where iSky/YouTube/etc don't count against your monthly quota, but what if you get your video from somewhere else?).

So until consumers don't have to worry about going over their quota after watching only a handful of movies each month (or their kids watching a string of TV shows), I don't think the UFB story is sufficiently compelling for most homes.

However, if I can keep my 500Gig plan, I'd leap on the 100/50Mbit/s UFB, though I'd be back to watching my usage closely, but Ngaire will get a much better experience uploading her videos and photos and we should be able to watch multiple videos at the same time as downloads are going on.

Though it will be interesting to see how various NZ services and the Southern Cross Cable (NZ's primarily link to the rest of the world) cope with consumers having significantly more bandwidth.
[/rant]

Tuesday, October 30, 2012

Enumerating SharePoint 2007 user permissions

As part of another project, I recently needed to enumerate through all the sites, subwebs, lists and items to determine which users had been assigned what rights.

Originally I came across this post by Roger Cormier, which provided a great base, but had a few issues:
  1. It was SP2010 based (SP2007 doesn't have Get-SPWeb)
  2. It didn't handle Items
  3. It didn't handle sub-site/web/list/items of parent site/web/lists that didn't have unique permissions.
  4. It didn't handle membershipproviders
So after some mangling, I submitted this back to Roger.

I then distilled it down to the following script, which is cruder, but outputs a CSV that I can then use for various automated tasks.

Feel free to do what you like with it.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
function stripProvider([string]$userName)
{
    if($userName.split("\").count -gt 1)
    {
        $userName.split("\")[1]
    }
    elseif ($userName.split(":").count -gt 1)
    {
        $userName.split(":")[1]
    }
    else
    {
        $userName
    }
}

#This function determines the source of the user AD/Local NT vs Membership provider
Function UserSource([string]$userName)
{
    if($userName.split("\").count -gt 1)
    {
        $userName.split("\")[0]
    }
    elseif ($userName.split(":").count -gt 1)
    {
        $userName.split(":")[0]
    }
    else
    {
        ""
    }
}


$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$farmWebServices = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
foreach ($farmWebService in $farmWebServices) {
  foreach ($webApplication in $farmWebService.WebApplications) {
    foreach ($site in $webApplication.Sites)
    {
        foreach ($web in $site.AllWebs)
        {
          # Write-Host "Site Collection: ID:" $site.ID " - URL: " $site.Url
          if ($web.HasUniqueRoleAssignments)
          {
            foreach ($RoleAssignment in $aList.RoleAssignments)
            {
                if(UserSource($RoleAssignment.Member.LoginName) -ne "")
                {
                    "web,direct," + (stripProvider($RoleAssignment.Member.LoginName)) + "," + (UserSource($RoleAssignment.Member.LoginName)) + "," + $web.Url + "," + ($RoleAssignment.RoleDefinitionBindings | select name).name
                }
                else
                {
                    $allUsers = $Roleassignment.member.users
                               
                    #Perform some action against all members returned.
                    foreach($User in $AllUsers)
                    {
                        "web,role," + (stripProvider($User.LoginName)) + "," + (UserSource($user.LoginName)) + "," + $web.Url + "," + $RoleAssignment.member.name
                    }
                }
            }
          }
         
          foreach ($aList in $Web.lists)
          {
              if ($aList.HasUniqueRoleAssignments)
              {
                  foreach ($RoleAssignment in $aList.RoleAssignments)
                  {
                      if(UserSource($RoleAssignment.Member.LoginName) -ne "")
                      {
                          "list,direct," + (stripProvider($RoleAssignment.Member.LoginName)) + "," + (UserSource($RoleAssignment.Member.LoginName)) + "," + $web.Url + $aList.DefaultViewUrl + "," + ($RoleAssignment.RoleDefinitionBindings | select name).name
                      }
                      else
                      {
                          $allUsers = $Roleassignment.member.users
                         
                          #Perform some action against all members returned.
                          foreach($User in $AllUsers)
                          {
                              "list,role," + (stripProvider($User.LoginName)) + "," + (UserSource($user.LoginName)) + "," + $web.Url + $aList.DefaultViewUrl + "," + $RoleAssignment.member.name
                          }
                      }
                  }
              }
              foreach ($anItem in $aList.Items)
              {
                  if ($anItem.HasUniqueRoleAssignments)
                  {
                      foreach ($RoleAssignment in $anItem.RoleAssignments)
                      {
                          if(UserSource($RoleAssignment.Member.LoginName) -ne "")
                          {
                              "item,direct," + (stripProvider($RoleAssignment.Member.LoginName)) + "," + (UserSource($RoleAssignment.Member.LoginName)) + "," + $Web.Url + "/" + $anItem.URL + "," + ($RoleAssignment.RoleDefinitionBindings | select name).name
                          }
                          else
                          {
                              $allUsers = $Roleassignment.member.users
                         
                              #Perform some action against all members returned.
                              foreach($User in $AllUsers)
                              {
                                  "item,role," + (stripProvider($User.LoginName)) + "," + (UserSource($user.LoginName)) + "," + $Web.Url + "/" + $anItem.URL + "," + $RoleAssignment.member.name
                              }
                          }
                      }
                  }
              }
          }
       }
       $site.Dispose()
    }
  }
}

Wednesday, May 16, 2012

SharePoint on mobile: HTML vs Generic App vs Custom App

Here at work we've been working with SharePoint since mid 2006 (SP2007 beta) and most of what we do is based around SharePoint 2010 these days. So with the influx of smartphones and tablets (OK, to be honest, it's largely iPhones and iPads, with a very small sprinkle of Android and WM7 devices) we've been looking at what our corporate response is and how to combat the likes of DropBox.


Given that we already use SharePoint for our Intranet, Internet and Extranet sites, it seems fairly logical to find a way of leveraging it to deliver content, securely, to mobile devices.  Currently there are three main approaches to supporting mobile devices:

I'm not going to say which approach is best for you, as every situation is different. But I will say you need to look closely at what you're trying to achieve and how you expect your users to interact with your content.

For example, here's our WWW site in Safari on iOS 5.1.1 on my iPhone 4s:
It's nicely branded, looks basically the same as it does on a desktop browser.  The content is largely HTML based with the odd PDF.  It's also basically a read-only site as we use SharePoint's content deployment to push the content from an internal staging farm to the DMZ based external facing farm.

Now here's the same thing in Filament Lite:
OK, so we're now looking at the "raw" SharePoint content, with Sites and Lists. But no branding, no wazy animations and mouse-overs, no neat menu structure and no whole-site search (Filament Lite only supports searching within a list). Not such a great story for consuming our WWW site then.

On the other hand, what about something more collaborative.  Here's part of one of our Extranet sites in Safari, again on my iPhone 4s:
So, now we've got a more vanilla branding and the site is farm more out-of-the-box SharePoint.  We've got a bunch of document libraries, each with various Word, PDF, Excel, PowerPoint and Project files. Safari is more than happy to navigate the site and open/view most of the documents, it's pretty much a read-only experience (you could do something around download/upload, but that's quite cumbersome). Lists however have the full read-write capabilities thanks to being simple HTML forms and fairly standard bits of JavaScript and CSS.

OK, how about Filament Lite:
Yup, we've got our document libraries and lists.  It's more than happy to drill into these:
Unfortunately, the Lite version doesn't support editing list items or documents, but I know the full/paid versions of the SP Apps listed, often support editing and I've seen editing on the iPad equivalents.

So what about Custom Apps?  A custom app has the potential to provide both branding and target the functionality that your users need, but at what cost? And how many platforms do you need to support?

So that's my 2c on the matter currently, no real answers, just more questions :)
We're still looking for a solution that fits our needs and a process that is simple enough for our staff to follow...

I look forward to seeing what the App vendors come up with and what MS do with the next version of SharePoint...

Friday, June 03, 2011

And now for something completely different

Spied KiwiRails new loco 9135, being dropped on the rails for the first time.  It's HUGE!

Monday, April 04, 2011

Microsoft Releases AD FS 2.0 Capacity Planning Spreadsheet - hillarity ensues...

OK, so it's been a while since I last blogged, various excuses are the reason...

But today I was tracking new downloads from Microsoft, we use their products a lot here at work and that can be both good and bad.

I'm involved in a project that will be using AD FS 2.0 and SharePoint 2010, so when I saw that MS had released a Capacity Planning Spreadsheet for AD FS 2.0, I ran off and downloaded it. Plugged in our numbers (we have about 450 staff and over 2000 Extranet users) and this is what I saw:
That's right, 0.01 AD FS servers are reccomended (actually, the first time I ran it, I got 0.00!).
Now, we're not a small firm by NZ standards, but we're certainly not an "Enterprise" by USA standards, either.

So why does it come up with such a small number.  Flip the sheet on the spreadsheet and you get MS's server sizes, as tested:
  1. Federation Server
    • Dual Quad Core 2.27GHz (8 cores)
    • 16GB RAM
    • Windows Server 2008 R2, Enterprise Edition
    • Gigabit Network
  2. Federation Proxy Server
    • Quad Core 2.24GHz (4 cores)
    • 4GB RAM
    • Windows Server 2008 R2, Enterprise Edition
    • Gigabit Network
[ouch!]

Which they qualify with:
"Capacity recommendations for AD FS 2.0 servers can vary considerably, depending on the specifications you choose for the hardware and network configuration used in a given environment. As a point of reference, the sizing guidance provided in this content is based on a utilization target of 80% on the computers specified above.



** Memory and disk space requirements for federation servers are modest, and they are not likely to be a driving factor in hardware decisions. The estimates contained in the AD FS capacity planning sizing spreadsheet can be used to estimate the recommended number of federation servers with more moderate memory specifications, such as 4 GB."



Well thank goodness for that! My original calculations had their base-line hardware at 70,000 times more powerful than was necessary to support us!
 
Let it not be said that MS doesn't support/promote hardware vendors :)
 
All joking aside, a more useful (if somewhat more complex) calculator would have given variances in hardware (or VM) specs, to cater for those of us that don't run 40,000 users through ADFS against 6 Claims apps. Yes, that's what it takes to require ONE ADFS server....