Hi,

Background - reinstalled sql and tfs but could not remember the password for the reporting services encryption key. I deleted the key and now am trying to add in all the bits that I am missing.

I have added the datasources with connection string "Data source=TFS;initial catalog=TFSWarehouse" and set the data source type to Microsoft SQL Server Analysis Services and Microsoft SQL Server for TfsOlapReportDS and TfsReportDS respectively. I also still have the role TfsWarehouseDataReader set up for both Microsoft SQL Server Analysis Services and Microsoft SQL Server. The securables section for TfsWarehouseDataReader in Microsoft SQL Server is blank if that makes any difference.

After entering these details I am still getting the following error when trying to view a report:

• An error has occurred during report processing. (rsProcessingAborted)
  • Cannot create a connection to data source 'TfsReportDS'. (rsErrorOpeningConnection)
    • Login failed for user 'MyDomain\tfsreports'.

And also in the event log is the error:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date:  4/09/2006
Time:  2:44:12 PM
User:  MyDomain\tfsreports
Computer: TFS
Description:
Login failed for user 'MyDomain\tfsreports'. [CLIENT: <local machine>]

If anyone has any ideas that would be great. I am sure its something simple but I just don't have a working TFS RS to use as a reference at the moment.

Thanks heaps.

Hi Scotty,

Have you been able to resolve this issue? If not, please let me know and I can try to find help.

Thanks,
-Matt

Hi Matt,

No, still no fix. Currently one of the team here is building a new TFS single server, I was going to do a comparison, but they are still waiting on hardware / access to a virtual machine.

Cheers,

Hi Scotty,

Can you please take a look the following post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722742&SiteID=1 to see if that helps? I just want to know if the reader role is correctly specified in SQL Server and Analysis Services databases.

Thanks.

Federico

Hi Frederico,

I followed the reference post that you provided and it did lead me to resolving the problem. Thanks heaps. I knew it would come down to a simple permission missing somewhere. It turned out to be the actual SQL Security Login for domain\tfsreports was missing. The TFSWarehouse Security Role for domain\tfsreports was there, and it was included as part of the TFSWarehouse Database role TfsWarehouseDataReader. So basically all the security settings as set out in the previous post were already correct, but.. the only one that you missed but made me think of was the SQL Security Login for domain\tfsreports. Thanks for the help, I could not have done it without some prodding, I had already searched through the permissions and was going crazy. I will add a note to the other post about that.

Also one thing I noticed: in the TFSWarehouse Cube, the Role TfsWarehouseDataReader has its membership for an SID, not the user name. When I tried to enter the domain\tfsreports user it gave me an error [The following system error occurred: No mapping between account names and security IDs was done. .
(Edit Role - TfsWarehouseDataReader)]. I figured out that the SID is actually for domain\tfsreports using the great tool, psgetsid.exe.

Hi Scotty,

I'm glad you were able to fix the problem. About your last comment I think that it is a known issue with Analysis Services (this post has some more information http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595103&SiteID=1), you should be able to add members to the role as domain\username.

Can you try to restart Analysis Services, delete the SID user from the role, restart AS again and then re-add the user but now as <domain>\<user>.

Thanks.

Federico

Here is something else I have discovered while upgrading from RC to RTM and also whilst trying to create a new project (more permissions that I had to create in order to enable the wizard to upload reports):

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=662319&SiteID=1

Cheers,