July 17, 2012 at 10:46 pm
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.
July 18, 2012 at 8:47 am
That certainly looks like a KERBEROS error message to me. I'd double-check all my SPN's and work with the network/windows admin to ensure that delegation is setup correctly on the Report Server and all the SQL Servers.
I'd also try the report with just one of the linked servers in the the stored procedure to see if the issue is just with one of the linked servers or if it is on the Report Server or the SQL Server that hosts the procedure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply