Can I use DPAPI (or something like it) in VBA?

Question

another in my beginnerish series of questions about VBA.

I am in the process of writing an Excel add-in in VBA, and the add-in uses a local configuration file.
This file needs to contain a password for a remote service.
Obviously, it is less than ideal to store this password as plaintext. But I am looking for an algorithm that can encode/decode text so it at least doesn't look like plaintext in the configuration file.

I came across a reference to Windows DPAPI but I'm not sure whether this is an appropriate solution for Excel VBA. I also am not sure how I can use this API from within VBA, as I've only found references to using it with .NET. Visual Studio is unavailable to this project.

So the 2-part question is this:
1) If it is possible to use DPAPI from within VBA, can I have an example of its use?
2) If it is not possible to use DPAPI in VBA, do you have any suggestions for how to store text in some encoded fashion that is reversible?

The solution must work in Excel 2003 and later, if it matters.

Thank you once again.

How "secure" does your solution need to be? That is, what level of trying to figure out the password do you need to be able to resist? Keep in mind that however you do this, it's only going to be as secure as the password protection on your VBA project, since once that's bypassed, your decoded service password is easily accessible... — Tim Williams, Jun 11 '12 at 17:08
Ultimately, for this project, it doesn't really need to be any more secure than "the average joe doesn't see the password when he opens up the config file in notepad". If an "attacker" copies the user's configuration file, then it doesn't matter how secure it is either. It would be nice to be able to use DPAPI, but I could use something far simpler if there's an easier possibility. — Josh, Jun 11 '12 at 17:12
.. but I would like it to be slightly more complex than rot13 or a simple substitution cipher :) — Josh, Jun 11 '12 at 17:16
Can you use binary files instead? There are numerous examples both here and on the 'net of reading and writing binary files using VBA. — JimmyPena, Jun 11 '12 at 17:38
I'd rather not use a binary file format, because I want the option for the user to be able to manually edit the other "non-secure" configuration options (which is pretty much everything, except for this one password). — Josh, Jun 11 '12 at 17:43
Some possible alternatives here: http://rumkin.com/tools/cipher/ No code on that page, but you can easily find examples via Google. — Tim Williams, Jun 11 '12 at 18:22
So you want an editable text file with an encrypted password, something like `First_Name: John;Last_Name: Smith;Password:kjhjHLIUYliyk` where "kjhjHLIUYliyk" is an encrypted representation of the REAL password? In which case, the comment by [Tim Williams](http://stackoverflow.com/questions/10984256/can-i-use-dpapi-or-something-like-it-in-vba#comment14348212_10984256) is especially relevant. I have the encrypted version, all I need to do is hack your VBA project to get the algorithm. — JimmyPena, Jun 11 '12 at 18:46
@JP. I have already stated that I am not concerned if the password is "hackable"; the jumbling of it is mostly for show. But apparently with all this discussion, the main part of my question is "No, there is no access to DPAPI from VBA". — Josh, Jun 11 '12 at 21:52
@Josh: i think I might have a solution for you. Which OS are you using? Also is it 32bit or 64 bit? — Siddharth Rout, Jun 12 '12 at 00:16

score 1 · Accepted Answer · answered Jun 12 '12 at 00:52

The solution must work in Excel 2003 and later, if it matters.

For Excel VBA, I suggest using the CAPICOM Library.

Download the file from here. Once it is installed, follow these instructions for registering the Dll.

32 bit OS

Copy the file Capicom.dll from the C:\Program Files\Microsoft CAPICOM 2.1.0.2 SDK\Lib to C:\Windows\System32

Next on Start Menu | Run , type this

Regsvr32 C:\Windows\System32\Capicom.dll

64 bit OS

Copy the file Capicom.dll from the C:\Program Files (x86)\Microsoft CAPICOM 2.1.0.2 SDK\Lib\X86 to C:\Windows\SysWOW64

Next on Start Menu | Run , type this

Regsvr32 C:\Windows\SysWOW64\Capicom.dll

Now we are set to use it in our VBA Project

Paste this code in a module

Option Explicit

Sub Sample()
    Dim TextToEncrypt As String, EncryptedText As String
    Dim TextToDeCrypt As String, DeCryptedText As String
    Dim KeyToEncrypt As String

    TextToEncrypt = "Hello World"
    KeyToEncrypt = "JoshMagicWord"

    EncryptedText = EncryptString(TextToEncrypt, KeyToEncrypt)
    DeCryptedText = DecryptString(EncryptedText, KeyToEncrypt)

    Debug.Print "The string " & TextToEncrypt & " after encryption looks like this"
    Debug.Print "-----------------------------------------------------------------"
    Debug.Print EncryptedText
    Debug.Print "-----------------------------------------------------------------"
    Debug.Print "The above string after decrypting looks like this"
    Debug.Print "-----------------------------------------------------------------"
    Debug.Print DeCryptedText

End Sub

Public Function EncryptString(strText As String, ky As String) As String
    Dim Cap As Object
    Dim cryptIt

    Set Cap = CreateObject("CAPICOM.EncryptedData")

    Cap.Algorithm = 3
    Cap.SetSecret ky
    Cap.Content = strText
    EncryptString = Cap.Encrypt
End Function

Public Function DecryptString(strText As String, ky As String) As String
    Dim Cap As Object
    Dim cryptIt

    Set Cap = CreateObject("CAPICOM.EncryptedData")

    Cap.Algorithm = 3
    Cap.SetSecret ky
    Cap.Decrypt strText

    DecryptString = Cap.Content
End Function

The function EncryptString encrypts the string and the function DecryptString decrypts the string. See snapshot of results when you run the above Sub Sample

enter image description here

Can I use DPAPI (or something like it) in VBA?

1 Answers1

Linked