Password Protect a Database connection

[LeggoMyEggo]

Hello All,

I am looking to password protect a database password that I have stored in my excel file. (in the properties section of the Workbook Connections wizard)

I do not want to password protect the entire file, because my file is designed to run some VBA code that involves a database download when opened. It is completely autonomous because it runs as a scheduled task, but I need some more protection if it encounters an error.

Also, protecting the sheet where the connection is downloaded does not work.


Thanks,
Alex

 

[Ruddles]

How secure does it have to be? If you just want to protect it from prying eyes, you could munge it before you store it, then un-munge it when you need to use it.

 

[LeggoMyEggo]

It doesn't need to be impenetrable, just safe from prying eyes, as you mentioned.

So, can you give me a little more detail on how to "munge" my password in excel?

I read the wikipedia article you linked to and I'm unsure as to how making a different password would protect my file from people just opening the connections box and looking at it... That's what I'm really worried about.

Thanks for the reply!
Alex

 

[Ruddles]

Set it up so the password isn't saved but provided when required by your VBA routine. Is that possible?

A very simple munge for test purposes:-

Option Explicit
 
Public Function SimpleMunge(ByVal aClear As String) As String
 
  Dim cPtr As Integer
 
  For cPtr = 1 To Len(aClear)
    SimpleMunge = SimpleMunge & Chr(287 - Asc(Mid(aClear, cPtr, 1)))
  Next cPtr
 
End Function

You'd run your real password through this function once to obtain the munged version by entering ?simplemunge("secret") in the Immediate window and then when you want to use the password, you'd use simplemunge("¬º¼­º«") instead and that would unmunge it back to "secret".

Does that make sense?

 

[LeggoMyEggo]

My real question is actually the question you just asked me!

"Set it up so the password isn't saved but provided when required by your VBA routine. Is that possible?"

Or in other words:
How can I provide a database password using VBA...?

That's what I'm trying to ask, sorry that is was muddled...


Thanks,
Alex

 

[Ruddles]

Sorry! Can you show the code you're using to connect to the database?

 

[LeggoMyEggo]

I can't really provide the SQL code for accessing the database, as I believe it's probably a copywrite thing, but I can tell you that I am just using an:

Activeworkbook.refreshall

to get it to refresh.

It is an SQL query that I set up manually in the connections dialog box (by using Microsoft Query...)

It's the "save password" checkbox that I have enabled within the connections properties that is the main problem. It lets me run my program automatically, but lets anyone who looks see my password.


Thanks,
Alex

 

[Ruddles]

In that case I think the only way would be to change it so that the connection is established using VBA. That way your password isn't stored as plain text anywhere.