How to secure an ADO connection in an Excel VBA project?

Question

I have an Excel spreadsheet (.xlsb) that connects to an SQL Server via ActiveX Data Objects (ADO) in VBA that I have to share with a bunch of people. The code uses the following string to connect do the database:

' Create the connection string.
sConnString = "Provider=SQLOLEDB;" & _
              "Data Source={server name};" & _
              "Initial Catalog={database name};" & _
              "UserId={username};" & _
              "Password={password};"

How do I keep my SQL username and password secure and prevent 3rd parties from gaining access to them? Is password-protecting the VBA project enough or can that be easily cracked?

Have you thought about talking to the DBA for a log-in that only provides access to the data that the users will see anyway? You have a view in the DB and an account that can only access the data in that view.... — Robin Mackenzie, Dec 12 '16 at 09:46
Can you limit the security based on the machine/user id in SQL Server? — Nathan_Sav, Dec 12 '16 at 13:15
@RobinMackenzie the account I'm using already is read-only, but I don't want anyone to see the login details. — Dariusz Kuśnierek, Dec 13 '16 at 09:54
@Nathan_Sav unfortunately, for now, I have to use a shared sql account. — Dariusz Kuśnierek, Dec 13 '16 at 09:55

score 2 · Answer 1 · edited May 23 '17 at 11:45

VBA password protection can't be considered secure as it's easily circumvented.

You could of course add some kind of obfuscation (hash function or similar) in the VBA Code, so that the password isn't visible in plain-text, but anyone familiar with VBA should be able to get around this in little time.

I'd consider restricting the access to the db with a view that only contains the needed data.

Hiding the password could be done by putting the connection functionality in a dll, which you then reference in the VBA code. This would require more work to reverse and gives you much more possibilities to really hide the credentials. See also this reply: https://stackoverflow.com/a/19163256/5970009

Did not think about putting the connection functionality in a dll. This looks promising; I'll look into it. Thanks! — Dariusz Kuśnierek, Dec 13 '16 at 10:04

Vityata · Accepted Answer · 2018-07-12T12:35:08.993

VBA project is not protected enough - anyone with 2 hours free time and internet can probably go through.

What is the base case scenario is to make different users on DB level with different permissions. Then ask your users on the Excel spreadsheet to give password and username in one of the cells or through userform. Take the password and username and use it in the connection string.

As a further step to security, you may use a little small trick, that I call salting. E.g. Let's say that your password for a the given user is vityata. Then ask the user to enter it. Upon entering, take the password and change it to something else. This something else should be the password to the database. I mean something like this:

Public Function str_generator(ByVal str_value As String, ByVal b_fix As Boolean) As String

    Dim l_counter As Long
    Dim l_number As Long
    Dim str_char As String

    On Error GoTo str_generator_Error

    If b_fix Then
        str_value = Left(str_value, Len(str_value) - 1)
        str_value = Right(str_value, Len(str_value) - 1)
    End If

    For l_counter = 1 To Len(str_value)
        str_char = Mid(str_value, l_counter, 1)
        If b_is_odd(l_counter) Then
            l_number = Asc(str_char) + IIf(b_fix, -2, 2)
        Else
            l_number = Asc(str_char) + IIf(b_fix, -3, 3)
        End If

        str_generator = str_generator + Chr(l_number)

    Next l_counter

    If Not b_fix Then
        str_generator = Chr(l_number) & str_generator & Chr(l_number)
    End If

    On Error GoTo 0
    Exit Function

str_generator_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure str_generator of Function Modul1"

End Function

Private Function b_is_odd(l_number As Long) As Boolean

    b_is_odd = l_number Mod 2

End Function

Let's say that the user password is vityata. Then after the user enters it, it is changed to cxlv|cwcc, which is the real password for the database.

?str_generator("vityata",false)
cxlv|cwcc
?str_generator("cxlv|cwcc",true)
vityata

Nice code - but I gain access to it then I can back-solve the password... — Robin Mackenzie, Dec 12 '16 at 09:56
That's 100% true. But at least you do not put the password in a plain text. — Vityata, Dec 12 '16 at 09:56
Security through obscurity - but what if his DB has the nuclear passcodes? — Robin Mackenzie, Dec 12 '16 at 09:57
Well, that's why each user should enter his own password manually through the Excel sheet. Then, taking this password we connect to the DB. 100% secure, if we do not keep the password anywhere. — Vityata, Dec 12 '16 at 09:59
I can see something like this used to generate passwords based on users' Windows usernames. That way, I'd have one *main* password, and seperate passwords generated from the main one, with usernames taken into account, only for those I'd like to grant access. Great idea, thanks! — Dariusz Kuśnierek, Dec 13 '16 at 10:01
This is a good option, unless the `bad user` learns the usernames of the good users (which in any company is usually how the emails are set). Then with the VBA code he/she can generate the passowords. But still, it is one step further in protection and better. — Vityata, Dec 13 '16 at 10:07

How to secure an ADO connection in an Excel VBA project?

2 Answers2