How to mask excel macro code

Question

I have the below stored in excel macro:-

Sub Sales()

Dim StrSQl As String

Con = "Provider=IBMDA400;Data Source=192.168.2.2;User Id=boss;Password=1we56"

Set Db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
Db.connectionSTring = Con
Db.Open
StrSQl = "select myuc, sum (myac) as Amount from myabc.myqwerty where mydt >= 20100101 and mydt <= 20100831 group by (mycl)"
rs.Open StrSQl, Db, 3, 3
Sheet1.Cells(10, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub

I wish to mask the above User ID & Password ie. User Id=****;Password=***** as part of security.

Is that possible?

Ok but it's not the best option. There are ways to crack it but if the ID & Password are masked, that will provide an extra security. — Bob, Mar 05 '12 at 03:12
@Bob There are ways to crack every type of security, it is unfortunately the nature of things, no system can be created that can't be broken. People will probably need paid programs to crack the VBA project and so are unlikely to bother. — Matt Donnan, Mar 05 '12 at 09:38

brettdj · Accepted Answer · 2012-03-07T04:32:48.417

11

Your most secure option - regardless of whether the user needs to type a password in or not - is to protect your entire macro code

Enter the Visual Basic Editor (VBE)
select the project you wish to protect in the Project Explorer window
right cliick then ....VBAProject Properties
click the Protection tab and then check "Lock project from viewing" and verify your password
Save your workbook, close it and re-open it to establish the protection

Short of writing a COM addin this is secure as your code will get. Be warned that there are products available that will crack VBA code

enter image description here

edited Mar 07 '12 at 04:32

answered Mar 05 '12 at 03:10

brettdj

54,857
16
114
177

1

Good answer as always :) (+1 for the 10th upvote to get the bronze badge). Yet, we can point out this thread: http://stackoverflow.com/questions/259897/how-password-protection-of-excel-vba-code-works which describes how VBA security is poor – JMax Mar 07 '12 at 12:42
@JMax Thx. Quite correct, breaking VBA protection is relatively simple (for current Eccel versions) if someone is determined to go down that path. – brettdj Mar 07 '12 at 23:11

score 2 · Answer 2 · answered Aug 17 '12 at 03:35

Con = "Provider=IBMDA400;Data Source=192.168.2.2;User Id=boss;Password=1we56"

AARRGGHH!! What are you thinking?

Here's the deal. No amount of encryption is going to help you out here because, if Excel itself can unencrypt the data (and it can, otherwise the connection could never be made), then a malicious type can also do it.

The right way to do this is to ask the user for the user ID and password and use that information to dynamically construct the connection string.

That way, the sensitive information exists only in the head of the user and (temporarily) on the machine they're using (which is probably their machine anyway). It's not in an Excel spreadsheet somewhere that anyone can get at.

And, on top of that, functional IDs (shared amongst different users) are almost always a bad idea since it makes auditing a nightmare.

How to mask excel macro code

2 Answers2