OWC Interop Security Error - resent

[cct]

Dear all,

I am confuse with ASP.net security in related to
Microsoft.Office.Interop.OWC.

My server setting:-
Windows Server 2003 + SP1
SQL Analysis Services 2000 + SP4
dotnet framework 1.1 + SP1
oxppia.exe
OWC10 (version=10.0.6619.0)

web.config:-
<authentication mode="Windows" />
<identity impersonate="true" />

IIS Authentication Method:-
Integrated Windows Authentication (ONLY!)

Sample Code (c#):-
protected OWC10.PivotTableClass oPivot = new OWC10.PivotTableClass();

System.Xml.NameTable oNT = new NameTable();

System.Xml.XmlNamespaceManager oXNM = new
System.Xml.XmlNamespaceManager(oNT);

oXNM.AddNamespace("x","urn:schemas-microsoft-comffice:excel");

oReportXmlDom.SelectSingleNode("//x:ConnectionString",oXNM).InnerText =
"Provider=msolap.2;Data Source=localhost;Initial Catalog=[FoodMart 2000];";

oPivot.XMLData =
oReportXmlDom.DocumentElement.SelectSingleNode("dataxml").FirstChild.OuterXml;
(NO ERROR!)

OWC10.PivotRowMember oRowMember =
((OWC10.PivotRowMember)oPivot.ActiveData.RowAxis.Member.get_FindMember(szPath,OWC10.PivotMemberFindFormatEnum.plFindFormatPathName));
(ERROR !!! System.Runtime.InteropServices.COMException: Unspecified ereror)


Any Help or Advice is highly appreciated.

cc

 

[cct]

But if Analysis Services database roles is set to allow everyone to access
the cube,
then everything work fine.

 

[Wei-Dong XU [MSFT]]

Hi,

From your code and the troubleshooting by setting the anonymous at SQL
analysis service, this error should be caused by the MS office OWC
Pivottable control when connecting to OLAP.

At this line code, the connection string is specified to the OWC pivottable
control.
oReportXmlDom.SelectSingleNode("//x:ConnectionString",oXNM).InnerText =
"Provider=msolap.2;Data Source=localhost;Initial Catalog=[FoodMart 2000];";

Then Pivottable will connect to OLAP for retrieving the data. At this
scenario, the server running the OWC code works as the client requesting
the data from OLAP. So the impersonated setting in ASP.net can help
nothing; then since the OLAP is set banning anonymous access, the request
will fail then. When you enable anonymous access at OLAP, this code will
work very successfully.

This is due to the MS OWC is designed to work as one client ActiveX control
working at desktop. So when we use it at server-side, it will still
establish one connection to OLAP and the server works like the client box
sending the connection request.

One workaround here is we can hardcode the user name and password in the
connection string of Pivottable. Then the connection will be permitted by
SQL analysis service.

Please feel free to let me know if you have any question. It is my pleasure
to be of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.