SQL Reporting - Security Credentails Query

[kezzab]

I wonder if anyone can shed a light on this query for me?

here's a brief overview:-

SQL Server, Reporting Services and IIS are all on one development box - I point my local browser to the reporting service homepage URL and my published reports appear as expected.

If I select a report where the datasource is pointing to the SAME sql server then the report runs ok.

However if the report is referencing a database on another SQL Server I get the error:-

"An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'datasource'. (rsErrorOpeningConnection) Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Finally if I remote onto the development box all reports run fine.

I know I’m missing some sort of security setting / sql login configuration but I don't know what – any ideas anyone?

 

[andrea96]

I have had a similar problem when attempting to use windows authentication with ASP .NET apps to a SQL Server on a different server than IIS. The problem is that the IIS server cannot pass the credentials to the SQL server. I found this in the Brian Larson Microsoft SQL Server 2000 Reporting Services book.

In order to get integrated security to work across more than one hop, your Windows domain must use a special kind of security known as Kerberos, which allows authentication across multiple hops. Using Kerberos security, integrated security will work across any number of servers in the network.

Hope this helps,
Andrea