MS Query, DSN, Password location

Topic

New Reply

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

Replies

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

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

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

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