Excel Login Follow-Up

Last Friday I talked about using a special user to allow Excel to login in to database. In response it was pointed out that passing credentials via the URL in this way is no longer supported by versions of IE with cumulative Security Update #832894 installed. The idea being to stop Phishers & co. from spoofing your bank's URL. If you tried to open a URL, like the one I posted, using IE you'd see this Invalid Syntax error (note it's not a Domino error):

Worry not though. This doesn't affect the solution. According to the How Things Work Department of my brain it's because Excel doesn't use IE to fetch the URL. Well, maybe it does in one way or another, but it doesn't seem to mind passing the user name and password.

However, there is a problem if you happen to be using session-based authentication on your server, as Curt pointed out in another response. If so, this method won't work anyway. Instead you have to pass the username and password as extra parameters in the query string, like so:


Another response pointed out that this is a "huge" security risk. I really don't think so and can't see how. The danger is that another use, who would otherwise be Anonymous, somehow accesses the user's PC and finds this URL. This they can do by searching the users internet cache folder for the cached .IQY "file", which they can open in a text editor and find the password. To do this they would need to know that this trickery was happening in the first place and how to look for and open this type of file. They would also need to be logged on to Windows as that user or as one with Administration rights. It's a small risk and, unless security is of the utmost importance, not one I'd lose sleep over. If they can access the user's cache they can read most of the content of the database anyway, unless it's made not to cache. Maybe they could see the URL in Domino logs? Again, only if they happen to have Admin rights.

Talking of cacheing, there's one more installment on these .IQY issues to come...


    • avatar
    • Caroline
    • Mon 31 Jan 2005 01:51

    Blimy Jake, what time do you get up in the morning?! It's not even 8am on Monday so I'm surprised to see Monday's blog already!

    • avatar
    • Jake
    • Mon 31 Jan 2005 02:11

    What time do I go to bed more like!

    I've started posting them just before I go to bed, so that folks in Oz get chance to read them on the say day as well. Sometimes this means posting them just after midnight. Sometimes I cheat and post them early, before altering the date.

    • avatar
    • Colin
    • Mon 31 Jan 2005 03:21

    Hey, what about us Kiwis?! We see the sun before they do!

    • avatar
    • Jake
    • Mon 31 Jan 2005 03:28

    You're part of Australia though aren't you?

    • avatar
    • CAroline
    • Mon 31 Jan 2005 04:04

    You need a publishing workflow thing with an agent to set it live at midnight on the international date line, then you can sleep easy knowing we all read it on the right day, except Americans who can't sleep and read it a day early. You probably can't please everyone!

  1. Kiwis are New Zealanders I think, am I right?


  2. Just an idea....

    What if did the following "security through obscurity" hack? In the $$NavigatorTemplate for ExportData, add an iframe that's source is

    www.domain.com/reports.nsf/loginpage?OpenPage&username=excelreader&password=pass123 where loginpage is simply a blank page or some other placeholder.

    Set the iframe to display:none.

    This way, you could open www.domain.com/reports.nsf/ExportData?OpenView and the iframe will compute and authenticate in the backend.... and the user's won't see a UID/PW in the URL. Not hack-proof, but a "cleaner" approach IMHO.

    Let me know what you think!


    • avatar
    • Jake
    • Mon 31 Jan 2005 09:53 AM

    You missed the point Chris. The URL is already hidden from the user as it's only ever accessed by Excel and so never really "visible".

    Plus, you would need to be logged in to open the page with the loginpage in it. So you wouldn't get that far anyway. Domino would simply return the login page.

    • avatar
    • laurens
    • Mon 31 Jan 2005 04:38 PM

    The url will be visible in Excel under Data > Get external data > Edit Query.

    But I have to admit, got no better solution yet.

    • avatar
    • Jake
    • Mon 31 Jan 2005 04:52 PM

    Laurens. In Excel 2003 I don't see the URL anywhere in any of the import external data options. I just see my normal IE history. Altough I *do* see the .iqy files in the *MY* recent documents list in the Import Data dialog. Nothing too worrying really.

    • avatar
    • Colin
    • Mon 31 Jan 2005 05:29 PM

    @Jake - part of Australia? *cringe* We're close, but not THAT close! We get some 3 hours of daylight before any Aussies "see the light" (says I with Australian ancestry).

    @Serdar - yep, thats us!

    • avatar
    • Jake
    • Tue 1 Feb 2005 02:07

    Colin. I was pulling your leg.

    • avatar
    • Andrea
    • Wed 30 Nov 2005 10:07 AM

    Maybe this is an hard work to do but.. what about this ?

    When the user click on the download link we ask him/her username and password. We create a new excel file and we save username, password, url, etc.. in some cells. This file has a VBA script to run a web quey and the user credential.

    The uses open the file and ..voila !

    Of course the credentials data are hidden and when the web query is done we delete the data.

  3. I tried to use your solution, but id doesn't work.

Your Comments


About This Page

Written by Jake Howlett on Mon 31 Jan 2005

Share This Page

# ( ) '


The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

Let's Get Social

About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »

More Content