Understanding SharePoint Application Security and Elevating Privileges

This post was prompted because of a particularly challenging bit of security that I needed to traverse. I needed some way of presenting the status of a Content Deployment Job (configured in Central Administration) in the Web Application that it relates to.

Seems pretty straight forward?
Well, its not, and this article will hopefully explain why.

RunWithElevatedPrivileges and Application Pool Accounts
So the first thing I looked at was using the good old SPSecurity.RunWithElevatedPrivileges method. This is a well known (and on occassion heavily used) practice for getting around security in SharePoint. But does everyone understand exactly what it does?

In a nut-shell, this method simply changes the currently impersonated user from the currently logged in user to an account called "SharePoint\System" (a.k.a. "System Account").

This account doesn't actually exist, and anyone inspecting the WindowsIdentity or SPUser object in any great detail will spot that this account doesn't actually have a valid SID (Security Identifier). This is because it represents a placeholder.. a flag in SharePoint that tells it to impersonate the Application Pool Account instead of the currently logged in user.

The Application Pool Account has full SharePoint permissions to the Web Application (effectively making it a Site Collection Administrator in every single Site Collection).

So what does this actually mean?

SQL Server Permissions
Believe it or not, SQL Server permissions in SharePoint are extremely simple.

Taking the 3 core databases for each SharePoint Farm:

1. Farm Configuration Database
This contains the core configuration information (servers, URLs, accounts) for the entire SharePoint Farm.

The Setup Account has DBOwner permissions.

All application pools accounts are added to a Database Role called WSS_Content_Application_Pools which has severely locked down read privileges.

2. Central Administration Content Database
This is effectively the content database for the Central Administration site. This contains the SPSite / SPWeb / SPList objects that store all of the content related settings (including Content Deployment Jobs).

Again, the Setup Account (which incidentally will be running the Central Administration Application Pool!) has DBOwner permissions.

All application pools accounts are added to a Database Role called WSS_Content_Application_Pools which has severely locked down read privileges.

3. Web Application Content Database
This is the database (or mulitple databases) that contain the Site Collection content for the Web Application.

Here the Application Pool Account (for that specific Web Application) is granted DBOwner permissions. No other accounts are specified!

That is pretty much it. From a security (and "least privileged" perspective) it's a very robust setup. If your application pool is compromised then the application pool account only has SQL permissions to it's own content database.

According to best practice, every Web Application should have it's own application pool account, which again makes sense according to the model above, limiting the surface area for any attack (as one web application being compromised would not have any impact on the other application pools).

This should also make it obvious why you should never make an Application Pool Account a Local or Farm Administrator! You are essentially breaking the security model if you do this (and massively widening the exposed area of your system if that account is ever exposed!).

NTLM authentication and "Double Hop"
The first thing that should scream at you here is that none of the SharePoint user accounts have ANY permissions in SQL. Every single SQL query is executed within a SharePoint Web Application using the Application Pool account!

The reason for this is clear once you understand the limitations of NTLM authentication.

Basically, when you log in to a SharePoint web site, you authenticate with the Web Server (IIS). There is no way for IIS to pass through credentials back to SQL Server because NTLM only supports "single hop" authentication (i.e. from one single machine - the browser - to another machine - the web server). For "double-hop" you need a more robust authentication method such as Kerberos (i.e. from one machine - the browser - hop to another machine - the web server - hop a second time to a third machine - the database server?).

Note - This is why you need Kerberos to use pass-through authentication with 3rd party systems (such as CRM or other LOB systems).

Thats all great .. but what do I care?
Well, this all nails down to where the object is that you are trying to access, what the SQL permissions are on that object.

Lets take the example of accessing a Content Deployment Job.

The first problem you will hit is that your account needs to be a Farm Administrator. We already know that making the Application Pool an admin account is bad for security.

So as an alternative you could use ASP.Net Impersonation to get around the SharePoint API, but as we discussed above, this doesn't solve the NTLM "single-hop" problem (your query is still going to execute in SQL using the Application Pool account, regardless of which account you are impersonating!)

Using .Net Reflector (tsk!) tells us that the Content Deployment Job information is stored in an SPList in the Central Administration Content Database. Using RunWithElevatedPrivileges simply executes using the Application Pool account (which we know from the SQL Permissions above, has very limited permissions).

So lets assume you tried to use Impersonation ... what happens?

Well, you get a nasty "Exception in HRESULT" error message.
Delving in to the SharePoint Diagnostics Logs tells something like " does not have EXECUTE permissions on 'proc_EnumLists' in ".

Basically running that code tries to execute a Stored Procedure in SQL in the Central Admin database which the Application Pool Account doesn't have access to! Your code managed to fool the SharePoint API into thinking you have permissions, but good old SQL Server stops you short (just as it should ... good server!)

So what can I do?
Well, the first thing to note is that you won't always run into this problem.
Many of the Farm level options (including access SSP and User Profile properties) can be gotten around in other ways, but when something like the above happens, your options are limited to 3 potential solutions:

1. Ignore all of the best practice. Make your application pool account an administrator, and spend your days hiding from the network security admins and hoping it doesn't all go wrong.
2. Create a dedicated Web Service, which executes as an admin account. Use this to farm out your "privileged" code, and make sure you lock it down tight as a drum so you can't get to it from outside of the SharePoint farm!
3. Don't do it .. and tell your users that it was a stupid idea in the first place!

Now I admit, Options 1 and 3 probably won't go down too well, and Option 2 is the best option but still has it's issues (running a Web Service as an admin account is still a security risk, if a smaller one than running the entire public facing Application Pool as an admin account!)

Summary
We ended up opting for Option 2, admittedly locking it down so that the URL was never published and it would only accept connections from other servers in the farm (so that end users could never access it).

Hopefully you now have a better grasp of SharePoint Application Security, what that super-method "SPSecurity.RunWithElevatedPrivileges" is actually doing and why it doesn't always work!

Comments a feedback welcome! :)

SharePoint 2010 Visual Studio Extensions (SPVSX) 1.0 Released!

Thats right, the first version of the SPVSX project has been released:

• Visual Studio Gallery
• CodePlex

It's a great project, aiming to provide extensive tools and functionality for Visual Studio 2010, such as quick deployment and a whole host of extra Templates for SharePoint 2010 items.

This project has been so far championed by my friends and colleagues; Wes Hackett, Matt Smith and Glyn Clough.

I'm currently out of the picture for a bit doing a handful of large project for a client in Reading, but hopefully I'll be able to join them and start contributing myself in the new year!

Go check it out, its free to use and we'd welcome any feedback!

I'm in a video showcasing the accessible MOSS system for RNIB

A short video has been released showcasing the RNIB website project, and how we (Content and Code) worked together with RNIB and Telerik to deliver a truly accessible web system in SharePoint.

Well worth a watch!

The best thing, I'm in it! :) (yay, I'm famous!)

Debugging VSIX Projects in Visual Studio 2010

This all came about because I've been working on the SharePoint 2010 Visual Studio 2010 Extensions Codeplex project with Wes Hackett, Matt Smith and Glyn Clough.

The new Visual Studio 2010 SDK (Beta 2) allows you to create VSIX projects (or Visual Studio Extensibility Projects) which are what enable deploying customisations to Visual Studio itself.

The problem comes trying to debug them (i.e. pressing F5). If you don't set the project up properly then Visual Studio 2010 complains that it can't start debugging because the DLL is missing.

The trick is setting the "Debug" options in the VSIX project properties.Full details (including screenshots) can be found on Wes Hackett's blog post.

Saved me some hours there! Cheers Wes!

Is software development like construction?

I often find myself explaining software development in the veign of construction, especially because my job title is "Architect" and therefore most people automatically assume I use CAD to draw housing designs all day.

It's not a perfect model, and not intended to be the explanation for all projects, but this did get me thinking about other parts of the construction business. What about other roles?

Disclaimer - I don't work in construction, so please excuse any faux pas in assumptions about terminology or process :)


------------------------------


Architect [Solutions Architect]
This is my role. The key skills here are the overall "vision" and solid grounding in all disciplines. You don't have to be a master of all trades (although it obviously helps) but the main thing here is being able to see the "bigger picture".

An architect needs to be able to understand what the client wants, and bring together all elements of the requirement to create a design that provides what they want on the budget that is available.

An Architect requires enough technical knowledge to be able to provide options, direction and advice.

e.g. "the design is a 5 bedroom house with 3 bathrooms. I know enough to tell you the walls / fittings of each room, and how much floorspace you need, and what the best fit is between technology and practicality".

SME Engineers [Technical Architects]
These are the subject matter experts. They have years of experience in a specific area (e.g. Plumbing, Electrics, Foundations) and are masters of their trade.

Typically involved in larger projects or for short technical consultation engagements.

e.g. "from the design I recommend you need X strength re-inforced steel beams" ... "you will need N-feet of wiring and a specific type of fuse and junction box"

Note - On smaller projects Technical Architects will often take the same role as Technical Leads.

Foreman [Development Technical Leads]
These people know their trade well enough to know how to do a good job, and are also natural team leaders. They make sure that the labourers do the right work, and to the right standards, according to plan.

They are also capable of recommending solutions to problems "on the job".

e.g. "the current design doesn't work, but I know it will if you move that window 3-feet to the right / change the materials"

Note - On smaller projects Technical Leads will often take the same role as Technical Architects.

Labourers [Developers]
This is where the actual build gets done. You will have the people who do the same jobs on each project. They are good at it and fast too. Because they do this all the time, the chances are they will actually be quicker than the Architects / Engineers and Foreman too!

You will also have in this category the less experienced apprentices (Junior Developers) who are learning on the job.

e.g. Brick Layers / Plumbers / Plasterers / Electricians

Project Managers
These people spend their tmie making sure that scheduling is on track and keeping touch with the client. They make sure that everything happens at the right time and in the right order.

If the cement doesn't turn up, or the wrong type of window frames have been ordered then it's usually the Project Manager's fault.

Consultants
You always have consultants, but the distinction between a consultant and another SME (such as Engineers or Architects) is that Consultants provide consultation for the client.

This is not always the case (consultants have a valued place for internal engagements and aiding the project team too!) but generally the main focus is to help the client to understand the requirements, the solution and to provide that much needed face for clients to ask technical questions (and get a lamens response).

------------------------------

I thought this was an interesting excercise, and certainly helps me to explain what I do to people in a way that they can understand. So few of the great-unwashed masses understand software development, at least this means I don't need to say "I work in IT" and leave it there :)

Of course, some people tend to wear multiple hats and do lots of different jobs ... but it also helps to describe in context (just because you are a "Developer" doesn't mean you can do SQL, C#, CSS and XSLT ... in much the same way you wouldn't expect a builder to be able to do brickwork, plumbing, electrics and plastering!)

How do you present CAPTCHA accessibly?

Following on from my presentation this week on Building an Accessible SharePoint System I had an interesting query from one of the attendees regarding accessible CAPTCHA methods.


For those who haven't come across the term, CAPTCHA refers to the method of challenging users with a query that a human could pass but computers cannot (typically represented by images that display distorted text).

The query related directly to accessibility, and specifically how would you achieve CAPTCHA methods in an accessible way?

CAPTCHA themselves have the following statement on their website:

"CAPTCHAs must be accessible. CAPTCHAs based solely on reading text — or other visual-perception tasks — prevent visually impaired users from accessing the protected resource. Such CAPTCHAs may make a site incompatible with Section 508 in the United States. Any implementation of a CAPTCHA should allow blind users to get around the barrier, for example, by permitting users to opt for an audio or sound CAPTCHA"

This of course does not account for users who are both visually impaired and audible impaired (for example, a user who was both blind and deaf).

The solution to this could include a number of workarounds, including mathematical questions ("what is one plus two?") or more "natural language" queries ("What is the colour of the sky on a clear day?") but these could also present other problems.

Firstly generating enough of these prompts to disrupt predicting the responses to them would be problematic. You then have to consider cultural and language barriers, as well as other impairments such as textual or numerical dyslexia.
It's certainly a difficult topic and one that is a challenge to get right without either leaving your site in an inaccessible state or leaving it open to programmatic mis-use.

To find out more about CAPTCHA you can visit the CAPTCHA Website or the CAPTCHA Wikipedia article.

Building an Accessible SharePoint System - Slide Decks, Source Code and Downloads (SUGUK London - November 25th)

First of all a big thank you to everyone who attended the session, and many thanks to Chris O'Brien for his presentation on ECM in SharePoint 2010 and also to Matt Taylor for pulling the strings behind the scenes and getting it organised!

You can find links to all of the Slide Decks and Source Code that was used in the Building Accessible SharePoint Systems session below.

There is loads of material, with links to all of the tools and websites I mentioned including the Disability Discrimination Act, the WCAG 2.0 and WAI ARIA guidelines, the new online SharePoint 2010 SDK and the ASP.Net 4.0 Whitepaper ... plus links to all of the tools that were mentioned.

The slides also include notes on each of the topics and the demo notes refer to the source files that were used in the demo!

In the mean time if anyone wants to contact me with any questions feel free to use the medium of your choice:

Email: martin.hatch@contentandcode.com
Twitter: @MartinHatch

Cheers, thanks for coming and hope to see you all again soon!

Files
Let me know if you have any trouble accessing them.

• All Files - Building Accessible SharePoint Systems

Otherwise you can get the individual files below:
[Update - some of the links were broken before - fixed now!]

• PowerPoint Slides (2007-2010 format) (.pptx)
• PowerPoint Slides (97-2003 format) (.ppt)
• Sample Code - SUGUK Visual Studio 2010 Solution (.zip)
• Sample Code - Control Adapters (.zip)

Thanks again!

Why I chose Blogger?

I've already had this question asked to me, and my blog only moved yesterday! Why did I move from the "Microsoft" Live Spaces to the "Google" Blogger / Blogspot?

Unfortunately it was depressingly simple. I got fed up with the lack of features on Live Spaces. My more popular posts were being flooded with spam comments, I had no way of changing the URL (even within the spaces namespace, let alone use my own custom one!) and I was quite limited in terms of available templates.

The main features I like with Blogger are therefore:

• Moderation of comments and support for blocking "bots" from posting comments
• Ability to control comments on a post by post basis!
• Ability to control URL
• Support for custom domain names
• Complete control over HTML template / colours / styles
• Multiple {Tags | Labels | Categories} per post (why does Live Spaces only allow 1??)
• Better post navigation (tag clouds and tree-view for post archive)
• Improved analytics (or .. more accurately Google Analytics, which I probably could have used on Live Spaces but the built-in statistics for Live Spaces are extremely poor). 
• Improved Text Editor for posting new posts (better paragraph / styling support, ability to post an older publishing date and AutoSave is awesome!)

In the end it seemed like a no brainer. I've been putting it off mainly because I didn't want to have to go around updating all my links (plus my Google and Bing rankings will probably takes ages to catch up again).

But now I've taken the plunge I'm much happier, just got to put some elbow grease into getting it ship-shape in terms of styling and links (not to mention plenty of new posts too!)

New Blog Launched!

This marks the birth of my new blog; www.martinhatch.com :)

I’ve still got some styling work to do (so you can expect that to change yet!) but otherwise have my brand new shiny blog.

It’s powered by BlogSpot/Blogger (a.k.a. Google) and if you were wondering why, it’s because I get more finite control over the layout, I get better reporting (Google Analytics) and I can have my own domain name :)

So come, enjoy and be merry!

SharePoint 2010 and Office 2010 Beta released!

Yep, Microsoft have got slightly ahead of expectations and the official public beta release of SharePoint 2010 and Office 2010 is now out (although you need a TechNet or MSDN subscription at the moment!).

You can find the download information as well as more details on the relevant websites:

• TechNet
• MSDN

For those of you who have access to the Technical Preview of SharePoint 2010 you can expect to see quite a few changes and improvements in the beta version. For those of you who haven't seen either, you're in for a treat!

How to add a Lookup Field to your List dynamically and programmatically

This is an old old problem, and for Content Types it has been easily realised that this can be solved by using a Feature Receiver and creating the lookup through code (to set the lookup list GUID .. something that you cannot do through CAML).


The biggest problem with Lookup fields is with List Definitions. You can still add the Lookup Field to your schema.xml, but it won't do anything without knowing which list it is supposed to lookup to so you typically end up with a field that doesn't work.

Now you can get around this IF you know the URL or the ID of the field you want to lookup to, but most list definitions that you release can be created anywhere so this is very rarely possible. Now .. in my particular example, we wanted the Lookup to lookup to itself! In this case, there really isn't any URL or method through the schema.xml that we can use for this, and managed code is the only route ... the main problem there is that there is no "ListAdded" event that you can trap when your list gets created ... but then I had a spark of inspiration!

The solution was surprisingly simple and came in a bit of a eureka moment... SPListEventReceiver "OnFieldAdding" event.

You can bind in event receivers using a variety of methods (not covered here) but for my example I bound it into the schema.xml as part of my list definition (so this code only ever executes when the list is created).

The "OnFieldAdding" event then executes every time a new field is added to the list, and this includes fields provisioned from the schema.xml! All I then needed to do was identify the field (which I could do easily because I knew the Field ID) and then I could use managed code to manipulate the SPField object and fill in all the blanks that I couldn't do from the schema.xml!

I really like this method, as it has opened the door to a way of executing code on a list when it is created. Now I admit it's not exactly bullet proof because it potentially executes the code a LOT of times (although ideally you would remove the event handler once you've finished executing what you needed to), but it was a really nice "other option" .. and certainly one I hadn't considered before!

NewSID is dead.. duplicate machine SID no longer a problem?

Well, this one was gob-smacking! A colleague of mine (Tristan Watkins) pointed me at this article from Mark Russinovich, the developer of the "NewSID" tool that so many people use for creating a new machine SID for a machine (typically after a re-image or copying a virtual machine).

Well, it seems we don't need to bother anymore and never really did, at least in the vast majority of cases!

"It’s a little surprising that the SID duplication issue has gone unquestioned for so long, but everyone has assumed that someone else knew exactly why it was a problem. To my chagrin, NewSID has never really done anything useful and there’s no reason to miss it now that it’s retired."

To read more, go check out Mark's blog post here: http://blogs.technet.com/markrussinovich/archive/2009/11/03/3291024.aspx

Update - A good follow up summary has also been written by one of my colleagues, Tristan Watkins.

New videos for SharePoint 2010

A load of new videos has popped up on the Microsoft SharePoint 2010 website.

 

These are broken up into various topics, and well worth a look!

• IT Pros
• Developers
• Sites
• Content
• SharePoint Foundation
• Enterprise Search
• Composites
• Insights (BI)
• Communities
• Opportunities for Partners

You can find all the videos here:

http://sharepoint2010.microsoft.com/Pages/videos.aspx

Presenting at SUGUK London 25th November

Yep, I'm taking the plunge and finally presenting publicly :)

 

I'm going to take the stand to talk about Developing an Accessible SharePoint System based on our experiences in designing and building the Intranet and Website for the Royal National Institute for Blind People (RNIB).

 

I'll be going into technical detail about development techniques for customising the front-end and back-end interface of SharePoint, as well as some of the more rounded issues around accessibility (such as the age old "accessibility versus compliance" discussion).

 

I'll also hopefully get the opportunity to show off our new SAS (SharePoint Accessibility Solution) framework and show the audience an example of a WCAG 2.0 AAA system running on MOSS 2007.

 

Please feel free to come along (assuming you are in the London area on November 25th, arrive 6:00pm for a 6:30pm start!). It's free to attend, and Microsoft usually do a good show by providing free pizza, tea and coffee! :)

 

You can sign up at the SUGUK forum thread here:

http://suguk.org/forums/1/21214/ShowThread.aspx

 

Look forward to seeing you there!

Load Testing SharePoint 2010 with Visual Studio Team Test

 

So exactly what do we mean by "load testing" when it comes to SharePoint 2010? There are lots of methods that people tend to point towards, and I've heard "hits/visits per day" and "throughput" bandied about, but at the end of the day it comes down to 2 things:

 

1. Requests Per Second

The requests per second literally means how many requests for information each server is capable of responding to per second. Each page may consist of dozens of artifacts, and for each artifact the browser needs to make a "request", therefore the more of these  "requests" it can serve the better.

 

1. Server Response Time.

The response time represents any processing on the server side (or TTLB - Time to Last Byte). This doesn't factor in network latency or bandwidth though!

 

So the first thing you should think about is what can influence those metrics? And you end up with 5 different elements of your SharePoint 2010 farm:

• WFE
• Storage
• Network
• App Servers
• SQL

 

This, as I'm sure you can imagine, can involve a LOT of testing. Simply testing the WFE on their own is going to be struggle for your average developer, and if you don't have any industry testing experience you are going to have a hard time, but this is where the new SharePoint 2010 wave continues to make it's presence felt. ..

 

SharePoint 2010 Load Testing Toolkit

This is a new set of tools being released with the SharePoint 2010 Administration Toolkit and represents the easiest possible way of load testing your SharePoint environment. The main objective here is to:

 

• Standardise and simplify the cost of load testing.
• Simulate common SharePoint operations
• Be used as reference to create other custom tests (for custom code, for example!)

 

The whole thing relies on the IIS analysis logs. These logs give pointers on where users are going, what kinds of requests they are doing (GET / PUT) as well as the types of files they are typically accessing (ASPX / CSS / JS / JPEG / DOCX / etc...)

 

The Load Testing Toolkit will analyse your IIS logs and automatically generate a set of loads tests to appropriately match your environment, producing automated scripts that can be run in Visual Studio (either Team System or Team Test Edition).

 

How hard can it be?

It is really quite simple (well, according to the ridiculously simple explanation at the SharePoint 2009 conference!). You literally point the tool at your IIS logs, and it spits out an entire suite of tests, for WFE, SQL, Storage, etc .. Including all the metrics you would want (from CPU, RAM, Network, Disk I/O and even SQL , ASP.Net and .Net Framework specific performance counters).

 

Then you just run it and analyse the results!

 

Analyse That!

The analysis couldn't be simpler. With "Requests Per Second" and "Response Times" two of the metrics generated by the Visual Studio test reports, you really can't go far wrong.

 

If you do find a problem, then you can delve into the new SharePoint 2010 "Usage Database" (which now runs on SQL Server) in order to identify exactly what was causing your dip in performance (say when someone deletes a large list?).

 

Tips and Tricks

There are a few gotchas, one thing is to be careful of "Validation Rules" in Visual Studio. Typically it will be happy with pages that return "200" codes. This of course includes Error and Access Denied pages (which SharePoint will handle, and returns a perfectly valid page (hence the 200 code!)).

 

It is also recommended that you let your test "Warm up" for around an hour before you start taking the results seriously.  This allows all of the operations, timers and back-end mechanics of SharePoint to properly settle down, and means you are getting a realistic experience of what the environment will react like once it is bedded into it's production environment.

 

Finally, the SharePoint Usage Logging Database is a great location to grab information out of, so why not leverage other great aspects of the Office 2010 family. You could pull through the Usage DB information into Excel 2010 (perhaps using PowerPivot?) so that you can spin out charts and pivot tables to easily drill down into your data.

 

Typically load testing tells you WHEN bottlenecks are occurring, but the Usage Database can tell you WHAT is causing the bottlenecks!

SharePoint 2010: Architecture Guidance - things everyone should know!

Well, the final day of the conference came and with it some of the most useful sessions (from my perspective). One of which was the "Architecture Guidance for SharePoint 2010". This hopefully distils some of that information. It's not a be all and end all, but hopefully points you in the right direction so that you can focus your research a little better!

 

[UPDATED: 27/10/2009 16:09]

 

UI Design

• Entire interface in SharePoint 2010 to be W3C XHTML compliant
• SharePoint 2010 "more accessible mode" to be WCAG 2.0 AA compliant
• New ribbon interface replaces toolbars and menus (and considerations for old "CustomAction" commands which may no longer work!)

• Wiki content allows web parts to be dropped in (removing over-reliance on web part zones)

 

Lists

There are a whole load of new List capabilities (in addition to the "External List" that BSC brings to the plate!).

• Lookup to Multiple

This means that when you create a new lookup column, you can now pull down additional fields from the lookup list item and use them for filtering.

• CAML support for Joins!

You can now perform "JOIN" operations in your CAML queries for linking lists together.

• Enforced List Relationships

You can now enforce specific relationships for lookup columns with two options:

• Restrict Delete - cannot delete parent if child items exist.
• Cascade Delete - If you delete the parent, all child items are automatically deleted (recycle bin aware with "restore" options!)

• Store-level enforcement

This is code level "required fields", so now you can enforce the requirements even through code !

• Unique Fields

Specify a unique field, so that no two values can match (e.g. Email addresses in contacts list)

• Compound Indices

If you want to query by 2 fields, you can now index both at once as a compound index.

• <In> clause for reverse lookups

This allows a CAML query to do a reverse lookup to get all child items that are associated with the parent!

• Formula based validation

e.g. Don't allow Field2 to be lower than Field1.

 

Workflows

• Out of the box SharePoint 2010 workflows can now be extended in SharePoint Designer 2010.
• SharePoint Designer 2010 can be used to create "re-usable" workflows
• Site Workflows - to manage processes across an entire site.
• You can now import a SharePoint Designer 2010 workflow into Visual Studio 2010!
• Import/Export workflow using Visio 2010 for visual workflow modelling.

 

Content & Document Management

• "Document Sets" allow you to treat a group of documents as a single item (with 1 version history, group executed workflow and policy, and a "download as zip" option).
• Managed Metadata Service  allows cross-farm Content Type management and a pre-defined enterprise taxonomy structure! This is a killer-app, bringing true enterprise content management to SharePoint 2010.
• Enterprise Wiki's allow more rapid "in edit" content, as well as Web Parts deployed directly into the rich text editor (no more web part zones?).
• Spelling check and broken link check when you "check-in" WCM pages.

 

Event Handlers

Three new event handlers added (at last!!)

• WebAdded - Fired every time a child site is created in the web.
• ListAdded - Fired every time a list is created in the web.
• Feature Upgrading  - Fired when a feature has it's "upgrade" method called (more on this in a future blog post).

 

Security

• Editing of ASPX pages now required "Designer" permissions (instead of contribute).
• XSS (Cross Site Scripting) protection for pages and web parts.
• HTML pages will now "force download" by default. This stops people from uploading HTML files with malicious scripts, so if you click on an HTML file in a document library you will get a download dialog instead of the file opening in the browser!
• There are still no field level permissions (it was estimated that this would add a 30% overhead to performance! Maybe in a future release)

 

BI and Connectivity

• New Business Connectivity Services (BCS) allows no-code connections of databases and LOB systems to content types and lists with two-way synchronisation of data  and full CRUD support.
• BCS interactivity from within Office clients, allowing LOB system data to be edited directly from desktop applications (such as Outlook and Word).
• PowerPivot for Excel allows upwards of 100 million rows into an excel workbook with phenominal performance.

 

Office Application Support

• New web level services for applications (Excel / Visio with JavaScript events!)
• SharePoint Workspace to replace "Groove" for offline file support and editing.
• Office Web Applications to allow for direct opening and editing of documents from within the browser!
• InfoPath 2010 can now be used to edit the List forms out of the box!

 

Databases

• Still a 100GB "limit" for content databases.
• Still cannot have site collections spanning multiple databases.
• New support for "Failover" databases, SharePoint 2010 is now SQL mirror aware!
• All "Service Applications" have their own SQL database, along with many other new databases (e.g. Feed Activity, Social Data, Usage Logs).
• New "read only content databases" open the door for simple content deployment (utilising SQL log shipping or database replication).

 

Content Deployment

• All execution now in Timer Jobs.
• Performance (and memory usage) improved.
• Export routine now creates database snapshot to improve data integrity!

 

Sandboxed Solutions

• Ability to upload WSPs directly into the content database to execute in minimal permissions using "virtual files" (no impact on the file system!)
• Resource throttling, code performance checking and "bad routine" blocking
• Provides new best practice for code development and deployment!

 

Search

• New FAST search with thumbnail views (and navigation!) for office documents
• Improved relevancy and non-query searching
• 2 new search products (FAST based)
• New refinement panel for advanced sorting and filtering "on the fly"
• Multi-lingual support with over 80 languages built-in.

 

Social Networking

• New My Sites structure
• Activity Feeds to provide updates on user activity with an extensible architecture!
• "Social Feedback" functions akin to Delicious and Digg allowing tagging of any URL based content, and subsequent discussions around items that have been "tagged".
• Ratings mechanism distributed throughout the product.

 

I'm sure there are many other things, so please let me know if there's anything else you think should "make the grade" and I'll see if I can add it in :)

100 million rows in Excel? PowerPivot.. a first look from the SharePoint Conference 2009

 

"Project Gemini" has been batted around for a while now but it was unveiled at the conference that it is now known as SQL PowerPivot for Excel 2010 and SQL PowerPivot for SharePoint 2010.

 

What does it do?

In short, PowerPivot allows you to pull data into an Excel workbook from almost any data source. This can be SQL databases, Analysis Services Cubes, or any ODBC data source.

 

This is all handled via the import wizard, which contains a nice interface to setup which tables and filters you want to apply (the wizard then generates the necessary query).

 

You then have access to a whole raft of Excel Formulas (and a bunch of new aggregation and time intelligence formulas) that you can use to add new columns to the data. You can even bring in your own Excel worksheets as tables of data that can be linked up to the other data sources (say to provide foreign key tables where the lookups are stored and managed in Excel!)

 

Ok ... So what's so special about this?

Well, the main thing that is impressive is that they demonstrated an example system running with over 100,000,000 rows of data! Now remember that this is running from Microsoft Excel!

 

You could then add your own extension columns (using simple Excel style formulas) and the whole  data set refreshes in seconds.

 

So the performance is good huh?

The performance is quite simply jaw-dropping.

 

One of the demo sessions the presenter imported over 3.5 million rows of data from a SQL Analysis Services cube and it imported in just under 2 minutes.

 

He then created a pivot table of the total sales data, split into rows by country.

He then added "slices" so that you can flick between sales figures for different years or product categories.

 

With all of these calculations the pivot table was refreshing it's data in under 2 seconds!

 

Not even SQL Reporting Services can execute that fast, and this is in EXCEL so the user has full control over the pivots and can filter / query / change the results as much as they like.

 

How does it actually work then?

The main thing that PowerPivot does is that the database columns are separated out and compressed individually. Foreign key values can then be separately indexed and this makes the compression levels fantastic.

 

Take an example of a foreign currency field for Europe. Regardless of how many rows of data you have that column is only ever going to contain a small number of different values (£, €, etc). You could have one thousand rows or one billion rows and it would still have the same variation in the values. This makes it extremely compressible so you can get extremely large data sets down to a very small footprint.

 

When you then query the data set it loads those columns into memory for execution, so you end up with a column based querying model running directly from memory (which is the reason it is so incredibly extremely fast).

 

Now before you start wondering if this will only work on beefy 64-bit workstations with RAM in double figures I have been assured by the presenter that this works fine on a 2GB netbook! Although he was running the demo on a quad core laptop (presumably with about 8GB of RAM).

 

What about SharePoint 2010 then?

Well, SharePoint 2010 has support for Excel Services, and with SQL PowerPivot for SharePoint 2010 you can publish Excel Workbooks containing PowerPivot data sets directly to SharePoint!

 

This allows you the flexibility to share and present your workbooks with colleagues and other users of the SharePoint platform directly from the browser!

 

Even better than this, if you save an Excel Workbook containing PowerPivot data to a document library, then you can import that into another PowerPivot workbook!

This means that your PowerPivot workbook has actually become a data source in it's own right, paving the way for true BI applications being built with this technology!

PerformancePoint Services 2010 new features

Some very nice new features for PerformancePoint Services 2010 for creating SharePoint 2010 dashboards.

 

The KPI web parts and filters now execute Asynchronously, so you can expect your web parts to refresh and update without page refreshes (hurrah!)

 

There was also some very nice cool stuff around Time Intelligent Filtering. If you are using SQL Analysis Services then you can use small formula functions like "month" or "year" and it will automatically calculate the query that needs to be called.

 

So for an example, you can create queries to pull through data for:

• Sales this month ("month")
• Sales last month ("month-1")
• Sales this month last year ("(year-1).month")
• Sales last month, last year ("(year-1).month-1")

 

All without any code and without going into SQL, very impressive.

 

There is also improved SharePoint connection settings so that you can associate SharePoint list data with your OLAP based KPIs. This allows you to use SharePoint lists to configure your scorecard information. But better than that, you can also configure your web parts to allow in-place editing of that scorecard information, so now the editing of the scorecard data can take place for within the dashboard itself!

 

Probably the best feature (and certainly got the most applause from the audience at SharePoint Conference 2009) is single-click deployment to SharePoint from the Dashboard Designer application.

 

You can now setup SharePoint connections to configure your dashboards, and from a single click of the button it will compile and deploy all of your dashboards into your SharePoint environment!

Social Feedback and Activity in SharePoint 2010 - Ratings, Tags and Notes

The social functionality in SharePoint 2010 has been massively improved from the previous versions of SharePoint, and one of the areas is around the concept of Social Feedback.

 

Question: How many times have you found a useful link somewhere on the internet, but had no way to usefull record that and get feedback from your colleagues?

 

Well, SharePoint 2010 social feedback can help with this, you can now "tag" any source on the internet (or intranet) which has a URL. This is stored in your "tags" section on your My Site, and also appears in your "Activity Feed" (which is one of the new areas in the SharePoint 2010 My Site).

 

Other users can also post "notes" relating to your tag, which effectively creates a discussion board around the "tagging" activity, allowing conversations around something that has been tagged.

 

Now, one of the key points is Security Trimming. Lets take this example: what happens if you Tag a document that someone else doesn't have access to?

 

The good news is that social tagging uses the Search Index to provide security trimming on content that is stored in SharePoint.

 

This provides the capability for senior managers to tag confidential documents (and hold conversations about that using notes) but those tags (and notes) are not visible to anyone who doesn't have read-access to the document!

 

On top of this is included a Ratings feature, where you can rate content within SharePoint lists (finally, the death of third party "rate my content" web parts).

 

This means that SharePoint 2010 now has similar social feedback functionality as other products like Digg or Delicious, in that you can tag and rate content, and other people can interact with that "tag" creating a discussion.

 

Architecture

All of the Social Feedback information in SharePoint 2010 is stored in a separate "Social Database". This sits alongside the Profile Database.

 

There are then "Gatherers" (Timer Jobs) which will collect all of the changes to both the Social Database and the Profile Database and this is stored in another database for Activity Feeds (the Activity Feed Database) with foreign key pointers back to the Profile Database (so you know who's activity it is).

 

The performance is impressive, aiming for 2000 requests per second, and in terms of storage they are looking to support over 600,000,000 rows of data! They claim that this is sufficient for activity (including social feedback) for 400,000 users over 5 years!

 

Extensibility

You can also hook into this process yourself. You can build your own "Gatherer" jobs to collect information from any data source that you like.

 

A good example is a CRM database, so that you can show activity in CRM in the My Site Activity Feed, showing when people schedule meetings or achieve sales activites.

 

 

All in all the Social Feedback and Activity in SharePoint 2010 is shaping up very nicely. The performance is something that they are still working on, so don't expect amazing results in the Beta version, but Microsoft are already using this for all of their employees so the dogfooding will make sure that this is given all the attention that it needs!

Securing SharePoint 2010 Web Servers

 

This was one of the best topics I've seen so far at the conference. The amount of concrete information was impressive (and to be honest a bit too much to post here) but there was some great information on how to harden your Web Servers.

 

SharePoint 2010 Security Features

There are a whole load of new features and changes to the SharePoint 2010 product for security.

 

• ASPX Pages are gone for contributors. You can no longer upload ASPX pages into document libraries unless you have "Designer" permissions! The main reason this becomes possible is because the new Wiki Pages are so much more extensible than they were.
• Anonymous Users Lockdown feature  now works for Web Services and WSS (SharePoint Foundation 2010)!
• PowerShell Access - you can now delegate remote scripting rights through PowerShell, so you no longer need the Setup account to perform PowerShell commands. This can be delegated to farm administrators!
• XSS (Cross Site Scripting) protection is now in place through the headers (although you can turn it off). This can be even be locked down to individual web part properties (through development)!
• Application Page settings can now be controlled more granularly, so that you can set the master pages used and even swap out individual pages (such as the Error Page). This makes lock downs and branding of these far easier, without breaking the supported state of your environment, and without extensive development!

 

There was then whole load of recommendations for hardening your environments. It's a bit of a list so apologies for that, but a lot of information to get through:

 

Hardening your Web Application

• Place your web application directories on a non-system volume. If you have any issues with logging or file access then the I/O operations (or even disk space requirements) could damage the Operating  System!
• Change the IIS header. By default this will include the SharePoint version number (which means any attacker knows which service packs and critical patches you have installed!). Removing this reduces your public footprint

 

Hardening your Web Servers

Windows Server 2008 takes care of most of the previous recommendations for hardening automatically, but there are still some things that you should do:

 

• Restrict remote administration of the Registry (no-brainer, but a lot of people forget to do this)
• Rename Administrator account
• Delete / Disable unused accounts (again, make sure your dev and test accounts don't hang around on the web front ends)
• Use the IUSR instead of IUSR_<serverName>

The IUSR account is a "built in" account so therefore it doesn't have a password and no-one can login using that account. This makes it much more secure than the Server specific IUSR account that gets created!

 

Hardening SQL 

There's a whole load about this on the internet. The only one to mention here is change the port number! There are a lot of viruses and malware that will specifically target this port.

 

Hardening your Network

Again, none of this is SharePoint specific, but goes a long way to making sure that your network in general is secure (which is of course best practice for SharePoint systems).

 

Routers:

• Block unused protocols and ports (see ports required, below)
• Screen Traffic (e.g. ICMP)
• Intrusion Detection should be in place

 

Firewall

• Use packet filtering policies
• Log your permitted / denied traffic, and make sure those logs are checked (using alerts)
• Make sure perimeter networks are firewall secured, effectively providing end to end firewall security.

 

Switches

• Disable any unused services in the switch
• Do not overly trust VLANS. Just because your traffic is isolated to a VLAN doesn't mean you shouldn't still block off the relevant ports and protocols.

 

Ports Required for Web Servers

Note - When SharePoint is installed the communication ports are automatically opened on the Windows Firewall!

 

External:

• Http 80 / TCP
• HTTPS 443 / TCP
• SMTP 25 / TCP

 

Internal*:

• HTTP 32843/TCP
• HTTPS 32844 / TCP
• TCP 32845 / TCP
• SMB 445 /TCP|UDP

 

* note that "internal" means Web Application --> Service consumtion over WCF. It does not include SQL or inter "server" communications.

Topology Changes for SharePoint 2010 Logical Architecture

The SharePoint 2010 topology has been massively updated, allowing for greater flexibility and scalability than ever before.

 

The "Shared Service Provider" is dead, it doesn't exist in SharePoint 2010 and instead is replaced with new "Shared Service Applications". This allows core services to have their own security settings, run in their own applications and on their own databases.

 

There is even support for "cross farm" Service Applications (such as Search, User Profiles and the Managed Metadata Service) to allow distributed farm architecture like never before. Now in SharePoint 2010 you can scale up into multiple farm environments, allowing you to take advantage of more geo-distribution flexibility, and greater performance and availability from having dedicated farm hardware for important applications.

 

For the larger enterprise environments you have the benefit that different farms provide the opportunity to service different SLA requirements, and the Many - Many relationship for Web Applications to Shared Service Applications means that core enterprise level services can be shared globally, but smaller core specific services can be hosted multiple times, closer to the client environments, to service  those farms that need them.

 

If you need greater security boundaries and better utilisation of resources you can spin up department specific farms for business critical organisational boundaries (such as HR and Finance) each with their own independent services or shared services (such as an HR specific BCS, or Finance and HR sharing their own  set of Managed Metadata for payroll and accounting data, a service that is not provided to the more generalised collaboration and publishing environments).

 

All of this comes together with other administrative changes (such as the SQL failover awareness and Managed Accounts) to make SharePoint 2010 a truly industry leading platform for web applications and technology. I cannot think of any other product on the market that offers this level of flexibility across so many different technology streams.

Cross Site Scripting (XSS) protection for SharePoint 2010 Web Parts

 

Some of the new features in SharePoint 2010 offer some great new opportunities for malicious scripts to be manipulated in your system. The new SharePoint 2010 Client Object Model is a great case in point.

 

Let's take the example where a contributor adds some Client Object Model scripts through exposed web Part properties to change list data that they don't have access to. As soon as someone with admin privileges visits the page that Client OM kicks off and you've got yourself malicious script executing!

 

Well, step in the new XSS protection. The WebPartPages class now includes a new attribute that you can add to your Web Part Properties called "RequiresDesignerPermissionAttribute". There is also a new SafeControl attribute called "SafeAgainstScript".

 

These allow you to protect your assemblies and properties against contributors. The main problem is that none of your MOSS 2007 web part properties will be accessible to contributors without these added!

 

This obviously creates quite an overhead in terms of code use, but it really is required to make sure that your web parts are running in an appropriately secure state.

Web Parts on SharePoint 2010 Wiki Pages.. marriage made in heaven

 

This is something that really confused me the first time I did it (by accident actually), but you can indeed drop web parts directly into Wiki Content.

 

Let me just repeat that in case you missed it:

You can drop web parts directly into the HTML of Wiki content

 

There is no concept here of web part zones, or ordering .. You can literally seamlessly have them embedded in the HTML!

 

This of course means great things for allowing dynamic page content to truly flow, with dynamic web part content sitting seamlessly side-by-side with your Wiki content (hopefully this also means the death of over-complicated Page Layouts to accommodate hundreds of Web Part zones .. And also hopefully the death of the Content Editor web Part!)

 

To add the web parts is really easy, it uses the new SharePoint 2001 "Ribbon" interface, and you just literally just insert web parts the same way you would with tables, images, or any other type of content.

 

It actually achieves this by using a hidden web part zone (called the "WP Zone") which the Wiki uses to store the web parts (and retrieve the web part properties.)

 

Now, let me just hit you with another big one: Web Parts now support content versioning.

 

Again: Web Parts will now roll-back along with page versioning! So when you restore a version of a page, the Web Part properties in that version will also work!

You don't need extra code for that, it "just works" (very very cool!)

 

How can I do this programmatically?

There are 2 different methods you can tackle for this:

 

The "WikiEditPage" class includes a method called "InsertWebPartIntoWikiPage". This is a ron-seal method (it does what is says on the tin!).

 

Alternatively you can also "roll your own".  Web Parts are identified in the Wiki HTML through a DIV placeholder with some specific GUID references. So you can hand-crank this HTML content and drop it into your wiki page.

Improving SharePoint 2010 Administration

 

This is a big area for SharePoint 2010. Far too often in the MOSS 2007 interface was administration settings a little bit neglected (and lets face it, the public facing sections of our systems always get more attention) but in SharePoint 2010 there are a number of massive improvements.

 

Logging & Alerts

One of the key areas for reporting is centralising the reporting and alerting interface. In this the main logging and event data sources (ULS logs, Windows Events, Performance counters for SQL , .Net Framework and  hardware resources) are going to get pulled together into a single SQL Database. The best bit is that this database will have a published open schema!

 

This allows the database to be queried and reported upon, and is expected to include full SCOM integration!

 

Managed Accounts

This is a HUGE feature for managing service accounts in SharePoint 2010. You can create specified accounts that can be used by farm administrators when setting up SharePoint services (such as Search and Timers) as well as creating new applications (such as Web Applications and the new Managed Service Applications which replace the SSP).

 

The upshot of this is that you don't have to hand out domain accounts just so that someone can provision a new web application.

 

But that's not it. Managed accounts can reset the password (presumably into some rediculously long strong password) and manage that password through SharePoint 2010. If you have an AD security policy for password expiry, then Managed Accounts can automatically reset the password for you, so you never have to worry about password expiry hosing some key services in your SharePoint 2010 farm!

 

Health and Monitoring

Central Admin is set to gain a whole raft of performance monitoring reports.

 

On such example is the "slowest pages" report, literally showing you the average times for the slowest pages to render.

 

You can then use the new features of the "Developer Dashboard" to show you key performance information about that page

• Which webserver was used
• What SQL queries were run
• What web parts loaded (and how long they took)
• The call-stack of code method calls
• SPRequest allocations

 

This allows detailed analysis and rapid debugging of problems that otherwise were seen by many as a black art!

 

Failover

There is now an option for a Failover Database Server when creating Web Applications and Managed Service Applications. This effectively allows SharePoint 2010 will automatically be aware of database mirroring. If the primary SQL server dies, SharePoint will automatically re-connect to the failover database.

 

Of course, you still have to setup mirroring manually, but this is a huge boon to creating high availability systems on SharePoint 2010.

 

Restoring Data

You can now recover a list from an unattached content database. From SharePoint 2010 Central Administration you can connect directly to any content database (regardless of whether it is attached or not), and you can browse the content structure from Central Admin.

 

You can navigate the structure and export any Site or List and it will download the package straight to your computer. This can also pull in versioning and security settings!

 

There is tonnes more content to cover, and loads more sessions .. But hopefully this gives you an indication of some of the improvements that are being made in SharePoint 2010!