In an environment where the users/clients have to connect to Team Foundation Server located on a untrusted domain or on a different domain, the users/clients' are prompted to provide the TFS credentials to connect successfully. In such scenarios, if the users try to generate an Excel report against a stored Work item Query, the report generation would prompt for TFS credentials once and later prompt for SQL Analysis services credentials couple of times even if you provide a valid credentials to authenticate against SQL analysis cube. Eventually, the report generation will fail.
While executing a query against SQL analysis service located on a different domain, the logged on user credentials on the client machine is used to authenticate. Since this account is not a valid user on the SQL domain, the credentials prompt is displayed multiple time.
The workaround to this issue is to use the Windows vault feature provided by the Windows Credential Manager that is available on Windows Vista and later Operating systems.
1) On the client machine, (every client that needs to have the ability to generate Excel report) launch Control Panel and select "Credentials Manager"
2) In the "Store credentials for automatic logon" windows, select 'Add a Windows credentials' option, in the section "Windows Credentials"
3) In the "Type the address of the website or network location and your credentials" window, type the following
For Internet or Network address: <NetBIOS name of TFS application tier>
User Name: <Domain\TFS Service account>
Password: <Password for TFS service account>
Important: repeat step 2 and 3 again to add a Windows credential to access the SQL analysis cube, Similar to the figure below:
For Internet or Network address: <NetBIOS name of SQL analysis server>
4) Restart Team Explorer on the client and try generating an Excel report for a Work item Query.
Content developed by: ArunRama
Content reviewed by: Lakhminder Singh