PDA

View Full Version : Locking Code to a Specific Machine



aerodoc
10-12-2011, 09:30 AM
What is the best way to lock a file to a specific machine? My thought it to use an If/Then statement with the worksheet open. The file would have the allowed machine ID (or other) embedded in it first.

The big question is what is the right "key" to look for (i.e. machine id, math id, hard drive?) I know some of these things might change while others may not. Also, I would need a utility or something for the customer to first send back to me. I suppose this could be embedded in the Excel file itself? The workflow would go something like:

- User opens the key generator Excel file (or other compiled .exe) to generate a unique "key".
- I add this to key to the list of allowable users.
- I distribute the updated Excel file.

Any bits of code to help?

JP2112
10-12-2011, 12:17 PM
How about MAC address?

http://p2p.wrox.com/access-vba/42695-retrieve-system-information-vba.html

aerodoc
10-12-2011, 03:20 PM
Seems a little more complicated than I would like...

"Here is network adapter info (there are a lot of these on each adapter so be prepared to add a conditional to remove things like WAN MiniPort etc to get the one you want):"

Any simpler method? It doesn't need to be perfect, just pretty good. Simpler is better.

shrivallabha
10-13-2011, 09:30 AM
Maybe following:
Sub TestEnviron()
Dim sComputerName As String
sComputerName = Environ("COMPUTERNAME")
'Search code for computername here
End Sub

Paul_Hossler
10-13-2011, 02:14 PM
You could create a GUID and store it in the registry the first time.

There after, just check it.

Not perfect since Regedit will see it

Depends on how secure you want to be



'If you need a unique identifier that is certain to be unique across all applications and all computers and all users,
'you can create a "Globally Unique Indentifier", or GUID. This identifier is universally unique. Note that while this
'value will be unique, there is no meaningful information contained within it. That is, you cannot examine the value
'of a GUID and discern any information about when or where it was created. The code for creating a GUID is shown below:
'You can then call this function with code like:
'Dim UniqueID As String
'UniqueID = CreateGUID()

Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
' 111 1111 1112 222222222333
'{12345678-9012-3456-7890-123456789012)
'{000204EF-0000-0000-C000-000000000046}

Public Function CreateGUID() As String
Dim G As GUID
Dim s As String
With G
If (CoCreateGuid(G) = 0) Then
s = _
String$(8 - Len(Hex$(.Data1)), "0") & Hex$(.Data1) & _
String$(4 - Len(Hex$(.Data2)), "0") & Hex$(.Data2) & _
String$(4 - Len(Hex$(.Data3)), "0") & Hex$(.Data3) & _
IIf((.Data4(0) < &H10), "0", "") & Hex$(.Data4(0)) & _
IIf((.Data4(1) < &H10), "0", "") & Hex$(.Data4(1)) & _
IIf((.Data4(2) < &H10), "0", "") & Hex$(.Data4(2)) & _
IIf((.Data4(3) < &H10), "0", "") & Hex$(.Data4(3)) & _
IIf((.Data4(4) < &H10), "0", "") & Hex$(.Data4(4)) & _
IIf((.Data4(5) < &H10), "0", "") & Hex$(.Data4(5)) & _
IIf((.Data4(6) < &H10), "0", "") & Hex$(.Data4(6)) & _
IIf((.Data4(7) < &H10), "0", "") & Hex$(.Data4(7))
CreateGUID = "{" & Mid(s, 1, 8) & "-" & Mid(s, 9, 4) & "-" & Mid(s, 13, 4) & "-" & Mid(s, 17, 4) & "-" & Mid(s, 21, 12) & "}"


End If
End With
End Function


Paul

aerodoc
10-25-2011, 11:40 AM
Paul,

I called the GUI using:

Sub Show()

Dim UniqueID As String
UniqueID = CreateGUID()
Cells(1, 1) = UniqueID

End Sub

But it is not constant (multiple execution of the macro yields different values). That wouldnt work though.

Kenneth Hobs
10-25-2011, 12:57 PM
Using the Environment variable Computername should do it.

If you wanted a unique ID other than the computername, then the GUID method could be used. They would run your macro which could email the GUID to you and add it to their registry. That would be specific to both the user and the computer.

To get and set registry entries, this method is easy. http://j-walk.com/ss/excel/tips/tip60.htm

aerodoc
10-25-2011, 08:15 PM
Ken,

I would like to try the GUID method. I used the code in post #5 and ran it with the code from post #6. But re-execution generates a DIFFERENT GUID (I was expecting it to be constant).

I don't really get the connection from the link you posted.

I would think I just need to user to generate a fixed (constant) GUID, which they would sent to me, then I would add that to the file and check against it. But it won't work if the GUID bouncing all over the place (as it is in the code from posts 5/6)

Kenneth Hobs
10-26-2011, 06:44 AM
I left out the send by email and added MsgBox's to show the data in the user's registry. A method is included to remove the registry entries.

'Registry Path: HKEY_CURRENT_USER\Software\VB and VBA Program Settings
Sub CreateAndSaveGUIDandSend()
Dim d As Date, masterFilename As String, AppName As String

masterFilename = "Master.xlsm"
AppName = "Master"

If ThisWorkbook.Path = "" Then
MsgBox "You must save this workbook to the folder where you will use " & AppName & ".", _
vbCritical, "Macro Ending - Save and Run Again"
Exit Sub
End If

d = GetSetting(AppName, "Date", "Value", 0)
'If True that Date was not in registry, install date and GUID to registry
If d = TimeSerial(0, 0, 0) Then
SaveSetting AppName, "Date", "Value", Now
SaveSetting AppName, "GUID", "Value", CreateGUID
End If

'Show Date and GUID
MsgBox "Date/Time: " & GetSetting(AppName, "Date", "Value", 0)
MsgBox "GUID: " & GetSetting(AppName, "GUID", "Value", 0)

'Email Date and GUID

End Sub

Sub RemoveGUIDandDate()
Dim AppName As String
AppName = "Master"
DeleteSetting AppName
End Sub

Paul_Hossler
10-26-2011, 07:30 AM
That's the way GUID works, new one every time. Once you have it, you need to remember it and check:

I'd do something like this ..

When the WB is opened

Check registry (GetSetting)

'first run
1. if blank or empty, then

1.1 create a GUID and store it in the registy (SaveSetting)
1.2 Create a hidden name (Names.Add visible = false) to store the GUID

2. If not blank, then

2.1 Get GUID that was saved above (GetSetting)
2.2.Check against the hidden name
2.3 Continue if match or display message if not

Again it's not very fool-proof since RegEdit will display and allow the user to delete the GUID, but if you use registry enteries that don't raise interest, you should be ok

All WBs would test against the same GUID, unless SaveSettings/GetSetting used different GUIDs for each workbook

If you want I'll do a simple WB and post it

Can you explain why the email might be needed? The above pseudo-code should handle it all automaticily

Paul

Kenneth Hobs
10-26-2011, 08:56 AM
If you want to hide the registry key(s) in a less obvious place, use one of these registry methods:

1. http://www.vbaexpress.com/forum/showthread.php?t=19060
2. http://www.cpearson.com/excel/registry.htm

In the end, you are only making it a bit harder for those with minimal cracking skills.

aerodoc
10-26-2011, 01:44 PM
I think I am confused about how a GUID works then, but here is my thought:

You have two public files:

1 - A file that generates a code that is machine specific and constant. Perhaps another way to do this is to use a combination of constants such as "Computer Name + ... + "
2 - Another file that has no key (again this file is made public). Upon running, it checks for a key. If there is no key, it attempts to make a web query for the key.
At this point, the user would have emailed you the key and you add it to a list of license codes that can be accessed via web query.

Upon running the public file, it will close if there is no valid key (or could not be found via web query). If it is found, then the key is added to the Excel file and that excel file is now permanently attached to the key (machine).

Once setup, then maintenance should be easy. The user just sends you the key and you add it to a list and upload to the host domain. What do you guys think about this?

P.S. If there is not a good constant key from the user machine, then you could use simpler constants and then scramble them. That way, the user is sending you "junk" so they wouldn't know what information was actually collected. In other words, it would be difficult to know how to duplicate that information on a separate machine (and would exceed the pain threshold for just about anyone).

aerodoc
10-28-2011, 08:38 AM
http://www.vbforums.com/showthread.php?p=1585637

This looks like a good approach, but is in VB, not VBA. Could anyone convert this to VBA?

Kenneth Hobs
10-28-2011, 12:16 PM
Did you run the routine to know if it is good? It is not worth the effort in my opinion. That code is designed for vb projects. An Excel file is not an EXE program. There is nothing wrong with using parts of that code but the other methods here work just as well. I detailed the steps that you can use.

I would not force a user to have an internet connection to run my program other than to email their GUID, Date, and/or UserName types of detail to setup their Excel file to run. I would then email them the workbook made just for them with the lock information.

Due to many different email programs, I would not even do all of the email process for them. I would put the contents that I need to lock it into the clipboard and tell them what was done and to paste that in the email and show it in the setup workbook. I might run a Mailto just to open their email program with my email address. I might just put my email address into the setup workbook and then the other info as well. Obviously, I would obfuscate some of the data so that they could not search the registry for the generated GUID and such. The basic idea is to keep it simple but yet meet your needs for some modicum of intellectual protection.

If I get time this weekend, I will see if I can make two workbooks to illustrate the concept as it is a fairly common FAQ on the various Excel forums.

aerodoc
10-28-2011, 12:37 PM
Ken,

- I agree with the web query. I don't think that is such a great idea after all.

- I also agree that you don't want/need to do the email process for them. The code alone is sufficient.

- I am not too fond of sending them a unique workbook though (even though it is a good stop gap solution). This could be time consuming if you have many clients. Also, what happens if want to give them updates, etc. It could be a lot of headache. The other approach has an embedded algorithm and therefore the workbook is constant (as well as the key generator). You would only need to send them the key one time and updates, etc. would take care of itself.

Kenneth Hobs
10-28-2011, 01:10 PM
The problem with pre-setting acceptable key values is that if one user got a key, he could share it with others.

If the user emails key value(s), and you use an email program like Outlook that can watch for emails with a specific subject line, you could auto-respond and automatically send them a modified workbook just for them. That kind of thing is a bit off-topic for this forum and best handled in an Outlook forum. I was going to do a project like that once in vb.net.

You can check for modified dates by looking at the last modification date on open. If less than the current data/time, date/time was rolled back. If the current date/time is greater than the workbook's hidden named range value for creation, you can tell the user that it expired and close it.

aerodoc
10-28-2011, 01:45 PM
Ken,

I think we are a bit out of sync. Since I don't understand the GUID, lets look at how it works with a Hard Disk Lookup.

- There is an EXCEL file that finds the HD serial number, which then sent via email.
- Using this serial number, you use an encryption algorithm to generate an encrypted key.
- The original EXCEL file that finds the HD serial number can also be used to place the encrypted key somewhere. It can placed in the registry or even as an ascii file in an obvious location. It really doesn't matter where you place it because you are not trying to hide it (though the registry would be better).
- The actual EXCEL file you are protecting is now run. It asks for the key, decrypts it, and checks to see if it matches the HD serial number. That is how it locks it to the machine, regardless of whether another user has the key or not.

Does that make more sense? So you have two public files (one to get the HD serial and place the encrypted key) and another public file that only works if the encrypted key and HD serial match.

The link to the VB forum goes over this on the first post as well and it seems that it would meet the objective.

Kenneth Hobs
10-28-2011, 02:18 PM
There is no reason to email anything in that case. On the first run, whatever method used: GUID, Computername, UserName, Hard Drive Serial Number, etc. is encrypted and stored whereever. When the user runs the 2nd workbook, the encrypted key is searched for.

You still have to deal with the macro enabled issue as I explained.

aerodoc
10-28-2011, 03:10 PM
Yes, that would "work". But if you did that, the user would be able to generate the encrypted key for any machine. You want to have control over the ability to generate keys, which is the security feature.

Paul_Hossler
10-29-2011, 08:15 AM
The way I'm reading this is along the lines of a 'one time sw registration'

User opens WB first time

WB is not yet 'registered' so userfrom#1 asks "Register Y / N"

If "Y" ....

WB emails HS serial num to aerodoc, who 'encrypts' is and send it back

Users enters the 'encrypted' key back into userform#2, which stores the encrypted key in registry, and closes WB


When user opens WB again, WB_Open checks registry for encrypted key, and get HD serialnum and encrypts that with same algorithm and compares

If they match, WB open (no more emails, no warning, etc)

If they don't match, userform#3 show error msg, what ever

Obviously, this is not very secure, since VBA protection can be broken, the user could disable events so WB_Open doesn't run, the algorithm in the VB link is not very cryptographically robust, etc.

:dunno Not sure about the cost-benefits

Paul

aerodoc
10-29-2011, 09:30 AM
Paul,

That method would work too, but that seems to be more code intensive?

I like the approach with 2 files:
File 1 - Two functions. (a) create HD serial number to be sent and (b) store the encrypted key in registry or file.
File 2 - Main workbook that checks if the encrypted key matches the serial HD.

That way you split up the coding, which may be easier to work with (and less chance of breaking).

- As far as VBA protection, I use a method that is pretty secure. It takes the existing project and creates a new file that does not allow access to the VBA. In fact, I cannot even get into it after it is "compiled". It is far more secure than just a VBA password, which can be easily defeated.

- As far as cost benefit goes, I need a method to protect my work. Otherwise, I won't continue the project with VBA. I would be forced to go with another language to take it to the next level. Getting this code to work seems to be the last trick to making Excel VBA a viable solution for me.

Paul_Hossler
10-29-2011, 10:25 AM
I was thinking along the 'trialware' business model with only one file to worry about.

I don't think the difference is coding would be significant (easy for me to say)

"creates a new file that does not allow access to the VBA"

Interesting -- How dod you do that?

Paul

acraens
10-29-2011, 12:22 PM
I don't want to interfere in this thread but could you send an example of your way of secure your excel files.

Would be really appreciated.

Thanks
Alex


Paul,

That method would work too, but that seems to be more code intensive?

I like the approach with 2 files:
File 1 - Two functions. (a) create HD serial number to be sent and (b) store the encrypted key in registry or file.
File 2 - Main workbook that checks if the encrypted key matches the serial HD.

That way you split up the coding, which may be easier to work with (and less chance of breaking).

- As far as VBA protection, I use a method that is pretty secure. It takes the existing project and creates a new file that does not allow access to the VBA. In fact, I cannot even get into it after it is "compiled". It is far more secure than just a VBA password, which can be easily defeated.

- As far as cost benefit goes, I need a method to protect my work. Otherwise, I won't continue the project with VBA. I would be forced to go with another language to take it to the next level. Getting this code to work seems to be the last trick to making Excel VBA a viable solution for me.

aerodoc
10-29-2011, 01:22 PM
How about we save the worksheet VBA protect for another thread. I will start/discuss that after we this first part worked out. Both are necessary to make it all work.

-------------------------------

Paul,

I think the single file is OK too. But I fear it may be confusing to the user? The two file approach provides a clear indication that step 1 must be done before file 2 works.

With the single file approach, it is more compact, but requires that "get into" the file multiple times. The user may be confused about why this needs to be done. Just my thought anyway. Personally, I like the "clearly distinct" 2 file method.