SSRS Report Rendering Hangs With Stored Credentials For XML data source type
In SSRS Report Server (SQL Server 2008 R2), I have a shared data source with data source type of XML. I have a report that uses a shared data set based off of the shared data source. The XML endpoint is living in an IIS-hosted Windows authentication app, and my development server is on a domain. In Report Server, if I set the shared data source "Connect Using" option to "Windows integrated security", the report renders quickly and as expected. If I set "Connect Using" option to "Credentials stored securely in the report server" (with either domain or local account as configured account), the report rendering hangs indefinitely. There is no error. The "Loading... cancel" popup never goes away. The last line in the Report Server log file says: library!ReportServer_0-6!19a4!11/22/2011-10:59:27:: i INFO: RenderForNewSession('/Test1/MyReportThatHangs') Since Report Server caching does not work with "Connect Using" = "Windows integrated security", I'd really like to use "Credentials stored securely in the report server". Is it possible that option is not supported with the XML data source type?
November 22nd, 2011 2:11pm
Hi Caleb, After you type in the relevant windows credentials in the relevant textbox, you also need to check the Use as Windows credentials when connecting to the data source checkbox, then the Report Server would treat the credentials as windows credentials, and use it to connect to the Xml endpoint hosting in IIS. Thanks, Bill LuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.
November 23rd, 2011 5:21am
I did check "Use as a Windows credentials when connecting to data source". I'll probably try attaching a debugger and monitoring network traffic to see if I can get any more info later on this week.
November 23rd, 2011 10:28am
The IE Developer Tools "Network" tab, says that the following URL request is stuck as "(Pending)" and it has been pending for at least the last twenty minutes (browser window shows the "Loading... Cancel" popup). http://mymachinename/Reports_SQLEXPRESS/Pages/Report.aspx?ItemPath=%2fTest2%2fSampleReportNumberOne So, I guess I need to attach to that process and see what it's waiting on...
November 23rd, 2011 3:21pm
On the most recent run, I checked both "Use as Windows credentials when connecting to the data source" and "Impersonate the authenticated user after a connection has been made to the data source". Over an hour later, the "Loading..." popup is still there. Looks like some timeout exceptions show up in Reporting Services log after 16 and 19 minutes: library!ReportServer_0-1!15e4!11/23/2011-14:56:27:: i INFO: RenderForNewSession('/Test2/SampleReportNumberOneSampleReportNumberOne') library!WindowsService_1!ae4!11/23/2011-15:05:35:: i INFO: Call to CleanBatch() library!WindowsService_1!ae4!11/23/2011-15:05:35:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings, 0 edit sessions. library!WindowsService_1!ae4!11/23/2011-15:05:35:: i INFO: Call to CleanBatch() ends processing!ReportServer_0-1!ae0!11/23/2011-15:12:24:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources ... library!ReportServer_0-1!154!11/23/2011-15:15:27:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.; session!ReportServer_0-1!154!11/23/2011-15:15:27:: e ERROR: Error in GetSnapshotData: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The System.Data.SqlClient.SqlException is confusing because there are no SQL data sources. This is an XML data source. Whatever the case, the error behavior of having the client hang indefinitely seems completely broken. IE still thinks the URL request is "(Pending)".
November 23rd, 2011 4:08pm
I guess I didn't wait long enough to get the error this guy did: http://completedevelopment.blogspot.com/2009/01/network-provider-issues-and-sql-server.html Renaming c:\windows\system32\BCMLogon.dll to BCMLogon-RENAMED.dll fixed the problem (I'm working off of a Dell laptop). I can't believe I didn't try that before... Anyhow, I still think it's odd that there was a System.Data.SqlClient.SqlException: Timeout expired exception when checking "Impersonate the authenticated user after a connection has been made to the data source" with an XML data source (unless SqlDataClient has some mode where it can load XML from urls). Also, it seems that reporting services isn't properly notifying clients that the HTTP request failed and instead leaves the client hanging (but perhaps http.sys does not allow a response that after 15 minutes).
November 23rd, 2011 4:41pm