Share via

Using PowerShell to discover Reporting Services 2012 in SharePoint

  • Article

I was recently going through an exercise of documenting how to discover certain aspects of Reporting Services for some Kerberos work.  RS 2012 in SharePoint is a totally different game though.  The easiest way I could discover certain items about Reporting Services within SharePoint was with PowerShell.  For previous versions, and RS 2012 in Native Mode, we can use other avenues such as WMI to discover configuration of Reporting Services.

Service Enumeration

One of the big things we want to do when we are discovering what is out there is to be able to tell if we even have the service installed.  This may also lead to multiple services within SharePoint.  Within SQL 2012, Reporting Services is a Shared Service within SharePoint and is deployed as such. We could run the following to see the Service Applications that are configured.

Get-SPServiceApplication |where {$_.TypeName -like "SQL Server Reporting*"}

image

This aligns with what we see in Central Admin as well.

image

From the Kerberos/Claims configuration perspective, we are also interested in the Service Account for the Reporting Services Application. Also keeping in mind that there may be more than one.

$apps = Get-SPServiceApplication |where {$_.TypeName -like "SQL Server Reporting*"}
foreach ($app in $apps){"{0,-20} {1,-20}" -f $app.name, $app.ApplicationPool.ProcessAccountName}

image

We can see that the name is “Reporting Services” and the AppPool Process Account is “BATTLESTAR\rsservice”. The next piece of information we want to know is which SharePoint boxes is this service running on?  We could have any number of App Servers that we need to go check.

$services = Get-SPServiceInstance |where {$_.TypeName –like "*Reporting*"}
foreach ($service in $services){"{0,-20} {1,-20}" -f $service.parent.address, $service.status}

image

In this example, I only have one server that has the Reporting Services Service started within SharePoint and that is on the CAPTHELO server. We can then compare with the Claims to Windows Token Service (C2WTS).

Claims to Windows Token Service (C2WTS)

As part of SQL 2012 with SharePoint Integration, the Claims to Windows Token Service plays a big part in our functionality when it comes to Kerberos. As such, if we are going to validate RS 2012 in SharePoint configuration, we need to look at C2WTS as well. We can use the following PowerShell command to get the C2WTS instances:

Get-SPServiceInstance |where {$_.TypeName -like "*Claims*"}

One thing to remember is that C2WTS is not a service app.  You may see multiple items here if they have more than one SharePoint Server.  For example, in my environment I have two SharePoint Servers:

image

The service only needs to be started on the SharePoint Box where the Reporting Services Shared Service is started as well. In the Reporting Services example above, we know that Reporting Services is only active on CAPTHELO, so that is where C2WTS needs to be started.

$services = Get-SPServiceInstance |where {$_.TypeName –like "*Claims*"}
foreach ($service in $services){"{0,-20} {1,-20} {2}" -f $service.parent.address, $service.status, $service.Service.ProcessIdentity.username}

image

We can see that we have two SharePoint servers here.  C2WTS is started on LTBOOMER but it is not running on CAPTHELO.  This will result in an error such as the following:

image

Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ClaimsToWindowsTokenLoginTypeException: , Microsoft.ReportingServices.Diagnostics.Utilities.ClaimsToWindowsTokenLoginTypeException: Can not convert claims identity to windows token. This may be due to user not logging in using windows credentials.; 

From the last command above, we can also see the Process Identity for the C2WTS.  So, this will provide the service accounts for both Reporting Services and C2WTS as well as let us know which servers Reporting Services is enabled on and which servers C2WTS needs to be enabled on.  This could be very helpful when it comes to automation.

 

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton

Comments

  • Anonymous
    November 18, 2013
    Very helpful. I had an intermittent issue with C2WTS (every other report run failed). Thanks to this guide I found out reporting services was running on the WFE and App server. Turning it off on the WFE worked for me. Thanks a lot!