VBA - Remove Password from OLEDB Connection String

Question

I have an excel file that contains a series of OLEDB connections leveraged by several pivot tables. I would like to create a VBA function that removes all password from that several connection string as the file is closed(so that the users password will not be persisted). First I thought all I need to do was set the "Save Password" property to false, something like this:

Public Sub RemovePasswordByNamePrefix()
    Dim cn As Object
    Dim oledbCn As OLEDBConnection

    For Each cn In ThisWorkbook.connections
        Set oledbCn = cn.OLEDBConnection
        oledbCn.SavePassword = False          
    Next
End Sub

Should work right, on closing the file and reopening it you shouldn't see the password anymore in the connection string. It should not be "Saved":

Wrong, password is still there... It has been "Saved". Not sure what that feature is supposed to do. Maybe there referring to a different password? So, I attempted the big hammer approach, unfortunately it has it's own challenges, and so far I haven't gotten that working.

I'm not quite sure how to do this... Why is this so massively insecure? It persists plaintext passwords every file that contains a connection string of this sort, easily readable by whoever could access that file.

Maybe I could make some sort of Regex to remove just the password from the file? When I do that in the interface my cubes refresh and prompt me for my credentials, (I wonder)would that occur if I did it in VBA, even if the trigger is upon excels closure?

Bottom Line: What is the best way to prevent these passwords from being persisted in the file upon it's closure?

Answer 1

@TomJohnRiddle points out that I should look at modifying the connection string similar to the following question. Initially I was concerned that taking this approach could prompt the user with a login screen after modifying the connection string. However since I don't have any better ideas I gave it a shot, and it seems to work, here's what I've mocked up:

Public Sub RemovePasswordByNamePrefix()
    Dim cn As Object
    Dim oledbCn As OLEDBConnection

    Dim regEx As New RegExp
    regEx.Pattern = "Password=[^;]*;"

    For Each cn In ThisWorkbook.connections            
        Set oledbCn = cn.OLEDBConnection
        oledbCn.SavePassword = False

        oledbCn.connection = regEx.Replace(oledbCn.connection, "")
        oledbCn.CommandText = "" 'My app repopulates this after open
    Next
End Sub

and it seems to work:

So I think I'll go with this approach, but I'm still open to other suggestions. Would be nice to clear everything and fully reload it, but so far that doesn't appear to be possible.

I'm also concerned with what versions of VBA support the "Regex" references. I would like something that would be Excel 2010+ 32/64 bit compatible. I have yet to test this on any older version(I'm currently running Office 365). I assume it will all work fine, but I've been unpleasantly surprised with these things in the past.

Answer 2

See this on SQL Server authentication Authentication in SQL Server. There it says you can use 100% Windows Authentication or you can use Mixed-Mode (Windows Authentication and passwords). If you really want to banish passwords from connection strings do not install with Mixed Mode Authentication just run 100% Windows Authentication. However, there may be some code already deployed written to use passwords so that may not always be practical.

So, the other way to discipline no passwords is to use

Integrated Security=true; 

in your connection strings. This Stack Overflow question on the subject is well visited.

Answer 3

@NigelHeffernan suggests a slightly different approach for how to do this, here's a version without regex's:

Public Sub RemovePasswordByNamePrefix()
    Dim cn As Object
    Dim oledbCn As OLEDBConnection
    Dim stringArray
    Dim stringElement As Variant
    Dim newStringArray As Variant

    For Each cn In ThisWorkbook.connections
        Set oledbCn = cn.OLEDBConnection
        oledbCn.SavePassword = False

        stringArray = Split(oledbCn.connection, ";")

        For Each stringElement In stringArray
            If Not InStr(stringElement, "Password=") Then
                If IsEmpty(newStringArray) Then
                    newStringArray = Array(stringElement)
                Else
                    ReDim Preserve newStringArray(UBound(newStringArray) + 1)
                    newStringArray(UBound(newStringArray)) = stringElement
                End If
            End If
        Next

        oledbCn.connection = Join(newStringArray, ";")
        oledbCn.CommandText = "" 'My app repopulates this after open
    Next
End Sub

I'm not sure the benefit of this method(other than a lack of another library reference) and I haven't tested outside of one connection string/one machine yet. My connection strings don't contain the "Extended Properties" field, maybe this approach wouldn't work for that.

Answer 4

It looks like you are using DSNs, which is something that Excel will create if you use the default connection management tools in the GUI. When working with DSNs, the ODBC driver will sometimes put cleartext passwords in to the Registry, even when you don't select "Save Password".

Instead of allowing Excel to manage your connections you would need to manage them yourself. Here is some example code from MS MVP Ben Clothier. You would have to modify the connection string to match your use case. You might be able to copy the details from your existing connections before you remove them.

Public Function InitConnect(UserName As String, Password As String) As Boolean
‘ Description:  Should be called in the application’s startup
‘               to ensure that Access has a cached connection
‘               for all other ODBC objects’ use.
On Error GoTo ErrHandler

    Dim dbCurrent As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    ‘<configuration specific to MySQL ODBC driver>

    strConnection = “ODBC;DRIVER={MySQL ODBC 5.1 Driver};” & _
                     “Server=” & ServerAddress & “;” & _
                     “Port=” & PortNum & “;” & _
                     “Option=” & Opt & “;” & _  ‘MySql-specific configuration
                     “Stmt=;” & _
                     “Database=” & DbName & “;”

    Set dbCurrent = DBEngine(0)(0)
    Set qdf = dbCurrent.CreateQueryDef(“”)

    With qdf
        .Connect = strConnection & _
                     “Uid=” & UserName & “;” & _
                     “Pwd=” & Password
        .SQL = “SELECT CURRENT_USER();”
        Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
    End With
    InitConnect = True

ExitProcedure:
    On Error Resume Next
        Set rst = Nothing
        Set qdf = Nothing
        Set dbCurrent = Nothing
    Exit Function
ErrHandler:
    InitConnect = False
    MsgBox Err.Description & ” (” & Err.Number & “) encountered”, _
        vbOKOnly + vbCritical, “InitConnect”
    Resume ExitProcedure
    Resume
End Function

NOTE: This is written for MS Access, not Excel. The concepts are all the same. You might want to try making your front end in Access and then export your views to Excel from Access. This would allow you better control of the link to your back-end and allow you to use SQL in Access to define what you want to export to Excel.

READ THIS: https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/