Excel 2007 Pass-Through Windows Authentication

I've created a simple (asmx) web service which returns a DataSet. I've added the webservice to my Excel 2007 workbook using the Data -> From Web button and I'm able to view / refresh the data.

The problem comes when I need to secure the web service: I've turned on Windows authentication for the web service and the request uses SSL.

Unfortunately, the user's logged on windows credentials aren't used by Excel when trying to refresh the data - the refresh fails.

If I click on Data -> Connections -> Properties -> Definition -> Edit Query, only then am I prompted for my windows credentials and does the refresh then succeed.... not a problem for me, but not something I want every user of this spreadsheet to h开发者_JS百科ave to do... any ideas how to make the prompt come up when the refresh is attempted instead of having it fail??

Thanks!!

Update Answers so far are to do with SharePoint and Excel Services (neither of which are any use to me)... and one link for which "The following procedure does not apply to data that is retrieved from a text file or a Web query"... I just want a person with a copy of excel on his desktop machine to be able to update from a password-protected web service... is that so hard Microsoft??

Another Update Still no answers accepted - because no answers so far have provided a working solution ( Nice googling though - thanks guys ;-) )

While I haven't got SSL I can attest that Excel normally shouldn't ask you for authentication when using pass through authentication.

My guess is that you will need to add the destination website (with the https) to your trusted zone in IE. The effect should be that when you go to the website you shouldn't be challenged for your password at all. IE will now pass through the authentication credentials because the destination is in the trusted zone.

Once this is fixed Excel should treat it like a normal website.

Here's a link which talks you through adding your site to the trusted zone: http://www.nateirwin.net/2007/01/19/enabling-ntlm-authentication-in-firefox-and-internet-explorer/

The last time I dealt with this issue was in 2004. If I remember correctly, this is a bug in the Web Query technology in how the query deals with the SSL certificate. This is Excel 97 technology; therefore, fairly basic implementation.

After much research and troubleshooting, the only way around this issue is to create user and password parameters and post the web query. Using POST will keep the user/password hidden from prying eyes.

Following is my note from 2004: There is a problem with https, application/vnd.ms-excel, Internet Query (iqy), and Excel 2000/2002.

Have you checked out this question: What do I need to do to make Excel access a Web Query via HTTPS?

Excel's Web Queries Enable You to Populate Worksheets from Web Sites at http://msdn.microsoft.com/en-us/library/aa155714(v=office.10).aspx.

Sites requiring authentication and passwords provide additional challenges. They may require coded workarounds or may be unsolvable.

Error message when you use Web query to a secure Web page () in Excel: "Unable to open" at http://support.microsoft.com/kb/290347.

XL97: How to Create Web Query (.iqy) Files at http://support.microsoft.com/kb/157482 is an invaluable resource. (There was a Web Query SDK once that I cannot find, but this article is a good replacement.)

Different Ways of Using Web Queries in Microsoft Office Excel 2003 at .

I don't know if this will help, but I faced a similar situation while importing data from a remote SQL Server Database. What I did was create a role inside the database itself, and assign any users who needed access to that role.

The data is updated into the workbook when the file is loaded using Microsoft Query, so I don't know how that might differ from how you have done things.

The biggest issue with doing it this way was to open the properties for the query and check the "Use Trusted Connection" box. This worked without an issue for me. Again, this was from a remote server, not a secure website. Hope this helps.

i hope this will help you : Refresh connected imported data

We had a similar situation at work, however, we are using Office 2010. I'm not sure of the limitations of 2007. Check out these links. The last two are specifically for Excel 2007.

Link 1: Configure Secure Store Service for Excel Services

Link 2: Ten Tips for Using SharePoint Server 2007 with Excel Services

Link 3: Plan external data connections for Excel Services