a misleading "Access Denied" error when joining a existing sharepoint farm

Lately I have tried to join a new Report Server to an existing sharepoint 2007 farm as it is required for SSRS in integrated mode, but the configuration wizard fails at the step 2, i,e, connecting to configuration database, and I got the following error from window event log:

Failed to connect to the configuration database.
An exception of type System.Security.SecurityException was thrown. Additional exception information: Access denied.
System.Security.SecurityException: Access denied.
at Microsoft.SharePoint.Administration.SPPersistedObject.Update()
at Microsoft.SharePoint.Administration.SPServer.Update()
at Microsoft.SharePoint.Administration.SPFarm.Join()

As the error message indicates, all my thoughts are on the database connection, for that, I first check if the sql server is ping-able, and then the permission on config database, and then whether window firewall is open etc... everything is fine, I can even create a new farm with the same sql server, but I could not join!

At last, i found i have it is caused by different sharepoint versions: one with April 2010 CU and the other is just SP2. This is nothing to do with database. After installing the CU, the error disappears, and join is successful. I hope this can save me or someone else several frustrating hours next time.

Create a persistent alias in PowerShell

Often times, I need to query sharepoint cmdlets as I am starting to use Powershell, normally i wrote something like this:

 get-command where {$_.name -like "get-sp*"}

It works, but would it be nice to have this defined as an alias as I need to run it very often. Sure you can do this by using set-alias cmdlet, but it won;t survive another session, in other words, after you close powershell, it is gone.

the workaround is to define a function or alias in profile:

•  Open Powershell ISE from Programs>Accessories>PowerShell folder
•  Run the following code from the immediate window in ISE to create a new profile for all users

if (!(test-path $profile.AllUsersAllHosts)) {new-item -type file -path $profile.AllUsersAllHosts-force}

•  Run the following code to edit the new profile

           psEdit $profile.AllUsersAllHosts

•  When profile1.ps1 opens, add the following code:

           function getspcmd { get-command where {$_.name -like "get-sp*"}}

• save and exit ISE
• relaunch ISE and type "getspcmd"

Logon restriction on service accounts

Lately I ran into the same problem several times, so I decide to blog this to save someone else a couple, if not more, frustrating hours.

The problem is, during sharepoint or sql server installation we need to enter several service account and their passwords, and even though I use those passwords many times without any problem, I keep getting an error saying "invalid password or username", or sql service (or any other window service) can't start with the password  I enter. Someone must change the password without letting anyone else know?!

Not really, generally for those service accounts, network or server guys like to impose a log on restriction:

make sure your new server is in the list! and check the spell (no typo. windows don't check that for you).

where is SSRS 2008 web service?

When you configure SSRS's web service URL in RS Configuration Manager:

Have you ever wondered where this web service is or tried to go to IIS and look for a virutal directory called ResportServer? I did, but could not find it of course. As the RS server is also a SharePoint WFE (in sharepoint integrate mode), could it be a Managed Path defined in the sharepoint? No, it is not either. Then how could this url be resolved?

The answer is,  this url is reserved in HTTY.sys, and SSRS 2008 is not using IIS any more, for more details see this post.

Why SSRS server need to have SharePoint installed?

It sounds odd at first that we need to install sharepoint on Report Server. The installation actually makes RS as one of SP WFEs even though it normally doesn't actually handle any sharepoint web requests (no AAM configuration for RS server)

The reason that SSRS need sharepoint is that RS extension use sharepoint object model to do security checking (see here for details), and SP OM can only be available on a sharepoint server.

The fact that SSRS use OM to check permission essentially makes it possible to use "Trusted Account" when configuring report server from SharePoint Central Admin:

With "Trusted Account" setting, you don't need to enable kerberos for either sharepoint site or RS web server. The downside however is, you won't be able to get a security trimmed reports. Data access is through either sql authentication or through a credential mapping.

Installation of SSRS 2008 add-in for Sharepoint 2007 is interrupted

SSRS add-in for Sharepoint need to be installed in every sharepoint server, but not required for RS server (even technically it is one of WFE). If you use SSRS 2008 R2, you need to install SSRS R2 add-in for SharePoint. At this time, this add-in is yet to RTM. SharePoint 2010 prerequisite installer will install the SSRS 2008 R2 Add-in for SharePoint 2010, so you don't need any extra install. this article is a must read on how to configure sharepoint 2010 SSRS integration.


When I working on a single server scenario, the installation is seamless, just click the .msi file. But when I scale the single server to a medium farm:

1 WFE
1 AS
1 RS
1 SQL

The installation was always interrupted and rolled back. The workaround documented in readme.htm, it requires a 2 steps command line. Weird but works.

what Sharepoint need to know about SSRS?

After SSRS provision a web service, all sharepoint need to know is its url as specified in this setting:

Like any web service, Equally important is how authentication is chosen (also shown above).

The interesting part is "Trusted Account" choice which makes service calls under user context without configuring kerberose. For this reason another setting, "grant database access", is a required configuration for SSRS. It essentially grant SSRS's service account a "full control" to all sharepoint web applications and some access to central administration ( as a proof, even though there is no place in UI where permission is specified for the SSRS service account, you can certainly log as this service account to any sharepoint site). Also this account is granted a "WSS Content Application Pool" permission to sharepoint Config db. The reason to grant those permission for RS service account is because SSRS web service will need to invoke sharePoint Object Model to check user permission in case of  "trusted account" authentication.

Sharepoint 2010 simplify the 2 settings in one place:

Error when selecting "windows Authentication" for Reporting Service Integraion

In Sharepoint 2007, you might get this error when configuring reporting service authentication mode:

 "The request failed with HTTP status 401: Unauthorized"

it can be caused by either

1) you are not inside the server where the central administration is hosted

or

2) the kerberos authentication in report server web service is not enabled. See this post on how to enable kerberos for RS

[Update] Sharepoint 2010 will not give this error in either way, but in order to get a security trimmed report, kerberos authentication still needs to be enabled manually.

Enable Kerberos authentication for SSRS 2008

Starting from SQL 2008, IIS is elminated from Reporting Service. RS web service authenitcation mode is defined in   rereportserver.config located in C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Service\Report Server\

If SSRS is running under localsystem account, Kerberos is enabled by default, if SSRS is running under a domain account, NTLM is enabled by default. In that case, to enable kerberos you first need to make the follwoing modification:

<authenticationtypes>
< rswindowsnegotiate >
< /authenticationtypes >

Notice: Use rswindowsnegotiate, DON'T Use rswindowKerberos, otherwise the RS service will immediately become non-accsible.


After that, you need to register SPN for SSRS servic account in Domain Controller, and also grant Delegation right for this account ( if it is localsystem account, grant the server computer delegation right instead)

Now the RS service is Kerberos enabled, but in order to use "Window Authentication (Integrated)" in data source configuratoin, the user database must be kerberos enabled too:

 To do that, you need to register SPN for sql service account running at user sql server, but delegation for this account is NOT necessary as it is the last hop in the chain.

You can certainly don't choose "Window Authentication", but  only the window authentication can give you  security trimmed reports, while other option can't not.

At this point, you should be able to view a report from RS web serive, but if you view reports from Sharepoint document library or Report Viewer web part,  you may get a 401 error

The reuest failed with HTTP status 401: Unauthorized

In order to make it work from SharePoint, SharePoint web applications have to be Kerberos enabled, and "window authentication" is selected in the setting of "Reporting Services Integration"

Permission to view reports for SSRS 2008 in SharePoint 2007 integrated mode

we know one of nice things about SSRS in SharePoint integrated mode is that user permission is managed by sharepoint site or library, in other words, to grant a user the permission to view report is as simple as to grant a read permission to sharepoint site or library where reports sit. It is very user friendly!

But one exception is, if you log in as sharepoint farm account or application pool id account, even though both accounts are marked as "System Account" and have access to absolute everything, it seems SSRS is not aware of them. You will get the following error when trying to view reports from sharepoint:

The permissions granted to user 'SP\spFarm' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'SP\spFarm' are insufficient for performing this operation.

[update] this problem only  occurs to sharepoint 2007 when kerberos is enabled for both sharepoint and RS, and data connectin uses window authentication. Sharepoint 2010 has this problem fixed.

The workaround is to add them explicitly in site or library, and the problem is gone.

another error when configuring sharepoint 2007 integration with Reporting Service

when I try to configure "Grant database access" of reporting service in Sharepoint Central Administration, I keep getting the following error:

Unable to connect to the Report Server WMI provider.

As I was able to do this for another Report Server where SSRS 2008 R2 is installed, so the first thing I was thinking of is to upgrade  SSRS 2008 SP1 with CU#5, but it made no difference.

The 2 servers are in the same domain, and can ping each other. Then i found the interesting thing is, i got the same error no matter what credential i put in, which lead me to think it might be a window firewall issue. I went to check the RS server's window firewall, it is ON! I turned it off, the problem went away immediately.

It turns out the following exception should be made: Window Management Instrument (WMI).  Sharepoint did give out a very useful error message! Don't blame SharePoint.