SQL authentication reporting services

Posted on 2009-04-21
Last Modified: 2012-05-06
We want to use Windows Authentication to log on to the SQL Server for the reports however when we try this we get the following error message in the SQL Server Error Log:

Login failed for user NT AUTHORITY\ANONYMOUS LOGON Token based server access validation failed with and infrastructure error.

Ive done some searches on Google and it appears that the issue is down to the login details being lost between the Reporting Services server and the SQL database server, and that the SQL server needs the Trust this computer for delegation to specified services only setting enabled or something like this.
windows 2008 sql 2008
Question by:mhamer

    Author Comment

    "      An error has occurred during report processing. (rsProcessingAborted)
    o      Cannot create a connection to data source 'CorpRpt'. (rsErrorOpeningConnection)
    ยง      For more information about this error navigate to the report server on the local server machine, or enable remote errors

    database and reporting services are on diffrent 2008 boxes  IIS is not installed on either.
    LVL 3

    Expert Comment

    I have experience the same error in the past.  This is the classic "double hop" issue.  Client to SSRS and then SSRS to SQL Server.  To resolve this you'll need to set up Kerberos between the SSRS server and the SQL Server.  Fortunately for me, I just had to ask our network team and they did it for me.  There are a lot of sites that talk about setting up Kerberos.  Here is a good article to start with.

    Author Comment

    Hi, thanks, now done that but still getting the error

    Author Comment

    no IIS in use  bit stuck   (2008 reporting services doesnt rely on IIS)

    Author Comment

    Hi any more help please?
    LVL 96

    Accepted Solution

    Wow, you have an interesting challenge on your hands!!  There might not be enough evidence to support my assumptions (ASP.NET or other equivalent web site).  It sounds like you are allowing anonymous access to your web site, which means that different credentials are used to authentication.  The current user's credentials are used when you are using Windows authentication for a web site, but not Forms authentication, and not when anonymous is allowed.

    Author Comment

    no anonymous users.

    should just be using the users window credentials from start to finish with out challenging them

    it works fine when reporting services and sql are on the same box

    Author Closing Comment

    It is the double hop issue  getting it working in our enviroment is the next step    works fien in test lab though  thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now