PivotView WebPart in MOSS 2007 accessing OLAP 2008 Cube over HTTP

So Tim, a colleague of mine, recently upgraded our databases from SQL Server 2000 to SQL Server 2008. Needless to say a few things changed. After the migration, we were unable to get a pivot view webpart to connect to our OLAP Cube via HTTP(S). We could get it to connect directly to the database fine, but when we went over HTTP nothing would happen. Well I would like to explain how to access SQL Server Analysis Services 2008 on Windows Server 2008 and IIS7 from MOSS 2007.

First you need to make sure you have a user that can access Analysis Services. You will use this user as your Application Pool Identiy in IIS7. This user needs read permission to the cube and all of its dimensions.

I recommend creating a DNS CNAME to the database server, so as not to put the name of the database server in the connection string.

For the first steps, I will reference Mike's Blog This blog will go through creating the virtual directory and application pool in IIS7. Now, I found a few things that need to be added to the steps in this blog.

1) Make sure that only Windows Authentication is enabled on the virtual directory.
2) Set the identity of the application pool to a user who has the necessary access to the cube (readonly recommended).
3) Finally, the MSOLAP.4 (2008 version) provider does not work for some reason. We were unable to figure out why. We were able to successfully connect using the MSOLAP.3 provider.
4)I also recommend only using SSL. If the cube needs to be access from an external source, then HTTPS will secure the transmission. If the external source is a non-domain joined computer then you will need a publically trusted cert.

All that being said, the process should be at this stage:

1)A cube is setup
2)A virtual directory (or website) is setup on the same server as the SQL Server instance. The HTTP site can be setup on a different server, but in our case, we just put it on the local machine.
3)A site within MOSS is allocated for the PivotView WebPart

To set the webpart up, you must know the following: The name of the cube and the url to OLAP server.

To Add the web part, edit a page in MOSS and select Add WebPart. In the list of WebParts you will notice a hyperlink at the bottom right for the Advanced webpart gallery. Choose this.

Navigate through this gallery until you find the PivotViewWebPart. Once you add the webpart, choose to modify this shared webpart. If you expand the advanced section of the webpart settings you will see the place for the connection string. There is one step after this so keep reading.

The connection string should look like this:

Provider=MSOLAP.3;Persist Security Info=False;Initial Catalog=;Data Source=https://olapserver.domain.com/virtual directory with OLAP HTTP files/msmdpump.dll

We played with a number of connection strings, but we finally settled on this one and here is why. MSOLAP.3 is the only provider we could get to work with OLAP 2008. The connection string does not contain any usernames or passwords because we are using Windows Authentication to connect to the website and then the application pool is making the connection to the cube. Finally, Persist Security Info is set to false, this insures that no usernames or passwords (security info) is passed either way during the connection. Now, the users first have to authenticate to the website which proves they are a member of the organization and have permission to see the cube, and this is the only way they can connect, which prevents any rogue connections to the database.

The final step for setting up the webpart is to add the name of the cube, not the data warehouse, to the field Query String.

Once you click save, the webpart should be populated with a pivot table of the cube.

There is one final note. We were able to get the connection working and then we started QAing it before we rolled it out. The issue we would see is that one sysadmin could connect fine and see all of the data, but the other sysadmin with identical rights everywhere could only see some data or none at all. This took us hours to narrow down. We finally ran across a few forum posts and verified that Windows 7 RC has trouble connecting this way. For whatever reason, it only works partially. We verified this and verified that we could access from Windows 7 RTM, Vista and XP. Gotta love Office Web Components.

Multiple Login Prompts when Logging into SharePoint from Vista or Windows 7

I have finally been able to put this issue at rest. It is actually a rather simple fix. On the client machine the MOSS url needs to be added to local intranet security settings in IE. At least each subdomain that will be accessed. The easier way would be to add http://*.domain.com.

The second piece is a registry modification. The issue occurs because of the way Vista and 7 handle WebDAV authentication with MOSS. To allow the connection to be made navigate to HKLM\SYSTEM\CurrentControlSet\Services\WebClient\Parameters and add the mult-string key AuthForwardServerList. Add the same list of domains you put in Local Intranet in this location. So http://*.domain.com and https://*.domain.com. You might have to restart the client computer.

I have reproduced the problem and resolved it with these steps a number of times now.

MySharePoint Sites in Vista

--Update 5/6/09
This solution does not appear to work. It only worked on some machines, and has just reverted to the previous issue on my local machine. So I might have not actually changed anything rather something else might have caused the issue to temporarily go away. Back to the drawing board.

Below is the solution I thought worked. After testing it on a few machines, it failed to work consistently.

I have seen a lot of chatter about My SharePoint Sites and Windows Vista, but no real solutions. I am also affected by the issue. Today I decided to try something I came up with after reading a number of articles and taking little pieces from them.

The last fix I tried was this one MSDN Blog but it did not work for me.

I remembered seeing an article that mentioned deleting the timestamp registry entry in HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Portal but that also did not work for me.

So I started looking around in the registry and I read an article in WindowsIT Pro about SharePoint Office Links.

When I looked at the keys in each folder in HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Server Links\Published\My Site I noticed that IsPublished was set to 0 though IsMember was not. Just out of curiosity I changed it to 1. At that point, I was able to create a new from existing document by going to My SharePoint Sites as well as Save As the same way.

I cannot explain why this works, perhaps someone else can, but it fixed the issue for me. The only issue that remains is that it asks me to authenticate when I click on My SharePoint Sites. That is my next hurdle to resolve.

I am running Vista x64 with IE7 and connecting to MOSS 2007 Enterprise.

Any explanations as to why this works would be great as well as any other feedback.