Monday, December 10, 2012

SSRS Execution Account Needs PrivUserGroup

I discovered, somewhat by accident, that if you specify an Execution Account in the SQL Server Reporting Services Configuration Wizard, the specified account will provide the security context for SSRS Web Services when using the CRM Reporting Extensions (SRS Data Connector).  Sound complicated?  It’s really not.

The Execution Account is SSRS’s way to connect to external resources which do not require login (or those for which no other credentials have been configured).  This keeps the SSRS service account safe from making any external connection with its own credentials.  CRM’s “SRS Data Connector” removes credential requirements in favor of an alternate impersonation scheme (which eliminates double-hop Kerberos, and makes reports easier to access).

Perhaps that was obvious to people with more SSRS education, but I thought it was a maintenance account.  /shrug  A brand-new installation is likely the place you’ll run into this issue.  Unfortunately, the real causes of the failure are best viewed from the SSRS logs.

The problem manifests as the following exception:

"Cannot create a connection to data source 'CRM'."

But ultimately is caused by this exception:

"Immediate caller <SQL Server Reporting Services Execution Account> has insufficient privilege to run report as user <SID>"

So the long and short of it:  if you specify an Execution Account for SSRS, and use CRM’s “SRS Data Connector”, then the Execution Account needs to be added manually to “PrivUserGroup”.