SSRS authentication error when deploying Monitoring Reports

There’s a lot of guides that go through the process of deploying Monitoring Reports in Lync/SfB. There’s a lot of articles that correctly diagnose the most common issues too. I ran into one of these issues and wanted to briefly share my findings.

The Error

I went through the normal deployment process and the deployment wizard did not encounter any errors. I launched went to the URL to check things out and got hit with the common error

“An error has occurred during report processing. (rsProcessingAborted)
Cannot impersonate user for data source ‘CDRDB’”

Whatever, we’ve all seen that before. I forgot the fix and stumbled upon this faithful blog post which walks you through changing the credentials used to access the CDR and QOE databases.

I walked through that, but every time I would try to test the connection I would get the error

error

“Log on failed. Ensure the user name and password are correct”

Of course I just assumed I messed up the service account or something of that nature. After a bunch of other attempts and after verifying the credentials by logging into something else, I still could not get it.

The Solution

By default, when you run the wizard to deploy monitoring reports it configures SSRS for these reports to store the credentials on the report server. You can see that in the screenshot above by the “Credentials stored securely in the report server” radial. Using this option has some requirements though (see this Technet article). Most importantly

“If you use stored credentials to connect to an external data source, the Windows domain user account must have permission to log on locally.”

The article then goes on to explain how you do this. User Local Security Policy, go to Security Settings->Local Policies->User Rights Assignment, and under ‘Allow log on locally’ you will want to add the account that you are trying to use for SSRS.

However, many organizations will not allow you to modify this locally as it will be set by group policy. If that’s the case, the “Add User or Group…” button will be grayed out like this

gpo

 

If that’s the case, you need to either add the account to whichever security group is already allowed (do not add it to the local admin groups) or you need to just add the account as an allowed login user by editing the existing GPO.

Once you add the account you can test the connection and all should be good (unless you are using the wrong username/password of course).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s