locked
Cannot find data source while adding a XML mapping data source RRS feed

  • Question

  • Product : Excel 2007 (also unseccesfully try with Excel 2010)

     

    I had a look at the following features :

    http://office.microsoft.com/en-ca/excel-help/import-xml-data-HP010206405.aspx?CTT=3
    http://office.microsoft.com/en-ca/excel-help/overview-of-xml-in-excel-HA010206396.aspx?CTT=3

    Usecase :

    A XML file is hosted on a web server using authentication. In a new workbook, I add a datasource from the web using the data tab. I am prompted my login/password. Once authenticated, the preview is filled with the XML body. Once clicked on "import", I'm promted whether I want to fill the current sheet or add a new one with the data. I choose to add a new worksheet and click OK. A new worksheet is added to the workbook and filled with data from the XML file. I can right click on any cell in the table to update the data.

    Then, I close the workbook. If I open it later and try to update the data, it fails displaying an error message : "Cannot find the data source" just as if the given URL is not a valid one. If I unzip the workbook package and open the connection file, the URL stored in it is the one provided; If I copy it and open it in any browser (IE, firefox, opera), the XML is displayed after authentication.

    We tried to store the XML file on Apache and IIS web server. On IIS, we tried to change the authentication method (windows, simple). This was unsuccessful too. Note that independantly from the web server and the authentication method, it was always possible to access the XML file from a browser and data upate in Excel always failed after having closed the workbook once.

    We also tried to manually change the connection setting according to ECMA  376 Office Open XML standard in the xlsx file. It hasn't worked yet.

    We cannot afford disabling authentication as a safe access to data is required. I'havn't read much in MS knowledge about security and most about MS Sharepoint facilities.

     

    Many thanks

    Monday, July 5, 2010 10:55 AM

Answers

  • Here's an update for this thread. This KB Article (http://support.microsoft.com/kb/2123563) was just recently published and may be the answer.

    "Enable Secure Sockets Layer (SSL) encryption on the web server (Recommended)
    Office 2010 applications will be able to access and download files by default from a web server that utilizes Basic Authentication over a Secure Sockets Layer (SSL) connection. "

    The article refers to Excel 2010 but it may also apply to 2007 (not confirmed). Can you try enabling SSL encryption on the web server and see if the error no longer happens?

    Will

    Thursday, July 29, 2010 10:40 PM

All replies

  • db_plw, I've a few questions that I'm hoping you can answer:

    1. What is the exact text of the error message you are receiving when it displays the error message?

    2. Is the XML file located on a Sharepoint site and if so is this 2007 or 2010?

    3. Are you using a DSN to establish the connection to the data source or just a URL?

    Will Buffington
    Microsoft Excel Support

    Thursday, July 8, 2010 5:28 PM
  • 1/ My first try was on an Excel 2007 French edition. I also tried on an English one. When I try to refresh the data by cliking on Refresh on the contextual menu of the table, I get a security warning that tells me that Excel wants to access remote data, I choose to go on. Then the following error message is displayed :

    Dialog Caption : "XML Import Error"

    There is a table with 3 columns :

    * Source : URL to my XML file http:://myservername/testjf/pdf/exemple.xml

    * Error : "Datasource not fount"

    * Import Status : "Failed"

    Of course no data uis refreshed since Excel seems not able to connect to the datasource

    2/ XML file is only located on a web server (IIS 6.0 or Apache) and connection with credentials is always possible from a browser after logging in in default HTTP authentication window

    3/ I am using a URL that matches the following pattern : http://mymachine/mydirectory/myfile.xml. This URL is working from a webbrowser.

     

    db_plw

    Friday, July 9, 2010 8:07 AM
  • Okay what kind of authentication method are you using on that server? Anonymous, Basic, Windows...?

    Will

    Friday, July 9, 2010 8:27 PM
  • Hello

    We tried using "integrated windows authentication", "basic authentication" and "integrated windows authentication + basic authentication". I just tried "Digest authentication for Windows domain server". All of them do work when querying with a web browser but fail when trying to update with Excel.

    db_plw

    Monday, July 12, 2010 6:35 AM
  • Okay, I've set this up using Excel 2007 and Windows Server 2008 R2 using IIS 7.0. Doing a web query to a virtual directory with the name http://servername/virtual_dir_name/simple.xml

    I've tried both Basic Authentication and Windows Authentication. I chose to do a web query from Excel 2007 and pointed it to the URL above. I was prompted for credentials and entered those, then was allowed to complete the import. The data came in. I then saved the spreadsheet, closed Excel and re-opened the spreadsheet, enabled the data link and I was not prompted for authentication again. My assumption here was that Excel was not properly storing the login credentials, but even when my login failed I received a different error than the one you mentioned. My error was "Unable to open <server location>. Cannot download the information you requested".

    Here's a few more questions for you so that I can narrow this down a bit further.

    1. When you entered the credentials in Excel, did you check the box that said to remember the user name and password?

    2. When you re-open your spreadsheet, are you prompted to enter credentials again or does it just fail straightaway with the error you mentioned?

    3. If you go into inetmgr on the server (the IIS Manager) and look at the authentication rules for where you are sharing the XML file, do you have any users specified that have access or are "All users" set to have access?

    4. Is this server on a separate domain than the one you log into?

    Will Buffington
    Microsoft Excel Support

    Monday, July 12, 2010 11:43 PM
  •  

    I hope the following answers will help you :

    1/No I didn't checked the box to remember credentials. Besides, we don't want credentials to be saved for security concerns as they are stored as plain text in the OOXML package.

    2/When I re-open the spreadsheet, I am not prompted to enter the credentials when trying to refresh the data. It fails straightaway and I get the error message

    3/Users of the computer belongs to different group of users (Admins, Basic users, IIS lauch user...). The one I'm using in my test bench belongs to the basic user group that can read and execute, list folder content and read. I think the problem I'm currently facing is that I don't get a prompt to give my credentials. Since I don't save the credentials, there is no reason why user group rights could change this behavior, isn't it ?

    4/I'm not sure since my network administrator is not here (I'll ask him later to double check) but I think that our machine do not belong do domains. Moreover, when I authenticate (first time in Excel when importing the XML ou in a web brower, I never gave a domain name).

     

    Regarding the error message, I double check and I really get  "Datasource not found" differen from yours.

     

    db_plw

     

     

     

    Tuesday, July 13, 2010 7:08 AM
  • If this Excel file is an XLSX format, could you try saving it as an XLS and then let me know if the problem still occurs?

    Also, is this spreadsheet using a separate header file by any chance?

    Will

    Tuesday, July 13, 2010 4:28 PM
  • Regarding your hints :

    * I tried to save it as a XLS file (Excel 2003 or previous versions) and the problem still occurs. Is there a version of Excel that makes things work properly ? I would be happy to have a workaround in a first time but our problem here is that we expect to have an Excel 2007 file that integrates authenticated working data sources.

    * When you mention separate header file, do you mean header like in header and footer ? I havn't specified neither header nor footer (In page layout). If you don't refer to page layout, how can I check there is no separate header file.

    Do you manage to have a working authenticated data source or it fails like in my scenario ?

    db_plw

     

     

    Monday, July 19, 2010 7:22 AM
  • Here's an update for this thread. This KB Article (http://support.microsoft.com/kb/2123563) was just recently published and may be the answer.

    "Enable Secure Sockets Layer (SSL) encryption on the web server (Recommended)
    Office 2010 applications will be able to access and download files by default from a web server that utilizes Basic Authentication over a Secure Sockets Layer (SSL) connection. "

    The article refers to Excel 2010 but it may also apply to 2007 (not confirmed). Can you try enabling SSL encryption on the web server and see if the error no longer happens?

    Will

    Thursday, July 29, 2010 10:40 PM
  • This article is quite interesting.

    It tried the workaround and it does work, even with Excel 2007. But i dont think my customers administrator will either want to change their office security level in the registry on each client machine nor allow the user to do so.

    I would prefer to work on the server configuration. I tried to enable the SSL secured encryption. In IIS 6.0, I created a Request for Certificate and I sign it with a CA issed by ourselves. I know that it's not the highest security level but it's the way my customer would do it themselves. Once the certificate added to IIS, I can connect to the XML (through a secured URL https://mymachine/mywebsite/file.xml). I get a warning that no trusted authority has signed the certificate, but I can accept to display the file.

    Would it be working with a certificate issued by a trusted authority ?

    I can also in Excel create a new file with a datasource, the data source being the https URL of the XML file. I'm prompted my credentials. Once entered, I'm warned that the certificate is not safe but I can go a step forward. The data are loaded. If I close the file and open it again, I get the error message aforementionned.

    Have you managed to get it working with a configuration close to our ? (IIS 6.0, Excel 2007 instead of Excel 2010)

    Is there a way to configure the server to have it working (128 bit encryption ...) ?

     

    db_plw

    Friday, August 13, 2010 3:08 PM
  • Hello

     

    I investigated configuration of IIS with secured connexions. I also tried the following

    * Use of a self-signed ssl certificate, registering this as a trusted certificate in internet explorer. This way, I got no warning message neither in MS IE nor in Excel when updating data on the first time.

    * Use of a certificate issued by a trusted authority. I got no warning message neither in a web browser nor in Excel on the first data update.

     

    Both solution didn't work. I still got the same error message

     

    Best regards

     

    db_plw

    Monday, August 16, 2010 2:14 PM
  • I'll test with Office 2010 to check wether it may be a version uncompatibility .

     

    db_plw

    Monday, August 16, 2010 4:04 PM
  • Hello

     

    I spend a week checking the different possibilities (IIS or Apache, Excel 2007 or 2010)

     

    * Excel 2007 works only if I change teh client security level or I choose to authenticate through SSO (Kerberos or IIS Windows integrated authentication) with a domain

    *Excel 2010 works in the cases I mentionned hereabove and works also with SSL (on Apache or IIS, with self-singed or certificate authority signed certificates).

    Thank you for your help

    Monday, August 23, 2010 3:04 PM
  • Hi Will,

    I may have the same problem but not sure if the situation I have is the same as the issues from this post.

    I have a number of reports built on Excel 2003 (also still on XP).

    In Excel 2003 I was able to use XML mapping (Data --> XML --> XML Source) to bring in data from a SharePoint 2007 list.

    The report runs a macro to automatically refresh the data and used in various areas of the report.

    We have just recently moved in Excel 2010, which I must admit I am still trying to figure out but it looks like when the macro is run, an error occurs "Initialization of data source failed."

    After trying to look up how to get around this issue, possibly a patch or maybe new VBA code - I have come short of finding anything online remotely related.

    I decided I would try to just recreate the XML Map however when I do to XML Source, I see nothing there that indicates a SharePoint Services lists like it did in 2003.

    I guess I have two questions:

    1. Is the reason I do not see the option to connect to a SharePoint list because of the version of 2010 I have or am I missing a setting?

    2. Ultimately, the first question should be can I even import data from a SharePoint list into Excel 2010?

    Note that the report is used by a number of different users all of who have access to the SharePoint list that the data is being imported from.

    Any insight would be greatly appreciated.

    Thanks,

    Anil

    Wednesday, June 6, 2012 12:49 PM