I was recently working with a Dynamics AX 2009 customer who had Enterprise Portal and SQL Server 2008 R2 Reporting Services installed on the same server – a fairly common configuration. Everything worked great except that they were encountering a barrage of logon dialog boxes each time they launched a Reporting Services report from the AX client, or directly from the Report Manager website. After clicking through a number of dialog boxes, the report rendered as it should.
Usually the logon dialog box can be eliminated by adjusting your Internet Explorer security settings to allow automatic logon as pictured below, but that didn’t resolve our issue.
If this would have been SQL Server 2005 Reporting Services, I would have started looking directly at IIS and file permissions for the virtual directory, but this is SQL Server 2008 R2, so IIS shouldn’t be involved at all. Eventually I resorted to capturing an http trace of the logon process to see what was going on. What I noticed was something really strange--the logon dialog box was popping up because IE was in fact trying to access files from an IIS path. Interesting? I thought so.
Thankfully somebody else must have encountered the same thing at some point because I found that it’s an issue fixed in Cumulative Update 6 for SQL Server 2008 R2. After updating SQL Server to CU6, the ever persistent logon dialog box disappeared forever. The KB article for the fix and CU6 are included below for your convenience. Hopefully you end up spending less time troubleshooting this issue than I did!
Interesting bug. Good to know.
About Trusted Sites Zone. I never put local servers in that Zone, only external websites that can be trusted (like *.microsoft.com).
I always add any SharePoint or SSRS servers to the Local Intranet Zone. Then login is automatically pushed through for those servers.
Tommy, thanks for the comment. I agree that the Local Intranet Zone is a more appropriate zone for SSRS servers. I was mostly just trying to show the authentication options circled in red. I guess I didn't pay close enough attention to the zone I was in when I captured the image. Happy Reporting!