Password Protect a Database connection

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2011

• #2

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.

 

Upvote 0

L

LeggoMyEggo

New Member

Joined

Feb 15, 2011

Messages

25

• Apr 19, 2011

• #3

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

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2011

• #4

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:-

Code:

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?

 

Upvote 0

L

LeggoMyEggo

New Member

Joined

Feb 15, 2011

Messages

25

• Apr 19, 2011

• #5

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

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2011

• #6

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

 

Upvote 0

L

LeggoMyEggo

New Member

Joined

Feb 15, 2011

Messages

25

• Apr 19, 2011

• #7

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:

Code:

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

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Apr 21, 2011

• #8

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.

 

Upvote 0