VBA - Remove Password from OLEDB Connection String

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.

@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.

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/