locked
Using Access to update SQL-Server2016 Always Encrypted data RRS feed

  • Question

  • I'm having trouble getting this to work. All I get from the Debug.Print is gibberish (which I assume is the encrypted data).
    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As ADODB.Recordset
    
    con.ConnectionString = "DRIVER=ODBC Driver 13 for SQL Server;SERVER=myServer;Trusted_Connection=Yes;DATABASE=TestAE;Column Encryption Setting=Enabled"
    with con
    	.Open
    	With cmd
    		.ActiveConnection = con
    		.CommandType = adCmdStoredProc
    		.CommandText = "IDSP"
    		.Parameters.Item("@id") = 68
    	End With
    	Set rst = cmd.Execute
    	Do Until rst.EOF
    		Debug.Print rst.Fields("SSN")
    Is there a way to access "Always Encrypted" data from Access? If so, any ideas what I'm missing?

    Sunday, January 29, 2017 10:10 PM

Answers

  • Trouble getting the newest version of the ODBC driver installed, but finally it worked. Once it's installed, this new option appeared on the ODBC configuration. Checking it made everything work.
    Monday, January 30, 2017 11:33 PM

All replies

  • Hi GordonPrince4575,

    It's very pleasant to help you, first of all, I suggest you reading this Document: Using Always Encrypted with the Windows ODBC Driver.

    Which version of ODBC driver did you use? According to your description, it is ODBC 13, have you upgraded it to the latest version 13.1?

    Besides, based on the code you given, I didn't find a definition for the keys, Encrypted Columns will need keys to decrypted data, please refer to the below table:



    For more information, please refer to the above Document I suggested you reading. In this article, you can find the sample code. If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Teige Gao Monday, January 30, 2017 7:14 AM
    Monday, January 30, 2017 7:11 AM
  • Which version of ODBC driver did you use? According to your description, it is ODBC 13, have you upgraded it to the latest version 13.1?

    I have tried to download and install the latest ODBC version 13 
    but always get the message
    "A newer version is already installed".
    I assume this newer version came from the SQL2016 updates that are installed on my computer.

    Besides, based on the code you given, I didn't find a definition for the keys, Encrypted Columns will need keys to decrypted data, please refer to the below table:

    Reading the documentation you recommended, everything seems to refer to Azure. I can't figure out what to do if there is no Azure involved.
    
    For now this is only on a development machine -- same machine running application and SQL-Server. In production, this will all be on an office network -- SQL-Server on Windows server, applications on Windows 7/10 workstations.
    
    Can you provide a sample argument(s)?


    Monday, January 30, 2017 1:03 PM
  • Trouble getting the newest version of the ODBC driver installed, but finally it worked. Once it's installed, this new option appeared on the ODBC configuration. Checking it made everything work.
    Monday, January 30, 2017 11:33 PM