• MS Query, DSN, Password location

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » MS Query, DSN, Password location

    Author
    Topic
    Cecil Rhoades
    Guest
    April 17, 2001 at 9:24 pm #1769123

    XL97, NT 4.0, Oracle8i(I think)

    I have created a query which retrieves data from an Oracle DB. When creating the query I am prompted for login info, i.e. UserName, Password, Server. When I refresh, I am not prompted. If I store the query on a network drive and use a UNC to access query, then when I open the spreadsheet from any computer, I can refresh data without logging in from the new random location.

    I know for a small fee of $15US I can get a program to crack Excel passwords. Is the Oracle login now just as (un)secure?

    Thanks.

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSShane Sargent
      AskWoody Lounger
      April 18, 2001 at 10:46 pm #1782339

      First, I know nothing about Oracle security, so grab a salt shaker. When you go to other PC’s to access the network based query, are you prompted to log in at the first PC you’re at? And do you sign into the other PC’s as yourself, or make the user slide over and pretend to be them as far as your Oracle server is concerned?

      Reply | Quote
      • Cecil Rhoades
        Guest
        April 19, 2001 at 12:58 am #1782347

        We did not set up a trust relationship between NT and Oracle. If I click the desktop icon twice, I log in twice, and the password is different (in most cases the username is the same for NT and Oracle). (Is that what you were asking). So far I have only logged into the other PC as me. I will log in as someone else tommorow and try it (Glad I thought of that 🙂 )

        Also, If I access Oracle from a new workbook, even using the same Query or DSN, even during the same Excel session, I must log in again. I also will try the same query from another sheet in the same workbook.

        Excel is remembering the Username and Password, and transmitting it each time. Apparently it is remembered on a QueryTable (Sheets(n).QueryTable(n)) basis. The issue, as I see it, is how easy will it be to get the Oracle username and password from Excel. I ment to try my MS Office password cracker on it today, to see if it found not only Excel sheet passwords, but ODBC passwords as well. I will also try that and let you know.

        Thanks for the response.

        Reply | Quote
    • WSkieran
      AskWoody Lounger
      April 19, 2001 at 10:43 am #1782364

      The Password, UserID and server ID is stored in the query as plain text.
      Any half decent text editor can view the query content(whether or not it is embedded in Excel or otherwise). Look for ‘UID=’ , ‘PWD=’ or ‘SERVER=’ and you will see.

      This is known at our site, so we only create public queries using a public read only user logon to Oracle.
      No external access is allowed to Oracle so the user must be on the local network.

      The alternative is to amend the query properties (Data Range Properties in Excel – access it by right clicking on the returned data range) to clear the ‘save password’ option.

      Microsoft does indicate somewhere (I can’t remeber where that exteranl database user ids and passwords are not encrypted)

      I hope this helps.

      Reply | Quote
      • Cecil Rhoades
        Guest
        April 19, 2001 at 3:51 pm #1782371

        The first place I looked is in the query and DSN files (*.dsn and *.dqy). Here is what I found,
        DRIVER={Microsoft ODBC for Oracle};UID=Name;SERVER=Machine; (obviously I changed the UserName and Server Name). However, the password is not here.

        What is interesting is, If I have a workbook with a QueryTable on sheet 1, and I refresh the data, I am not prompted for login. If I go to a new sheet, or even a new location on the same sheet, and do a “Data, Get External Data, Run Database Query” and run the same query, I am prompted to login.

        I just now, while writing this note, did what I should have done earlier. I opened the Excel file with a Hex editor and searched for the password. Sure enough, there it is, the entire query embedded in the spreadsheet in plain text, complete with Username and Password.

        Therefore, I thank you for the help. You are entirely correct. Our own employees cannot find the data themselves even when it is put in front of them, but any college intern or temp we bring in can have access to all the company secrets. Heh! What a deal.

        Thanks again.

        Reply | Quote
    Viewing 1 reply thread
    Reply To: MS Query, DSN, Password location

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.