Double hop error while rendering a report on Report Manager
-
Hi, I'm a SQL DB engineer and not an SSRS expert. I created a report which I hosted on an URL and am trying to create an subscription to my team for informational purposes.
Purpose of the report: Display growth of all database's growth and display monthly, weekly and current numbers with hi-lighted on the ones which occupied more than 80% of space than allocated.
Process of rendering: The report calls a Store proc and just validates and adds the color coding expression. The store procedure pulls data from 3 different remote tables using 3 linked servers. (Basically it only pulls the records from tables in which the database growth is been stored)
Error on Report Manager: "An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. OLE DB provider "SQLNCLI10" for linked server "DataServerSeven" returned message "Invalid connection string attribute."
I guess this is a double hop issue.
Few informational Tips:
--The Store proc populated the result set without any issues.
--The linked server connection looks goon for all the 3 linked servers.
--The report renders fine in the visual studio where I build the report. (I build the report from different server and just called the store proc remotely. Everything works fine here. )
--When I execute the Store proc from SSMS in any different server and connect to the SQL instance where the SP resides, it gives me a similar error like the above one.
--
Trouble shooting steps taken:
1) Created a Constrained delegation Setup for the Backend SQL instance of the report manager and all the SQL instances involved with the report.
2) Tried to run the report with the service account of the report manager's server.(Made sure that service account has enough credentials to execute the report in Back end SQL instance where the store procedure resides)
3) Cannot create a linked server using different SQL login credentials so that I can run the report using the same credentials because our application's business rule insists us to have windows authentication mode.
Please help me out in this issue. cannot trouble shoot any further and is been on my name past 3 days.
-
looking into this.
-
You must not have constrained delegation set up correctly or the report server is not able to connect via kerberos for some other reason.
Also, don't use Windows Auth for the report. Configure it to use a specific account with a login and password stored as Windows Credentials. Essentially, use a domain service account to connect the report to the SQL Server.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
That was how exactly I have configured the datasource in the report manager.
Yes, U R correct in spotting out on the CD Setup. It was by defaultly set up on the port 1433. I need to put it to the appropriate port and try rendering the report.
But mean while I do that can anybody please give me any more suggestions?
-
Other suggestions would be to pull the data locally rather than using linked servers.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply