Tuesday, January 24, 2012

[SP2010/SPF] Required permissions to run command line tools or other tooling that uses the OM

[ISSUE]
With SharePoint in many cases the SharePoint administrators also are the Windows of the SQL server and/or SQL administrator. This means that those persons also having loads of permissions in SQL Server, which means that everything will work without issues. They can do anything they want, using any tool they want.

But what about a situation where you don't have permissions on SQL. You will see actions via the Central Administration site work fine, however certain things like console applications (custom like SharePoint Manage or default applications like stsadm), scripts via PowerShell and other tooling (basically everything that uses the object model) won't work.

This is caused by the fact that SharePoint requires the account to have certain permissions on the databases in order for it to work. When using the Central Administration, this is done via the application pool account which has sufficient permissions. When using the object model outside of the Central Administration, this is done under the account the command is executed with.

[SOLUTION]
Then which permissions are required? Here is where it becomes a little tricky!

PowerShell scripts:
  • When you are running SharePoint 2010 and you are trying to use PowerShell, Microsoft has created a SharePoint internal group called ShellAdmins. By adding your account to this group (Add-SPShellAdmins) your account has sufficient permissions to run PowerShell scripts. Other activities via the object model still don't work.
Other tools:
  • To enable all object model activities, grant the account the following permissions:
    • SharePoint (Depending on the activities you are going to do)
      • Farm Administrator
      • Permissions in the site collection(s), for example site collection administrator or contributor to a list
    • Database:
      • Configuration database: WSS_Content_Application_Pools
      • Content database:
        • Db_datareader
        • Db_datawriter
        • GRANT EXECUTE
        • NOTE: An alternative would be db_owner permissions on the content database, however from SQL administration perspective this might be unwanted/against policy
NOTE: I am assuming that the account already has local administrator permissions on the SharePoint server, how else will you be able to run tools or scripts on the SharePoint server :-)

More info: SharePoint 2010 PowerShell Permissions Explainedhttp://sharepoint.microsoft.com/Blogs/zach/Lists/Posts/Post.aspx?ID=56

Tuesday, January 17, 2012

[SP ALL] Opening a web service is returning a 401.1 "Access Denied" error

[ISSUE]
Yesterday I was asked to assist in troubleshooting an issue with a SharePoint web service. The SharePoint indexing process failed to work properly for just one web application. Some investigation revealed that the indexer was unable to open the sitedata.asmx web service. When trying to open the same web service via IE, I was prompted for credentials however whatever credentials were entered, after three attempts an "Access Denied" page (401.1 error) was shown.

[SOLUTION]
Unfortunately Process Monitor didn't reveal anything and I noticed that the sitedata web service wasn't the only web service that failed. After some troubleshooting I found out that the cause was in the web config:

The "remove verb *.asmx" line was placed after the "add verb *.asmx" line in the httphandler setting, essentially removing the configuration after adding it. For example:
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
....
<remove verb="*" path="*.asmx" />
After correcting this by placing the remove line in front of the add line, all web services started working just fine!
<remove verb="*" path="*.asmx" />
....
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

[EXPLANATION]
Why is this happening? By first placing the remove line, you make sure any declarations that are done in other (global) configuration files are made void. That way you know for sure that no conflicts will occur between two configurations. However after removing the asmx httphandler, you have to declare it again, else SharePoint (or better yet IIS) does not know how to handle the asmx file. The confusing part for this issue is that it will display an authentication prompt to the user, without it actually being an authentication issue.