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?

Ply

What is the data source? Is it Microsoft SQL Server?, if so then you could use Windows Authentication to secure access instead of passwords. – Harding

A SSAS cube, but accessed through msmdpump.dll. My application provides a streamlined way to add cubes, I'm not sure if it's windows authentication or not, but the credentials are stored in windows AD on the SSAS server. – Ply

Have you seen this: #3217514? It's crazy that it needs so much workaround to protect connection strings. – Condensate

@TomJohnRiddle No I haven't, yeah that's ridiculous you'd have to do that, just a design issue I guess, hard to build a good house on a bad foundation.... – Ply

@David Rogers: It's a SQL Server cube, it looks permissionable, learn.microsoft.com/en-us/dotnet/api/… so who cares if password visible so long as the permissioning is enforced? – Harding

@TomJohnRiddle : On that referenced question, I don't believe the connection string is obscured. Simply that the details are not in the VBA source. That is to say, Debug.Print conn.ConnectionString will still reveal password. So permissions is the way to go. – Harding

@SMeaden Yes it is permissioned, and that's really the point, each user see's a slightly different set of data based on their permissions. My little application attempts to clear those credentials on startup and close. Say the file was shared with some one else... They would see their own version of the data, vs what the original user might have seen(on refresh). My real issue here isn't the permissions, it's the fact that any file has the potential to contain the last credentials used in plaintext form of any previous users who may have accessed it. – Ply

@SMeaden users have the tendency to share files of this type, they don't understand the security implications of doing so, and my hope was that by removed any stored passwords I could mitigate this sharing issue. – Ply

Ok, well, you'll probably need to write some SQL Server Data Transformation Services (DTS) code to export for each use case a custom CSV or some other file that each user can run their own separate OLAP cube reports on. – Harding

@DavidRogers maybe you can try this solution for removing creditentials: #16696204 – Condensate

Upvoting the question, because most banks have a 'No embedded passwords and absolutely no plaintext passwords at all' policy. Also: however helpful it is to be told that we 'should' use Windows Authentication - and most databases do offer it now - we still need to strip embedded passwords from existing spreadsheets. – Canaigre

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.

Ply

I wouldn't use regex: a connection string is a delimited string, which you can split into an array: arrCS = Split(strCS, ";") and you can then traverse the array, excluding the array members which start withPassword=' and Pwd=` when you concatenate the connection string again. Beware of the 'Extended Properties' item of a connection string, as this is a separate coma-delimited list of name=value pairs which starts and ends with a Chr(34) double-quote: if the password is in there, you need to handle the quoted correctly. – Canaigre

@NigelHeffernan Huh, I though of doing it that way, each reference in VBA is another potential system specific issues later on. Other than that what would be the advantage of using the string split method? Maybe I can write up another answer attempting that instead... – Ply

@ David Rogers Two advantages: One, you don't need to import a RegEx library; Two, you don't need to use RegEx, which is an approximation - a guess at the semantics of the text and its structure - when you know that the connection string is a structured set of name-value pairs with a known name that you need to extract. – Canaigre

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.

Harding

I would love to do that, unfortunately all my users are in different domains/companies/locations outside of my control, we use the "MSDMPump.dll" method of giving them access, all credentials are stored on a AD server in the same domain where the cube is hosted. Connection strings are populated by my VBA macro. Maybe if I had the flexibility to have users connect through a centralized AD that would possible, maybe one day.. but that would be a major change to how the whole application works and pretty far out. It's a good suggested work around for certain cases though :) – Ply

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.

Ply

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/

Shing

It's a interesting idea, but the issue is that I'm trying to leverage excel's build it connection and pivot table logic rather than roll my own. I actually have a separate feature that does something similar to this to hide the credentials but its a bit different than this issue. – Ply