SQL Reporting Services Login Issue With CRM 3.0 - Another Fix

Ever since CRM 3.0 hit the market there have been issues with getting reports to work with new installations. Particularly troublesome is when the SQL Server is on a different server than CRM. There are a number of KB articles and documents that Microsoft has released pertaining to this problem. It often comes down to the now-infamous Kerberos double-hop: When a user requests a report through the CRM interface their credentials are passed (via Kerberos) to the SQL Reporting Services server. No problem up to that point. But then the SRS server requests the data from the SQL Server and may fail to pass the originating user's credentials to the SQL Server. This is the double-hop that the credentials need to make. The problem can be complicated by duplicate SPNs, trust for delegation not being correctly configured and many other factors depending on the network topology.

I thought I had seen them all, but an installation we did this week presented new challenges. Users were getting the dreaded error: "Cannot create a connection to data source 'CRM'. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

I followed all the standard KB articles out there (links at the end of this post) to no avail. I am stubborn so it was a while before I gave up and got Microsoft involved. I'm glad I did. Their friendly engineer sent me a number of diagnostic tools and had me go through many steps to make sure everything was configured correctly. What finally got the problem resolved was the following:

Adding the CRM installation account credentials to the data source:
1) Open Report Manager (http://server/reports).
2) Click the Show Details button on the MSCRM_DataSource.
3) Click the Edit button for the DataSource.
4) Select the "Credentials stored securely in the report server" radio button and enter the credentials for the CRM installation account or a domain administrator.
5) Select the following two checkboxes:
- "Use Windows credentials when connecting to the data source" and
- "Impersonate authenticated user after a connection has been made to the data source."
6) Click "Apply" and attempt to run the reports as a user logged on to a client machine.

So, the lesson is, if you've tried everything, try something else. I don't recall ever having to set this before in numerous other CRM deployments, but in this case, this is what did the trick. Hope you find this helpful. If not, here are some other troubleshooting resources:

http://support.microsoft.com/kb/909509/en-us
Additional Setup Tasks Required if Reporting Services Is Installed on Different Server