1

I've got an Access application I'm trying to make work with SQL Server 2016 data that has several columns that are "Always Encrypted". I'm using Access 2010 and I can't get this to work, although from what I read, it seems like it should.

I've got the latest ODBC 13 drivers installed, new .accdb database (created with Access 2010, though, in case that's causing the trouble), I link to the tables but only get gibberish from my two encrypted columns.

I even tried this to use a parameterized query, but still gibberish for the encrypted columns.

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As ADODB.Recordset
With con
    .ConnectionString = "DRIVER=ODBC Driver 13 for SQL Server;SERVER=myServer;Trusted_Connection=Yes;DATABASE=AEdatabase;Column Encryption Setting=Enabled"
    .Open
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandText = "Debtor4IDSP"
        .Parameters.Item("@id") = 68
    End With
    Set rst = cmd.Execute
    Do Until rst.EOF
        Debug.Print rst.Fields("SSnum")

Any ideas what I'm missing here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gordon Prince
  • 142
  • 2
  • 9
  • Does the account you are using the access the db have permissions to use the column master key and column encryption key to decrypt the columns? – dfundako Jan 29 '17 at 17:34
  • I am sysadmin on the SQL-database. I checked the permissions and those permissions are granted to dbo. So I don't think that's the problem. – Gordon Prince Jan 30 '17 at 18:25
  • Do you have the Column Master Key Certificate exported from the SQL server and installed wherever you're running your Access Query from? – Nick Jul 25 '18 at 08:20

0 Answers0