SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel 2007 Connect to SSAS 2008 - An error was encounted in the transport layer


Excel 2007 Connect to SSAS 2008 - An error was encounted in the transport layer

Author
Message
YearMonthDay
YearMonthDay
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 148
Dear All,

I having an issue when using Excel 2007 connect to SSAS 2008.

Client

1. Window XP SP3

2. Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

Server

1. SSAS 2008 & Window Server 2008 Enterprise 64 bits

When I try to connect to SSAS 2008 using Excel 2007 by providing the correct username and password, but I getting an error as "An error was encounted in the transport layer". When I click "OK", "multidimensional connection 10.0" wizard display. Follow the wizard by prodiving the same username and password and it connected to the cube successfully.

I even try on the same SSAS Server by using Excel to connect to cube, but getting same error.

Anyone know that:-

1. Why I getting an error as "An error was encounted in the transport layer"?

2. Why I only can connect to cube using "multidimensional connection 10.0"?

Thank in advanced
YearMonthDay
YearMonthDay
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 148
Hi All,

I was wondering anybody know the root cause?

Thanks
stevefromOZ
stevefromOZ
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: Moderators
Points: 3464 Visits: 3757
You need to provide more detail/s on exactly what you're doing and then what is happening - ie you're connecting from Excel to AS2K8 how? Using the From OTher Sources option on the data tab? Using the Insert Pivot Table on the Insert tab?

Can't explain the 2nd part of what you're seeing, as I don't know what it is that you're doing. The first part, is likely caused by authentication failure. you can confirm this by running the profiler on the AS instance, you'll likely see a connection attempt from an Anonymous user.

It also sounds like you're entering/typing User/Pwd in the dialog/wizard - generally, you don't do this when in an intranet situation, your credentials are/will be passed across. This was mainly used when connecting to the dll exposed through IIS when in an Internet scenario.

HTH,

Steve.
YearMonthDay
YearMonthDay
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 148
Hi,

Sorry for late reply.

Below are the details, how I connect to SSAS using Excel:-

1. MS Excel 2007
2. Data Tab --> From Other Sources --> From Analysis Services --> Data Connection Wizard appread
3. Enter the "Server Name". Log on credentials --> Check "Use the following User Name and Password" (Enter the Administrator User Name and Password) --> Next
4. Select Cubes --> Next
5. Click --> Finish
6. Error Message --> An error was encountered in the transport layer.
7. Once I click OK --> "Multidimensional Connection 10.0"wizard appread
8. Enter the User ID and Password --> Next --> Finish
9. Pivot Table show in the Excel

Hope you can provided some adviced.

Thanks
thijs.nijhuis
thijs.nijhuis
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 72
Hi,

I am experiencing the exact same problem when trying to connect to an SSAS 2008 R2 instance. Did you ever get this fixed and, if so, what was the solution?

Kind regards,
Thijs Nijhuis
dbradish
dbradish
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 78
Read Devarajan from Microsoft's response from here --> http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/45d6a242-b501-44c9-aec0-1032a38f07eb

"
this seems to be a feature limitation in PowerPivot, We haven't provided custom impersonation capabilities as it's always current NT user while connecting to SSAS. There is a workaround for this, you can run excel using runas to launch excel using that credential.

runas /user:domain\username excel.exe

This will launch excel with the user are trying to impersonate and it will let you do the job you want.

Once you finish creating model and publishing the model, you can schedule refresh job using any nt credentials.

"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search