Set up Reporting Services in SharePoint Integration Mode

I followed How to: Configure SharePoint Integration on Multiple Servers step-by-step to configure SharePoint Integration on two servers. But on the step "Set server defaults" of "Configure the Report Server Integration Feature in SharePoint Central Administration", I only saw this error:

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

I was sure I followed the steps carefully. Then I tried to look for error details in Web and Database log files, but there was no any error message. After many hours of investigation, I found somebody mentioned Kerberos/NTLM on MSDN Forum. As my environment did not allow me to use Kerberos, I tried to set up NTLM on both SharePoint server and Report server:

cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

Then I was so happy to see the error disappeared! :)

SSAS Permission for Least-Privilege User Account

When using LUA (Least-Privilege User Account) on development machine, I normally add myself as sysadmin for SQL server, so that I can create database freely.

As SSAS is part of SQL Server, I took it for granted that my account was also sysadmin in SSAS. But it turned out that SSAS has different permission settings from SQL Server.

When I tried to deploy an SSAS project, Visual Studio threw the error:

Error -1055391738 : Either the '[domain]\[account]' user does not have permission to create a new object in '[machine]', or the object does not exist.

So I went to SQL Server Management Studio and connected to SSAS, but I had no permission to create SSAS database manually:

Either the '[domain]\[account]' user does not have permission to create a new object in '[machine]', or the object does not exist. (Microsoft.AnalysisServices)


One way to solve the problem is to add my account to the SSAS' server role, which means to grant server-wide security privileges to my account in SSAS:
  1. Run SQL Server Management Studio as Administrator account
  2. Connect to the SSAS server
  3. Right-click the SSAS server name and select Properties from the popup menu
  4. Select Security to add account to server role

Now, I can deploy SSAS project in Visual Studio without problem.

SharePoint can not find user in its domain

It's quite weird that SharePoint (WSS 3.0) could not find any Active Directory user account from its own domain!

According to its log, WSS complained it could not get trusted domains:

05/08/2008 10:09:18.72  Error when trying to get trusted forests and domains. Exception message: Access is denied.  , callstack:    at System.DirectoryServices.ActiveDirectory.Forest.GetTrustsHelper(String targetForestName)     at System.DirectoryServices.ActiveDirectory.Forest.GetAllTrustRelationships()     at Microsoft.SharePoint.Utilities.SPUserUtility.GetTrustedDomains(List`1 trustedForestNames, List`1 trustedDomainNames)  
05/08/2008 10:09:18.72 Found 1 trusted forests ad.int.com.
05/08/2008 10:09:18.72 Found 0 trusted domains
05/08/2008 10:09:18.87 Error in searching user 'Bob' : System.DirectoryServices.DirectoryServicesCOMException (0x8007052E): Logon failure: unknown user name or bad password. at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail) at System.DirectoryServices.DirectoryEntry.Bind() at System.DirectoryServices.DirectoryEntry.get_AdsObject() at System.DirectoryServices.DirectorySearcher.FindAll(Boolean findMoreThanOne) at System.DirectoryServices.DirectorySearcher.FindAll() at Microsoft.SharePoint.WebControls.PeopleEditor.SearchFromGC(SPActiveDirectoryDomain domain, String strFilter, String[] rgstrProp, Int32 nTimeout, Int32 nSizeLimit, SPUserCollection spUsers, ArrayList& rgResults) at Microsoft.SharePoint.Utilities.SPUserUtility.SearchAgainstAD(String input, SPActiveDirect...
05/08/2008 10:09:18.87* ...oryDomain domainController, SPPrincipalType scopes, SPUserCollection usersContainer, Int32 maxCount, String customQuery, String customFilter, TimeSpan searchTimeout, Boolean& reachMaxCount) at Microsoft.SharePoint.Utilities.SPActiveDirectoryPrincipalResolver.SearchPrincipals(String input, SPPrincipalType scopes, SPPrincipalSource sources, SPUserCollection usersContainer, Int32 maxCount, Boolean& reachMaxCount) at Microsoft.SharePoint.Utilities.SPUtility.SearchPrincipalFromResolvers(List`1 resolvers, String input, SPPrincipalType scopes, SPPrincipalSource sources, SPUserCollection usersContainer, Int32 maxCount, Boolean& reachMaxCount, Dictionary`2 usersDict).


But do I need care if the domain is "trusted" or not when only one domain exists? What I wanted was to get users from the same domain. MSDN also said "Users in the forest that the server is in (that is, a resource forest) are displayed automatically." But the reality was the opposite.

Finally, I had a try to add the WSS server's local domain using stsadm:

stsadm -o setproperty -url http://localhost:82
-pn "peoplepicker-searchadforests" -pv "domain:ad.int.com"

Although I had thought that the command should do nothing because I was not supposed to do that, ironically I could see users in the PeoplePicker control! :)

SharePoint XML Web Part and proxy setting

XML Web Part is quite useful when you want to collect data from other sites (e.g. RSS). Yesterday I wanted to display local weather information using Weather.com XML Data Feed. I followed these steps:
  1. Registered to weather.com to download the SDK (to use weather images)
  2. Copied SDK images to TEMPLATE\Images\weather\
  3. Added XML Web Part to WSS site and set URL to
    http://xoap.weather.com/weather/local/(zipcode)?cc=*&prod=xoap
    &unit=e&par=null&key=(license key)
    [Update] Weather.com has changed the URL to
    http://xoap.weather.com/weather/local/[zipcode]?cc=*&dayf=5
    &link=xoap&prod=xoap&par=[PartnerID]&key=[LicenseKey]
  4. Set up XSLT to display weather images
But I got errors on different WSS machines:
  1. "Cannot retrieve the URL specified in the XML Link property"
  2. "The web part has timed out"
The reason was my WSS sites were behind a proxy server. WSS XML Web Part need know proxy settings, otherwise it can not access the URL.

The first error happens when the WSS application pool does not use a domain account; the second error happens when a domain account is used.

After I added proxy settings in web.config, the problem was solved:

<!-- Proxy setting -->
<system.net>
<defaultproxy useDefaultCredentials="true">
<proxy usesystemdefault="false"
proxyaddress="http://proxyServer:port"
bypassonlocal="true">
</proxy>
</defaultproxy>
</system.net>

ZoomIt

This post is mainly for my own recall.

Robert Shelton provides a link for the excellent screen zoom and annotation tool for Windows. It is quite useful for presentation of course, but also useful to zoom in/out high resolution screen during normal work.

I tried on my computer just now:

Reports cannot be deployed from VS 2005 to Reporting Services in WSS integration mode

I tried to deploy reports from VS 2005 from Dev machine to Prod Report Server across different domains, but VS 2005 did not allow me to deploy.

The Reporting Services on Prod was set up in WSS integration mode. I set up TargetDataSourceFolder, TargetReportFolder and TargetServerURL in VS 2005 on my Dev machine as mentioned in MSDN article "Deploying Reports, Models, and Shared Data Sources to a SharePoint Site". Then I tried to deploy reports to Prod machine. But VS 2005 keeps showing "Report Services Login" window, even when I used Administrator account of Prod.

The WSS log file on the Prod machine shows this error: "The file you are attempting to save or retrieve has been blocked from this Web site by the server administrators."

Solution? After two-day frustration and research, then the problem was solved: I need use Internet Explorer to log into WSS and check the "Remember my password" checkbox (Very important!). Then I could deploy from VS 2005 without seeing the login dialog window. The deployed report on Prod showed the author was the same account that I used to login from IE.

The same trick can be applied to Report Builder. When user tries to use Report Builder in WSS to build ad-hoc report, he/she should make sure to check the "Remember my password" checkbox in the login window. Otherwise, the user will see "unauthorized" error when downloading Report Builder application.

Do not remove VS 2005 and install VS 2008 if ...

Do not remove VS 2005 from your computer and install VS 2008 if:

1. You want to develop Business Intelligence solutions with SQL Server 2005, because VS 2008 does not support those projects (Report Designer, Report Model ...). Or to say, Microsoft does not want you to work on SQL Server 2005 with their own latest IDE!

2. You want to build Workflow (WF) solution for SharePoint. You will realize the old way in VS 2005 to install Features.xml is easier to use.