VBA Password Protection

Question

Is there a way to protect VBA Password protection against a crack such as this one:

Is there a way to crack the password on an Excel VBA Project?

Or this one:

https://superuser.com/questions/807926/how-to-bypass-the-vba-project-password-from-excel

I really need to beef up security of my VBA code as it contains sensitive SQL data within!

Thanks

Answer 1

Unfortunately the answer to your question is no. No Excel password is totally secure.

If however your vehicle for data transmittal must be Excel and you are trying to obfuscate SQL connection strings (it sounds like this is the case from your question), there are articles on obfuscating SQL connection strings in Excel which will help. Although the password can ultimately be reverse engineered, it cannot be read as plain text within the connection string and requires a degree of nous to reverse egineer.

Link here and full credit to @Ruddles : https://www.mrexcel.com/forum/excel-questions/512040-data-connector-hide-password.html

In the interest of potential future dead links, I have taken the liberty of transposing the information below:

From Website:

You process the password through a bit of VBA which converts it into a completely different string. You can then hard-code the string into your VBA because if anyone sees it, they won't be able to use it because it's not the real password. Then when you need to use the password in your code, you run it through the reverse process and that returns the original string.

You can make the code as simple or as complex as you wish - but if someone sees the code they may have enough knowledge to reverse-engineer the process and recover the password. It doesn't really encrypt, it just adds an extra hurdle for people to overcome if they want to access it illicitly. On the other hand, it may satisfy your ndatabase security gurus!

Try this: create a new workbook and open the VBE (Alt-F11), then go Insert > Module. paste this code in the code window:-

Code:

Function Obfusc(oWord As String, nCrypt As Boolean) As String

  Dim iPtr As Integer
  Dim iByte As Byte

  For iPtr = 1 To Len(oWord)
    If nCrypt Then
      iByte = Asc(Mid(oWord, iPtr, 1)) + 99 + iPtr
    Else
      iByte = Asc(Mid(oWord, iPtr, 1)) - 99 - iPtr
    End If
    Obfusc = Obfusc & Chr(iByte)
  Next iPtr

End Function

(This is an extremely simple routine - you might want to make it more complex!)

In the Immediate window (Ctrl-R) type ?obfusc("MerryXmas#2010",true) and hit Enter. This will return the obfuscated version of "Hello World!" which is "±ÊØÙáÁ×Ìß Ÿ¡¡".

Now type ?obfusc("±ÊØÙáÁ×Ìß Ÿ¡¡",false) and hit Enter. (You'll probably have to copy & paste that!) This will return the original string.

So in actual use, you'd get the obfuscated string manually by typing
?obfusc("MerryXmas#2010",true) and pasting the obfuscated string "±ÊØÙáÁ×Ìß Ÿ¡¡" into your code like this:- Code:

password = obfusc("¬±ÊØÙáÁ×Ìß Ÿ¡¡",False)
conn_str = "Provider=SQLOLEDB.1;Password=" & password & ";Persist Security Info=True

... etc" That's the general idea.

If you really want to go to town with obfuscation you can follow the 'tutorial' here: How to securely store Connection String details in VBA