Excel VBA: Secure way to get user's database login/password for connection string?

Question

I have an Excel VBA tool which needs to query Oracle. I am using ADO. When building the connection string, I get the database, username, and password out of cells in the spreadsheet. What is a more secure way to handle the login?

The spreadsheet will probably be emailed and saved on public drives. Assume an ordinary Office Professional 2007 install, so using 3rd party controls is less good.

MikeD MikeD · Accepted Answer · 2010-01-07T07:56:34

Request database, username and password through a dialog window; after successfull connection to the Oracle DB write out database and username to the registry using the SaveSetting() statement.

In the dialog window's Initialize() event preload database and username with the saved values if found, using the GetSetting() function, and move focus directly to password field

On XP/Office 2003 information will be stored in Window's registry under

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\[Appname]\[Section]

IMHO passwords should |: never :| be stored anywhere

Good luck MikeD

Excel VBA: Secure way to get user's database login/password for connection string?

2 Answers