I had setup a test environment with SQL Server Reporting Services in integrated mode with SharePoint 2010. My initial tests had confirmed everything had been setup correctly, including Kerberos. I had even setup a few test reports to a SQL database and everything was fine. I then rolled in a copy of production data and the reports just didn’t want to work.
I attempted to modify the data connection which was looking at a SharePoint list, but wouldn’t connect successfully, instead coming up with the error The report server has encountered a configuration error.
I checked the SSRS logs (default location: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles) and saw this in the text:
Call to TestConnectForDataSourceDefinitionAction().
ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon attempt for user ‘tst.sprsunattended.svc‘ failed., Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. Ensure the user name and password are correct. —> System.ComponentModel.Win32Exception: Logon failure: unknown user name or bad password
I initially thought this was permissions related, so in an effort to give this account full access to the web application, I discovered something peculiar:
The short account name had been truncated to the first 20 characters! I had seen this before in other scenarios, and this is a short explanation on TechNet:
“The SAM-Account-Name attribute (also known as the pre–Windows 2000 user logon name) is limited to 256 characters in the schema. However, for the purpose of backward compatibility the limit is 20 characters.”
While I had checked the SharePoint service accounts, I didn’t pay too much attention to those for SSRS. I updated the Execution Account details in the Reporting Services Configuration Manager to the 20 character account name and pressed Apply.
Without a service restart or an IIS reset, I then proceeded back to the data source and retested the connection, this time with success!