I wanted to evaluate the new features of Excel 2007 regarding cubes, so I
set up a test machine with it.

I do have SSAS 2005 SP1 running through IIS, and several users connect with
Excel 2003 - works nice. The IIS is set to use Windows Authentication only,
so my users have to enter a username and password of an account on the
server to get their data. The user accounts are enabled in SSAS with
different access settings. Works really fine!

With Excel 2007, I cannot connect anymore, I get an error 0x80040e4d. This
error is mentioned within groups as (DB_SEC_E_AUTH_FAILED) several times,
but no answer really fits to Excel 2007 and Olap - some are with Jet, ADO,
ODBC but nothing "recent" pointing to Excel 2007.

I am doing nothing different that I did for almost a year with Excel 2003,
in fact I just need to take one step to the next system and can connect with
Excel 2003 using the same account, server, http address and all. I did try a
couple of accounts, passwords and so on to make sure that is not my problem
or that typing of password was wrong.

As side note, if I set the IIS to allow anonymous access (and have IIS map
to a user the SSAS knows) I do get access with Excel 2007, so it is REALLY
the authentification that fails.

Any explanation?

I am afraid I do not know what software you mean...

I know I need to install the OLE-DB Analysis 9 driver, which in turn
requires the MSXML 6 driver to make Excel 2003 work.

I usually download these from Microsoft.com, I find them by typing "SQL
Feature Pack April" in search window. There is also a feature pack dated
November 2005, I do not know if these are different from the April 2006
versions, I always use April versions anyway.

But I installed them, I am quite sure about that since I wanted to test
without first, just to see if they are part of Office 2007 now. Without them
did not work, so I installed them both as I do for Excel 2003. After adding
them, it still did not work though...

Is that what you mean? Or are these SQL 2005 some special additions to
Office 2007 already?

"Bjørn T" schrieb...

Can you try installing the SP2 CTP2 (Tech Preview) OLE-DB Analysis 9 driver
instead? This strikes me as something that may have been fixed in SP2...

SP2 CTP2 is available from below:
http://www.microsoft.com/sql/ctp.mspx

There is a link to the feature pack from there...

One thing you may want to try is to use regsvr32.exe to re-register the DLL
msolui90.dll -- usual warnings apply...

HTH,
Akshai

--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Ralf Mayer" <Ralf.Mayer1 (AT) gmx (DOT) de> wrote

[Excel 2007 will not connect to SSAS]

I was not able to get it working until now.

I made a totally clean install of OS and Office 2007 and used MSXML, OLAP9
and Native Client from the Service Pack 1 version - no good.
I then went for the CTP SP2 Version. (I did check the file dates were
updated to make sure they did install) - no good. I only installed SP2 to
the client though, I left the server untouched on SP1.

I did try to re-register msolui90.dll, but that fails with an error. The
error meaning was something along the that this type of dll cannot be
registered (wrong file?)
I did repeat that on my day-to-day work machine instead of a test system,
and there it DID register the dll... very strange, the test system is a
totally clean install and *should* be more predictable than my daily work
system.

I am out of ideas really. Google seems to list quite some posts now
regarding Excel 2007 and OLAP, most of them using "sharepoint services", but
there seems to be a bigger problem somewhere.

Can someone confirm he can connect from Excel 2007 to SSAS? (on different
machines in different domains over http using Windows Authentication and
giving the credentials of a server-recognized user)


"Bjørn T" <bti (AT) ementor (DOT) no> schrieb im Newsbeitrag
news:1164291932.438947.154510 (AT) l12g2000cwl (DOT) googlegroups.com...
The Feature pack also contains the SQL Server Client (Native Client)
tools. You should install it.

RE
BT



Akshai Mirchandani [MS] skrev:

Even I am facing same issues. Let me give scenario...

We have a two boxes :
1 : SQL Server with Analysis Services
2: MOSS 2007

We are having issues with connection to 2nd box from first box.
Although we are able to
connect to normal SQL Database but the connection to Analysis service
has been failing
after several retries.

The connection is performed with Administrator Account(domain account)
with all permissions to
database

I am attaching the exception text which we are getting.

Cannot connect to the server provided in the connection string:
Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security
Info=True;Data Source=msbucnc-2;Initial
Catalog=MySiteAccountManagerRole.
Microsoft.AnalysisServices.AdomdClient.AdomdConnec tionException: The
connection either timed out or was lost. ---> System.IO.IOException:
Unable to read data from the transport connection: An existing
connection was forcibly closed by the remote host. --->
System.Net.Sockets.SocketException: An existing connection was forcibly
closed by the remote host at
System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32
size, SocketFlags socketFlags) at
System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset,
Int32 size) --- End of inner exception stack trace --- at ...
....System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset,
Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32
offset, Int32 count) at
Microsoft.AnalysisServices.AdomdClient.DimeRecord. ForceRead
Failed on constrained delegation. Exception:
System.ArgumentException: The connection either timed out or was lost.
---> Microsoft.AnalysisServices.AdomdClient.AdomdConnec tionException:
The connection either timed out or was lost. --->
System.IO.IOException: Unable to read data from the transport
connection: An existing connection was forcibly closed by the remote
host. ---> System.Net.Sockets.SocketException: An existing connection
was forcibly closed by the remote host at
System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32
size, SocketFlags socketFlags) at
System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset,
Int32 size) --- End of inner exception stack trace --- at
System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset,
Int32 siz...
...e) at System.IO.BufferedStream.Read(Byte[] array, Int32
offset, Int32 count) at
Microsoft.AnalysisServices.AdomdClient.DimeRecord. ForceRead(Stream
stream, Byte[] buffer, Int32 length) at
Microsoft.AnalysisServices.AdomdClient.DimeRecord. ReadHeader() at
Microsoft.AnalysisServices.AdomdClient.DimeRecord. .ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader. ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.G etDataType()
--- End of inner exception stack trace --- at
Microsoft.AnalysisServices.AdomdClient.XmlaClient. EndRequest() at
Microsoft.AnalysisServices.AdomdClient.XmlaClient. CreateSession(ListDictionary
properties, Boolean sendNamespaceCompatibility) at
Microsoft.AnalysisServices.AdomdClient.AdomdConnec tion.XmlaCli...

...entProvider.Microsoft.AnalysisServices.AdomdCli ent.AdomdConnection.IXmlaClientProviderEx.CreateSe ssion(Boolean
sendNamespaceCompatibility) at
Microsoft.AnalysisServices.AdomdClient.AdomdConnec tion.ConnectToXMLA(Boolean
createSession, Boolean isHTTP) at
Microsoft.AnalysisServices.AdomdClient.AdomdConnec tion.Open() at
Microsoft.SharePoint.Portal.WebControls.KPIAuthWor k.Execute(Object[]
args) --- End of inner exception stack trace --- at
Microsoft.SharePoint.Portal.WebControls.KPIAuthWor k.Execute(Object[]
args) at
Microsoft.Office.Server.ApplicationRegistry.Infras tructure.WindowsAuthenticator.ExecuteAfterConstrai nedDelegation(Object[]
args) at
Microsoft.SharePoint.Portal.WebControls.KPIHelper. OpenConnectionAndCube(ODCData
connectionInfo, AdomdConnection& asConnecti...
...on, CubeDef& cube, Boolean remapSsoCredsExceptions)
Initiating BDC Cache Invalidation Check in AppDomain 'DefaultDomain'


Ralf Mayer wrote:

"Ralf Mayer" schrieb im Newsbeitrag...

Recap, it is still unsolved:
[Excel 2007 cannot connect to SSAS 2005 SP1, Excel 2003 works. Error given
is 0x80040e4d (DB_SEC_E_AUTH_FAILED).
SQL Native Client, MSXLM6 and OLAP9 have been testet in SP1 and CTP SP2
Version, no success, no change.
Disabling Windows Authorization in IIS (Cubes are hosted in IIS) allows
connection, so it is really that Excel 2007 cannot authorize itself to IIS]

As said above, the problem is still not solved for me, but there are some
interesting points to add:
If I have Excel 2003 and 2007 installed together, and use 2003 to open a
connection and close Excel 2003 completly afterwards, I can connect with
2007 as well. Even without providing a user name or password.
So if Excel 2003 "clears the path" or "puts down a token cookie" or
whatever, Excel 2007 can work with this open door for around 20 minutes...
after some time, it goes "kaputt" by itself, as if a cookie expired.

After Excel 2003 opened the path, I can even change the windows password of
the user that the connection is made through. The new password is valid and
allows connections immedeatly, but the old one also allows connections for
said 20 minutes.

That puzzles me a lot, tbh...
Ralf