December 27, 2008 at 9:14 am
Scenario:
(Domain A) (Domain A)
(Server A) (Server B)
(User A Domain Server Admin A) (User A Domain Server Admin B)
(SQL Server A) (SQL Server B)
(Database A) (Database B)
(IIS 6.0 Server)
(Reporting Services)
(Data Source A - Svr A & DB A & WIS)
(Report A)
(Data Source B - Svr B & DB B & WIS)
(Report B)
What does work.
User A - Admin both servers. Report Manager on Server A - Both reports using appropriate Data Source A & B run fine.
User A - Admin both servers. Report Manager on Server B - Report A using appropriate Data Source A runs fine.
What doesn't work.
User A - Admin both servers. Report Manager on Server B - Report B using appropriate Data Source B won't run.
Gets the "Cannot create a connection to data source".
Question is why won't Report B with Data Source B on Sql Server B run?
Temporary fix - added local user to DB A and DB B, public and reader role. Changed Data Source B to credentials stored locally. Left Data Source A with WIS.
Question is why and did I need to add the local users to both DB's?
TIA,
Rich
December 27, 2008 at 10:18 am
Not sure I follow all of your dialog - but, if I am reading this correctly you are trying to access a report in SSRS on ServerA that accesses the database on ServerB using Windows Authentication.
Just to get this right:
Connect to SSRS (ServerA) from desktop PCA using Windows credentials (doman\user).
Access ReportA - which uses database on ServerA using Windows credentials (same domain\user). This works.
Access ReportB - which uses database on ServerB using Windows credentials (same domain\user). Does not work.
If so, this is known as a double hop and will not work with standard NTLM Windows authentication. To get this to work, you need to enable and configure Kerberos. Search this site for the recent article on setting up and configuring Kerberos to find out how to get this done.
Or, you can use a SQL account as you have it setup now.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2009 at 7:14 am
Manish Khattar has posted information regarding this recently in the thread Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
You can find the post here --> http://qa.sqlservercentral.com/Forums/Topic626429-359-1.aspx
--
Andrew Hatfield
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply