我创建了一个简单的 (asmx) Web 服务,它返回一个数据集。
我已使用“数据 ->”将 Web 服务添加到我的 Excel 2007 工作簿中。通过网络按钮,我可以查看/刷新数据。
当我需要保护 Web 服务时,问题就出现了:我已为 Web 服务打开 Windows 身份验证,并且请求使用 SSL。
不幸的是,在尝试刷新数据时,Excel 不使用用户登录的 Windows 凭据 - 刷新失败。
如果我点击数据 ->连接->属性->定义->编辑查询,只有这样我才会提示输入我的 Windows 凭据,然后刷新是否成功......对我来说不是问题,但不是我希望此电子表格的每个用户都必须做的事情......任何想法如何制作尝试刷新时出现提示而不是失败?
谢谢!!
更新 到目前为止的答案与 SharePoint 和 Excel Services 有关(这两者对我来说都没有任何用处)...以及一个链接,其中“以下过程不适用于从以下位置检索的数据”:文本文件或 Web 查询”...我只希望桌面计算机上有 Excel 副本的人能够从受密码保护的 Web 进行更新服务……微软有那么难吗?
另一个更新仍然没有接受答案 - 因为到目前为止还没有答案提供有效的解决方案(不过很好的谷歌搜索 - 谢谢大家;-))
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 have 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 ;-) )
发布评论
评论(3)
虽然我没有 SSL,但我可以证明 Excel 在使用传递身份验证时通常不会要求您进行身份验证。
我的猜测是,您需要将目标网站(带有 https)添加到 IE 中的受信任区域。其效果应该是,当您访问该网站时,您根本不会被询问密码。 IE 现在将通过身份验证凭据,因为目标位于受信任区域中。
修复此问题后,Excel 应将其视为普通网站。
以下链接将引导您将站点添加到受信任区域: http://www.nateirwin.net/2007/01/19/enabling-ntlm-authentication-in-firefox-and-internet-explorer/
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/
我上次处理这个问题是在 2004 年。如果我没记错的话,这是 Web 查询技术中查询如何处理 SSL 证书的一个错误。这就是Excel 97技术;因此,这是相当基本的实施。
经过大量研究和故障排除后,解决此问题的唯一方法是创建用户和密码参数并发布 Web 查询。使用 POST 可以防止用户/密码被窥探。
以下是我 2004 年的注释:https、application/vnd.ms-excel、Internet Query (iqy) 和 Excel 2000/2002 存在问题。
您检查过这个问题吗:我需要做什么才能使 Excel 通过 HTTPS 访问 Web 查询?
Excel 的 Web 查询使您能够从网站填充工作表,网址为 http://msdn.microsoft.com/en-us /library/aa155714(v=office.10).aspx。
在 Excel 中使用 Web 查询安全网页 () 时出现错误消息:“无法打开”,网址为 http://support.microsoft.com/kb/290347。
XL97:如何创建 Web 查询 (.iqy) 文件,网址为 http:// support.microsoft.com/kb/157482 是宝贵的资源。 (曾经有一个 Web 查询 SDK,我找不到,但本文是一个很好的替代品。)
在 Microsoft Office Excel 2003 中使用 Web 查询的不同方式,位于 .
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.
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 .
我不知道这是否有帮助,但我在从远程 SQL Server 数据库导入数据时遇到了类似的情况。我所做的是在数据库本身内部创建一个角色,并分配需要访问该角色的任何用户。
使用 Microsoft Query 加载文件时,数据会更新到工作簿中,因此我不知道这与您的操作方式有何不同。
这样做的最大问题是打开查询属性并选中“使用可信连接”框。这对我来说没有问题。同样,这是来自远程服务器,而不是安全网站。希望这有帮助。
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.