Unsuccessful connection from Excel Data Mining Addin via IIS to MS SQL 2012 Analysis Service with attached AdventureWorksDW2012


  • This connectivity issue has been a days-long struggle for me.  I have referred to several sources for a solution.

    I want to connect from the data mining add-in in excel 2010 to a MS SQL 2012 analysis service that is not on the same domain.  I would be very happy if I could get a simple canned solution working.

    Here's what I did:

    1.  I installed MS SQL2012 with Analysis server on Win2008 server

    2.  I successfully attached adventureworks data warehouse and ran through a few lesson solutions to load models:

    I am able to verify these objects in SQL server mgmt studio -> analysis server -> analysis server tutorial -> etc.

    3. I configured HTTP Access to Analysis Services via IIS (anonymous):

    4. I installed the data mining excel addin on a separate PC:

    5. In excel -> data mining -> connection, I added my URL and 'test connection' successfully:

    The problem:

    I click on any item under Excel -> Data Mining (manage models, brows, query, add mining structure, etc.) and receive:
    No default database is available for the selected Analysis Services server connection.

    Excel -> Data tab -> From other sources ->
    Unable to obtain list of tables from the data source.

    What may be wrong: 

    A.  as i mentioned, the two systems are not on the same domain, so something could be wrong with the IIS application pool.  I don't suspect this, but there's just no solid indication that its working, since the success indicated above does not result in a success.

    B.  I added local users and roles.. so many different ways that I lost track.  The login that I use to connect from excel is added in SQL Management Studio -> Database Engine -> AdventureWorksDW2012->Security as well as SQL Management Studio -> Analysis -> Analysis Services Tutorial -> Roles -> Membership; it is a windows login on the win2008 server.

    C.  The analysis server may simply somehow point to the (empty) default 'master' database without regard to my login/role i am using from excel.  I don't suspect this, though the errors say otherwise.

    D.  I may have either forgot to do something, or I did something and removed it.  I have done days-worth of work, so at this point, I am no longer sure.

    I'm very frustrated with piecing everything together after several days without success and would appreciate any step-by-step guidance or troubleshooting assistance.  

    Thanks in advance.

    Update 2013-10-24

    good news is that I got it working with the sample data provided.  i'll have to report back at a reasonable hour of the day to provide detail

    • Edited by Nothing happens Friday, October 25, 2013 3:57 AM notification of working solution
    Saturday, October 19, 2013 1:39 AM

All replies