How do I set the OLEDB connection string in VBA to query a SQL Server database with Always Encrypted columns?

Question

I'm having trouble reading Always Encrypted data from SQL Server using ADO.

I have an Access front-end application which uses a SQL Server back-end. Some columns in the SQL Server database have been set as Always Encrypted, using a certificate generated in SSMS. I can read the unencrypted values of the encrypted columns using queries in SSMS and via ODBC linked tables in the Access front-end, but all my attempts to do the same via VBA code using ADO have been unsuccessful - the returned data remains encrypted.

I'm using the MSOLEDBSQL19 provider with this connection string (with actual server and database names replacing ServerName and DatabaseName). The database is using SQL Server authentication so I'm also including user and password information in the string (not shown here):

Provider=MSOLEDBSQL19;SERVER=<ServerName>;DATABASE=<DatabaseName>;Use Encryption for Data=True;Trust Server Certificate=True;

I've also tried variations like:

Provider=MSOLEDBSQL19;SERVER=<ServerName>;DATABASE=<DatabaseName>;Authentication=SqlPassword;Use Encryption for Data=True;Trust Server Certificate=True;

Provider=MSOLEDBSQL19;SERVER=<ServerName>;DATABASE=<DatabaseName>;Use Encryption for Data=True;Trust Server Certificate=True;DataTypeCompatibility=80;

Here is some example code, which uses an ADO command to open a recordset and print the value of the returned field, in this case the Medicare No of the client with ID=8. This field is encrypted in SQL Server and is not decrypted by the OLEDB provider:

Public Function Test(strUserName As String, strPassword As String) 


    Dim objADOConnection As Object 

    Dim strSQLConnectionString As String 

    Dim cmd As Object 

    Dim rst As Object 

    Dim strSQL As String 

     

    ' Open the ADO connection 

    strSQLConnectionString = "Provider=MSOLEDBSQL19;SERVER=<ServerName>;DATABASE=<DatabaseName>;" _ 

        & "Use Encryption for Data=True;Trust Server Certificate=True;" 

 

    Set objADOConnection = CreateObject("ADODB.Connection") 

    objADOConnection.Open strSQLConnectionString, strUserName, strPassword 

 

    ' Execute the query via a command object 

    Set cmd = CreateObject("ADODB.Command") 

    Set rst = CreateObject("ADODB.Recordset") 

     

    strSQL = "select MedicareNo FROM General.Client where ClientID = 8;" 

     

    With cmd 

        .CommandType = 1 

        .CommandText = strSQL 

        Set .ActiveConnection = objADOConnection 

        rst.Open cmd, , adOpenKeyset, adLockOptimistic 

    End With 

 

    rst.MoveFirst 

    Debug.Print rst(0) 

    rst.Close 

 

End Function 

The result is still encrypted:

??(?????????????????????????????????C???