Login failed for user ''NT AUTHORITY\ANONYMOUS LOGON''

Question

Login failed for user ''NT AUTHORITY\ANONYMOUS LOGON''

QPR

SSC Eights!

Points: 814
More actions
December 7, 2005 at 6:37 pm

#111843

Hi,
I have installed SQL RS on my machine and on the Development server all is fine.
Except when I change the datasource to point to our production database and then I get the error in the title. I've checked and yes anon has no access to the production box but it does to the dev version. I don't want to allow it on the prod box and want SQL RS to run the report in the context of the user (domain) who attempts to launch it. IIS is set to windows authority with anon turned off.
I've looked (no idea if correct place) in the web.config file in the RS folder and impersonate is set to true. I am able to run the underlying query fine in VS.NET.
How can I set up the report server to always run all reports using the id of the domain user who launches it?
SQL 2000 SP3a (RS from the SP3a disk)
Visual Studio.NET 2003

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dave Klug SSCertifiable Points: 5811 More actions · Answer 1

Have you tried setting the authentication on the datasource for the report to Windows Authentication? Be aware though, users will not be able to create subscriptions for these reports then.

QPR SSC Eights! Points: 814 More actions · Answer 2

Datasource credentials?

They are already set to windows authentication... as is IIS (anon access unticked)

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 3

In development, is the SSRS installed on the same server as SQL Server? What about production?

K. Brian Kelley
@kbriankelley

Dave Klug SSCertifiable Points: 5811 More actions · Answer 4

So you have a dev and prod server both for RS and the SQL Database, correct? If you have access to both, then the issue is not with authentication with RS but with the datasource you are using to run the report.

I would suggest verifiying the properties on the datasource. Also, check the login in and permissions on your production database. If that doesn't help, can you attach the error you're getting?

sridevi sriraman Mr or Mrs. 500 Points: 580 More actions · Answer 5

You must use Network Credentials (and not default credential) if you are using web service to render reports. Also you must check the role based security of reporting services. See if domain users are given permission atleast as browser in reporting service role.

QPR SSC Eights! Points: 814 More actions · Answer 6

Hi,

I'll describe the set-up... hopefully this will answer a couple of the questions.

I'll re-read through the questions when I've had my Monday morning coffee (yawn) and see if I can answer anything specific.

We have a Dev box and a Production box.

DBs, users, permissions are identical.

The reports are created on our local machines and deployed to the Dev box.

IIS lives on the Dev box and RS has been installed on the Dev box.

When the datasource points to the Dev box all is well... when it points to the production box we get the error:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help
- Cannot create a connection to data source '<datasource_name>'. (rsErrorOpeningConnection) Get Online Help
  - Cannot open database requested in login 'DBName'. Login fails. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

(names changed to protect the innocent)

IIS and RS are both set to use Windows authentication so I'm not sure why the production box considers the request to be anonymous.... unless credentials are being dropped somewhere between IIS and SQl (production).

I had similar problems with a .NET application which I solved by setting impersonation to true in the web.config.

Thanks

david russell-253790 SSCarpal Tunnel Points: 4042 More actions · Answer 7

are host and clients in a domain or workgroup? the same domain?

Cheers,

david russell

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 8

Users are connecting to the web server on the dev box using Windows authentication. That's one "hop." Therefore, when the SQL Server also lives on the same box, in this case, the development box, things work just fine. The reason is because you're not having to make another "hop" using Windows authentication.

However, as soon as you go to a data source on the production box, you've just introduced a second hop. That hop is from the web server to the database server. So basically it's one hop from the client to the web server and a second hop from the web server to the database server. That's a no-no without additional configuration.

If you're on an NT 4.0 domain, there aren't any other options. Double hopping is prohibited by the NTLM protocol. If, however, you're on an Active Directory domain, you can enable delegation and this will allow for the second hop. Here's an article that explains how to do that:

How to configure an ASP.NET application for a delegation scenario (810572)

K. Brian Kelley
@kbriankelley

QPR SSC Eights! Points: 814 More actions · Answer 9

Thanks bkelley, that was exactly my thinking. (I didn't realise the double hop wasn't possible though).

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 10

It is in Active Directory, but with delegation turned on. Check with your system/domain administrators about this.

K. Brian Kelley
@kbriankelley

sohelnax005 SSC Enthusiast Points: 113 More actions · Answer 11

Dear,

I finally got a solution. It may not be suitable for everyone. But any one can try it.

we have a SSRS Server that host for report only and our data are in different server. This is a issue of Multi hoping. I have read so many articles on this But none of suggested solution are suitable for us. Because, our servers are not in Domain environment, right now we are unable to implement the Impersonation and delegation feature. So, I found a solution.

Our data servers are linked server in a our report server. I configure report's data source to use OLE DB data source for Microsoft SQL Server and write same query with fully qualified object name (Distributed Query)

like- select * from 127.0.0.1.[DB_Name].[Schema_Name].[Table_Name]

Now i deploy the report on report server and any user who have the windows login on report server can access reports from report server.

Zahirul