Access front end w\ ms sql backend and encrypted column

[tmcgrath]

Hi, first time posting! Access Forms are bound to linked tables. ODBC is used for connection to SQL Server. We want to encrypt two columns on a table but when I encrypt the SQL tables column, access does not decrypt the data. No experience with encryption. I've drop the tables and reconnected. I am guessing there is more required to decrypt the data on the front-end. Thanks

[CJ_London]

you will need to decrypt - it may be possible to do this using a view in sql or using a passthrough query. If using an access query you will need to use a decryption function.

In all cases you will need to know which method was used for encryption and the key to decrypt it.

[tmcgrath]

Thanks, the Sql columns are encrypted with Always Encrypted if that helps. If there is another method to use to make decryption easier I'm open to any suggestions.

[CJ_London]

I've not heard of an encryption method called 'always encrypted'. Sounds like a property to me -

always encrypted with what?

I'm not a sql server expert, just know enough to be dangerous. There are some responders here who may have some knowledge and may see your post. Suggest you post your question on a sql server forum or google your question. Found this link for example
https://learn.microsoft.com/en-us/sq...l-server-ver16.

[tmcgrath]

You and me both. Thanks for the tips and will do that. It's using the Deterministic encryption, saw the same article. It was too simple to set-up but now I need to know how to de-crypt on the front end side. I read about views and pass through queries also and sort of understand both approaches. I just want to be able to link the tables to the encrypted sql tables but view, add, update, modify in the front-end access database.

[NoellaG]

To run queries that return plain text you'll need the following permissions in the database:
- View any column master key definition
- View any column encryption key definition
- read permissions to the certificates store

[tmcgrath]

Thanks NoellaG!! I seem to have it working. I added the permissions for the keys and had to export/import the user certificate from the server to the workstation certificate store. Is there another way to do this or does this have to been done for all workstations? Thanks

[NoellaG]

You have to check with your network responsable: if you can't give permissions to the server certificate store, you'll have to import the certificates to the workstations.

[tmcgrath]

Again, thanks for the information. I now have another issue and I believe more critical. The encrypted data appears in plaintext just fine in the front-end linked table, however I am unable to run lookup queries on the encrypted column. See below. The query is a select query in the front end using one the tables that has encrypted columns in sql server. Any suggestions would be greatly appreciated.

[pbaldy]

FYI, to assist NoellaG when they're back online, there is no attachment.

[tmcgrath]

Apologizes! See below.

[NoellaG]

Do you have the correct settings in the ODBC connection string? see: https://learn.microsoft.com/en-us/sq...l-server-ver16

[tmcgrath]

Thanks for the reply. Here is my connection string. It connects fine and can view the table but if I run any sort, query or filter on one of the encrypted fields I get the error.

Description=xxxxxxxxx 2016;
DRIVER=ODBC Driver 17 for SQL Server;
SERVER=xxx.xxx.xxx.xxx;
UID=xxxx;
PWD=xxx;
Trusted_Connection=No;
APP=Microsoft® Windows® Operating System;
DATABASE=TEST;
Encrypt=Yes;
TrustServerCertificate=Yes;
ColumnEncryption=Enabled

[NoellaG]

Do you use pass through queries? https://www.mssqltips.com/sqlservert...ver-in-detail/

[tmcgrath]

I don't use pass through queries. I see that might be a solution but would involve a tremendous amount of work. My understanding was the connection string took care of the encryption/decryption process and allows queries to run as they had before.

This is all to satisfy a client who wants certain columns encrypted "at rest" so I may have to look at other alternatives to do this.