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! :)