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

The only way to make it really secure is not to hand out the sensitive data at all. Instead use a client/server approach where you send a request from client to server. The server then can process the input data and return only the result the client asked for. So the client only receives a part or result of the data but no sensitive raw data. — Pᴇʜ
I haven't tried this but could you not setup an ODBC connection and use that to connect to the database? Immediate draw back I can think of with this approach is that you will then have to setup this connection for every user.. but it should hide your connection details — Zac
Why not just restrict database access at the database user level and set up appropriate "public" permissions? That way if your users happen to bypass the password and get the credentials they can't do anything they wouldn't be able to do through your front end. — Comintern
Maybe this is a solution for you excel-pratique.com/en/vba_tricks/vba-obfuscator.php — Storax
This might be another solution: How to securely store Connection String details in VBA — Pᴇʜ

GoodJuJu GoodJuJu · Accepted Answer · 2018-09-26T13:06:54

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

VBA Password Protection

2 Answers