My struggle with Browser enabled InfoPath Form connection to 'UserProfileService' Web Service

Overview

I faced a silly error when trying to use data connection in a Browser-enabled InfoPath Form to connect to MOSS built-in 'UserProfileService' web service in order to retrieve the display name (Preferred Name) of the logged in user. It works great without any error when I preview the form in InfoPath. However, I keep seeing the "ID 5566" error after I published the form to a document library and open it in browser. On the other hand, the published form works to show the current logged in user of the computer (not the MOSS portal) when opened in a client application (i.e. InfoPath).

Symptom

1. Fail with "ID 5566" error when the form is opened in web browser.
2. Work without error when opened in preview mode or in client application, shows the logged in user of the computer.
3. Fail to work with all methods in 'UserProfileService' web service.

Cause

After many googling and trial & error, I still can't find the cause. Or I actually overlooked one suggestion in a post. That is the "Allow Anonymous login" option. I thought that this is the option in "Central Administration" -> "Application Management" -> "Authentication Providers" -> "Edit Authentication". But actually the main problem is at IIS settings of the web applications' authentication.

Reference

InfoPath - Get the current user without writing code

Steps to disable "anonymous login" in "IIS Manager"

1. In Windows Server 2003 R2, click "Start" -> "Administrative Tools" -> "Internet Information Services (IIS) Manger"
2. In "IIS Manager", expand the name of your computer on the left pane, expand "Web Sites"
3. Right click on the web application you want to edit (e.g. mine is "SharePoint - 80", select "Properties"
4. In "Properties", select "Directory Security" tab. Press "Edit" in the "Authentication and access control" category
5. Uncheck "Enable anonymous access" in "Authentication Methods", click "OK" to go back to IIS Manager

Deploy old SQL Reporting Services Reports to SharePoint Site

Overview

In my previous post, I discussed about how to setup SharePoint Integrated SQL Server Reporting Services (RS). Now that I have the Integrated RS ready, I can deploy the reports to the SharePoint site. But wait, how can I deploy a Pre-SP2 RS report to SharePoint site? Below shows the steps I used.

My RS reports consists of two files: a Report Definition file (.rdl) and a Data Source file (.rds). To deploy a Pre-SP2 RS report to a SharePoint site, I have to upload the Report Definition file, and create a new SP2 Data Source file (with extension changed to : .rsds).

Reference

1. Deploying Reports, Models, and Shared Data Sources to a SharePoint Site
2. How to: Create and Manage Shared Data Sources (SharePoint Integrated Mode)
3. How to: Add Report Server Content Types to a Library (SharePoint Integrated Mode)

Steps

Upload Report Definition file

1. Create a New Document Library (or use an existing one) in SharePoint site to store the Report Definition file and the Data Source file.
2. Upload the Report Definition file (.rdl) of your report to the Document Library created in Step 1.

Add "Report Data Source" Content Type to the Document Library

1. Before we can "New" a Data Source in SharePoint Document Library, we must first add the "Report Data Source" Content Type.
2. Navigate to the Document Library, select "Settings" -> "Document Library Settings"
3. Select "Advanced Settings"
4. Select "Yes" for "Allow management of content types", click "OK"
5. At "Document Library Settings", select "Add from existing site content types" under "Content Types" Category
6. Make sure that "All Groups" is selected for "Select site content types from:", then select "Report Data Source" Content Type and click "Add"
7. Click "OK"

Create a Data Source file in Document Library

1. Navigate to the Document Library, select "New" -> "Report Data Source"
2. Fill in the properties of the Data Source, I opened my old data source in visual studio and copy all the properties to the new one.
3. Click "OK"

Bind the Report Definition file to the new Data Source

1. Navigate to the Document Library, click to display the drop-down list of the Report Definition (.rdl) File
2. Select "Manage Data Sources" in the drop-down list
3. Click on the name of the Data Source to edit it.
4. Specify the URL of the newly created Data Source (.rsds) file in "Data Source Link" and click "OK"
5. Click "Close" to go back to Document Library, click on the Report Definition file to see the result. If a report is displayed successfully, then the deployment is done!

Guides on setting up SharePoint integrated SQL Server Reporting Services

Overview

This is going to be a long post, in this post I am trying to describe how I setup Integrated SQL Server Reporting Services on a Standalone SharePoint Server. According to my understanding, these steps can be applied to SharePoint Services too.

Software Configuration

• SQL Server 2005
• SharePoint Server 2007
• Microsoft .NET Framework 3.0 Download

Reference

Guides to install and configure

1. How to: Configure SharePoint Integration on a Stand-alone Server
2. Configuring Reporting Services for SharePoint 3.0 Integration
3. How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration)
4. How to: Configure the Report Server Integration Feature in SharePoint Central Administration

Information on MOSS integrated SQL Server Reporting Service

1. Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007
2. Installation and Configuration Guide for SQL Server Reporting Services - SharePoint Integration Mode (Word format)

Steps

Prepare the software package

1. SQL Server 2005 Service Pack 2 Download
2. SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies Download

Install software package

1. Install .NET Framework 3.0 (My server originally has this installed)
2. Install SQL Server 2005 SP 2
3. Install SQL Server 2005 RS Add-in for SharePoint

Create a Report Server DB for SharePoint Integrated Mode (Check Reference 3 for details)

1. Go to "Report Services Configuration" in SQL Server 2005 "Configuration Tools"
2. Connect to the Instance you want to create the Report Server (Mine is "MSSQLSERVER")
3. Click "Database Setup" on the left pane, then select the Server you want to connect and click "Connect"
4. Click "New" button next to the Database Name.
5. In "SQL Server Connection" dialog box, enter the name for the new Database, make sure to select the "Create the report server database in SharePoint Integrated mode" check box.
6. Click "OK"
7. On the Database Setup page, specify the Credential you want to connect to the report server, Click "Apply"

Configure Report Server Integration Feature in SharePoint Central Administration (Check Reference 4 for details)

1. Go to "SharePoint 3.0 Central Administration"
2. In Central Administration, select "Application Management", then click "Manage integration settings" under "Reporting Services"
3. Specify the "Report Server URL" and "Authentication Mode", normally the Report Server URL will be something like "http://server-name:8080/reportserver" where port number should follow your configuration but not always "8080". I use "Trusted Account" for "Authentication Mode"
4. Click "OK" to go back to "Application Management", then select "Grant database access".
5. Specify the "Server Name" and "Instance" of the Report Server (In standalone case the server name should be the same as the SharePoint Server).
6. In the pop up windows, specify the "User Name" and "Password" to connect to report server. Enter the administrator account here. Then click "OK" twice to go back to "Application Management".
7. Select "Set server defaults", if you enter this page successfully, then it means that SharePoint connect to report server successfully. Actually I didn't change any settings here, and the report service works fine in SharePoint Site.

Problems in deploying / debugging SQL Report Server Project: Permissions granted to user 'domain\username' are insufficient (rsAccessDenied)

Overview

I came across an issue that the project created from "Report Server Project Wizard" Template in Visual Studio did not deploy successfully and the "permissions granted to user 'domain\username' are insufficient for .... (rsAccessDenied)" error was shown.
Finally I was able to get it done by restoring the IIS 'ReportServer' Active Directory. Below shows the steps I follow to do the restore.

Reference

"The permissions granted to user '' are insufficient for performing this operation." when trying to view Report Site.

Steps

1. Click "Start" -> "All programs" -> "Microsoft SQL Server 2005" -> "Configuration Tools" -> "Reporting Services Configuration"
2. Enter the "Machine Name" & "Instance Name" and click "Connect"
3. Select "Report Server Virtual Directory" at the left pane and check "Apply default settings", and click "Apply"
4. Done!!!

Set the Start Up display Form in Access 2007

Overview

In my previous blog showing How to convert Access 2007 App into Runtime Mode, I discuss how to convert Application to Runtime Mode. However, you need to specify a Start-Up Form so that the Runtime Application have something to show when started.

Reference

Change Access 2007 database options for your application

Steps

1. Fire up Access 2007 and open your application (NOT in Runtime Mode)
2. Click the "Office" button
3. Click the "Access Options" button, then choose "Current Database" in left pane
4. Click the "Display Form" drop-down list and set your start-up form
5. Finish!

Convert application to Runtime Mode in Access 2007

Overview

Access Forms let you create a user-friendly interface to end-user, it can also restrict what your users can view/modify. However user can still modify your table directly if your application is not run in Runtime Mode. Below shows the steps to convert your Access 2007 application to runtime before you put it to production.

Reference

Convert your Access 2007 application from design to runtime mode by changing its extension

Steps

1. Open the folder that contains your application (with .accdb extension)
2. Ensure that extension is shown in filename (this can be set in "Folder Options"),  rename the application to have .accdr extension (e.g. "myApp.accdb" rename to "myApp.accdr")
3. Finished!

Customizing Web Parts Outlook with XSL Editor

Overview

Out-of-the-box web parts in MOSS is very useful in development. However some of their looking might not fit your needs. However, the looking and style of web parts can be customized by editing the XSL(Extensible Stylesheet Language) file.

Steps

1. Navigate to the Page containing the Web part you want to customize.
2. Choose "Site Actions" -> "Edit Page"
3. At the title of Web part, click "edit" -> "Modify Shared Web Part"
4. At the Web part Tool Pane just opened on the right, under the "Data View Properties" category, click the "XSL Editor..." button to open a text editor containing XSL of the Web part.
5. As the so called "XSL Editor" is just a very simple text editor (without Search Function), better copy the XSL inside to Visual Studio for editing. To do this, just new a file in VS and select XSLT as the template.
6. When you finish editing the XSL in VS, copy it back into the "XSL Editor" and press "Save", then click "Apply" in the Web part Tool Pane to see the change.

Embed SQL DB connection ID & password into BDC Definition File

Overview

(From MSDN: BDC Overview) Business Data Catalog (BDC) is a new business integration feature in Microsoft Office SharePoint Server 2007, it enables Office SharePoint Server 2007 to surface business data from back-end server applications without any coding.

Business Data Catalog provides built-in support for displaying data from databases and Web services. That is, you can use Business Data Catalog to display data from your SAP, Siebel, or other line-of-business (LOB) application via Web services or databases.

Water-blowing

(By me) BDC is a very good tool in connecting SharePoint with other system (DB or Web Services), it is good because the information collected from BDC application can be displayed in some out-of-the-box (but not good looking...) webparts, and be searched just like other information in SharePoint (Yes~ you can search your BDC connected DB in SharePoint!), you can also make site columns based on BDC information and embed it into content type too! One example is to make a SharePoint generated Word Document able to crawl data from DB thru BDC, quite amazing....

Besides all these exciting features, writing a BDC application is quite a pain in the xxx (Sorry I can't control myself for this). The Microsoft claimed "NO CODE" experience is equivalent to writing a XML file with several hundred to thousands of lines. This infernal XML file is called (Application Definition File).

Connecting to DB in BDC - SSO

Writing the damn Definition File is one thing, getting your application to connect to remote DB is another mountain to climb. I used to get it work by using Single Sign-On in MOSS, which is VERY difficult to setup, and required the server to be connected to an Active Directory.

Recently I come across this method to embed connection UserID and Password into the Definition File, so no more SSO nightmare~~ (This method is obviously NOT the best practice and should be used in development environment ONLY)

Code Snippet

<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property>

<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property>

<Property Name="rdbconnection Data Source" Type="System.String">ServerName_Here</Property>

<Property Name="rdbconnection Initial Catalog" Type="System.String">DataBaseName_Here</Property>

<Property Name="rdbconnection Integrated Security" Type="System.String">SSPI</Property>

<Property Name="RdbConnection Pooling" Type="System.String">false</Property>

<!--For Passing Login & Password Directly-->

<Property Name="RdbConnection User ID" Type="System.String">UserID Here</Property>

<Property Name="RdbConnection Password" Type="System.String">Password Here</Property>

<Property Name="RdbConnection Trusted_Connection" Type="System.String">false</Property>

In the above Code Snippet, actually what you have to customize is "ServerName", "DataBaseName", "UserID" & "Password", and the authentication should work.

Note that I am using SQL Authentication here, so make sure to SQL Server you are connecting to have SQL Authentication enabled and have a matching UserID and Password.