Post reply

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

  • November 23, 2011 at 9:31 pm

    #251010

    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

  • November 24, 2011 at 8:34 pm

    #1412837

    Hi All,

    I was wondering anybody know the root cause?

    Thanks

  • November 25, 2011 at 7:56 pm

    #1413083

    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.

  • November 29, 2011 at 4:16 am

    #1413903

    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

  • July 23, 2012 at 3:19 am

    #1516117

    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

  • June 5, 2013 at 2:49 pm

    #1621528

    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.

    "

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply