none
Error in using Analysis Services 2005 over http with basic authentication RRS feed

  • Question

  • Hello:

     

    I have enabled Analysis Services 2005 SP2 over http according to the article: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx#EXH. I am using basic authentication on the directory security of the virtual directory for msmdpump application. I am using Excel 2007 to connect to the Analysis Services. I provide the server name as  http://myservername/olap/msmdpump.dll and for username/password, I provide a network username password. Doing this I can connect to the AS database and see the configured cubes. However if I select a cube for Pivot Table, I get the error: 

    "Initialization of the data source failed.

    Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database. "

     

    Has anyone encountered this before? Any thoughts are much appreciated.

     

    Thanks

     

    Tuesday, March 11, 2008 7:51 PM

Answers

  • Have found a workaround

     

    When you create the data connection, in the last step of the wizard ("Save Data Connection File & Finish") there is a tickbox to "Save password"

     

    Select it.

     

    When you see the "Import Data" dialog box, click on properties. Select 2nd tab "Definitions"

     

    The connection string shows the password. The tickbox save password is unticked. Leave it unticked. Select OK and Finish and you should see your data now.

     

    Create a Pivot Table and then save. .

     

    If you now reopen the workbook the connection string is there but no password. So the string is saved to the connection and not to the workbook.

     

    Security not perfect but a lot better thna Anonymous logon.

     

    Monday, March 24, 2008 12:23 PM
  • Another way not to save the password in the file is:

    In Control Panel -> User Acounts -> Manage Passwords (password saved were, where the admin could protect it)

    Add the server with the information of the user (basic authentication)

    In Excel use normal [windows authentication] and point the server to HTTPS msmdpump.dll


    This should works.
    But I've a problem with a proxy server in the middle.

    Anyone as done this with a proxy server between excel and msmdpump.dll?

    Thanks Askmehow
    Monday, May 5, 2008 5:01 PM

All replies

  • I have EXACTLY the same issue...same SQL, same Excel. Funny thing...If you go to IIS and enable anonymous access and then try again it  works!!

     

    Tried turning off windows firewall but doesnt help.

     

    Registering msolap.dll doesnt work either.

     

    Did u find a solution?

    Monday, March 24, 2008 9:35 AM
  • Have found a workaround

     

    When you create the data connection, in the last step of the wizard ("Save Data Connection File & Finish") there is a tickbox to "Save password"

     

    Select it.

     

    When you see the "Import Data" dialog box, click on properties. Select 2nd tab "Definitions"

     

    The connection string shows the password. The tickbox save password is unticked. Leave it unticked. Select OK and Finish and you should see your data now.

     

    Create a Pivot Table and then save. .

     

    If you now reopen the workbook the connection string is there but no password. So the string is saved to the connection and not to the workbook.

     

    Security not perfect but a lot better thna Anonymous logon.

     

    Monday, March 24, 2008 12:23 PM
  • Thanks a lot for your response. I did finally figure out the workaround you found. It seems that after IIS authenticates, there is something else trying to authenticate as well (maybe the pivot control?). The password is not persisted from IIS authentication to this "something else". In the workaround, the password is saved in clear text in the connection file for a user under My Documents.

    I wonder if there is a way to pass the password from IIS to the control that is trying to authenticate again.

     

    Any thoughts...

     

    Thanks

    Monday, March 24, 2008 12:47 PM
  •  

    Hi, I'm having the same scenario but we are on excel 2002. Actually, not I, but my user. When he try to refresh pivot, the same warning pops up. but when I myself tried it, I don't encounter this. I also asked someone to open it on their machine, and they didn't encounter it as well.

     

    any idea?

     

    Thanks

    Wednesday, March 26, 2008 7:01 AM
  • I have seen posts where people have encountered this problem with Excel 2003. You might need to register msolap.dll on your user's machine.

     

    Thanks.

    Thursday, March 27, 2008 3:47 PM
  • Another way not to save the password in the file is:

    In Control Panel -> User Acounts -> Manage Passwords (password saved were, where the admin could protect it)

    Add the server with the information of the user (basic authentication)

    In Excel use normal [windows authentication] and point the server to HTTPS msmdpump.dll


    This should works.
    But I've a problem with a proxy server in the middle.

    Anyone as done this with a proxy server between excel and msmdpump.dll?

    Thanks Askmehow
    Monday, May 5, 2008 5:01 PM
  • I have the same proxy server problem, did you ever figure this out?

     

    many thanks,

     

    John Cullom

    Wednesday, April 28, 2010 1:20 PM