UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL To Access Encryption, Access 2013    
 
   
SomekindaVB
post May 15 2019, 08:09 PM
Post#1



Posts: 293
Joined: 15-December 16



Hello all,

I have been tasked with providing encryption to my database. My Database is MS Access frontend with SQL backend.

I can encrypt and decrypt data in the SQL database using Keys, Certificates, etc.

If it's not encrypted, I can use an SQL table like any access table. No problem linking. However, I am unclear about how Access decrypts data and pulls it into fields and how I can then encrypt any data I edit into these fields.

I wonder if anybody can offer some insight, or provide a quality resource that I can read that may help with this.

Cheers
This post has been edited by SomekindaVB: May 15 2019, 08:14 PM
Go to the top of the page
 
theDBguy
post May 15 2019, 08:25 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,317
Joined: 19-June 07
From: SunnySandyEggo


Hi. Encryption, of course, adds an extra layer of complexity to your database application (this is necessary to add an extra layer of security). You should be able to use a passthrough query to "see" the encrypted data. However, you will have to use code (VBA or SP) to edit and save the changes back to the table (I believe).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nvogel
post May 15 2019, 09:32 PM
Post#3



Posts: 940
Joined: 26-January 14
From: London, UK


If you use Transparent Data Encryption (TDE) then the encryption and decryption is automatic - you don't have to do it yourself. TDE is available in SQL Server Enterprise Edition and Developer Edition.
This post has been edited by nvogel: May 15 2019, 09:32 PM
Go to the top of the page
 
SomekindaVB
post May 15 2019, 10:48 PM
Post#4



Posts: 293
Joined: 15-December 16



DBGuy: Thanks. I looked into Pass-Through queries and built one to see how it works, and it looks great. Except I don't really want to have to code every field back into the DB which could be painful. is there another way?

nvogel: Thanks for the tip. Except I have no control over the SQL database and cannot change how it's set up. I am a simple Access Dev who has build a large DB and only just now been asked to encrypt certain fields. But the idea seems great!

I'd really like to see or know of an example. Could you point to anything?

Cheers
This post has been edited by SomekindaVB: May 15 2019, 11:01 PM
Go to the top of the page
 
nvogel
post May 16 2019, 12:35 AM
Post#5



Posts: 940
Joined: 26-January 14
From: London, UK


Hi SomekindaVB, You might not have control but somebody does. You could ask the person responsible for the database to turn on TDE. In principle it only takes a few moments to enable TDE, although they may want to test it out first and schedule the change to happen during a maintenance window.

You will need the database administrator's help whatever you do. You will almost certainly need to make some changes to the database if you try to do encryption yourself without TDE. The encrypted data would be binary and that means changing the data type of any numeric or string column you want to encrypt - or alternatively creating a new binary column.

If you are using SQL Server 2016 or later you may want to investigate Dynamic Data Masking rather than encryption. Masking data is more useful than encryption for many applications.

In any case, please consult your database administrator first.


This post has been edited by nvogel: May 16 2019, 01:25 AM
Go to the top of the page
 
nvogel
post May 16 2019, 04:25 AM
Post#6



Posts: 940
Joined: 26-January 14
From: London, UK


There is an example of using Symmetric Encryption here:
https://docs.microsoft.com/en-us/SQL/relati...-column-of-data
Go to the top of the page
 
gemmathehusky
post May 16 2019, 05:57 AM
Post#7


UtterAccess VIP
Posts: 4,573
Joined: 5-June 07
From: UK


It can't be undertaken lightly, I imagine.

I don't know if this surmise is right or wrong, but it seems to me that if you encrypt the stored data, then trying to read a single key needs the that key to be turned into the encrypted version, not the natural version. Data that would be contiguous in natural order, will not be contiguous in encrypted order. Therefore retrieving a range of data will not be as efficient with encrypted data.

Does this make sense, or does the data get managed differently?

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
nvogel
post May 16 2019, 06:29 AM
Post#8



Posts: 940
Joined: 26-January 14
From: London, UK


Right. Encrypting columns in an existing database using either server-side symmetric encryption or the "Always Encrypted" feature will impact quite a few things: new columns/changing datatypes, changes to keys or indexes and most likely rewriting stored procedures and application logic. I should think that TDE or Dynamic Data Masking is a simpler and better option in 90% of cases. TDE is Enterprise only but Dynamic Data Masking is available in all editions, including Express.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th May 2019 - 02:06 AM