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
> Reading And Editing Encrypted SQL Db, 2013    
 
   
SomekindaVB
post May 27 2019, 11:50 PM
Post#1



Posts: 312
Joined: 15-December 16



Hi All,

My access DB is linked to SQL tables. I have encrypted some of the fields in those tables

CODE
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert
UPDATE dbo.Tbl_Table SET EncryptedField = EncryptByKey(Key_GUID('MyKey'), N'Text To Be Encrypted')
Where [ID] = 1234


And I can decrypt those fields using a passthrough query in MS Access.

CODE
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert
SELECT EncryptedField
CONVERT(nvarchar(Max), DecryptByKey([EncryptedField ])) AS 'Decrypted Field'
FROM DBO.Tbl_Table
Where [ID] = 1234


The passthrough query works just fine to view the data, however, I need to be able to edit the data in this query result.

Can anybody who might have worked with Access and SQL backend offer a suggestion to edit the query record?

Also, am using SQL 2008, MS access 2013.

Cheers
This post has been edited by SomekindaVB: May 27 2019, 11:51 PM
Go to the top of the page
 
nvogel
post May 28 2019, 12:10 AM
Post#2



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


You won't be able to edit the results of your query with a bound form. You could create an unbound form that calls a SQL stored procedure to save data for each row.

This question would be more appropriate for the SQL Server forum where you already have a thread on this topic. If you reply in the same thread then people can see the history of what has been suggested and what you have tried so that the discussion doesn't need to cover the same ground again.
Go to the top of the page
 
SomekindaVB
post May 28 2019, 01:18 AM
Post#3



Posts: 312
Joined: 15-December 16



nvogel

QUOTE
You won't be able to edit the results of your query with a bound form. You could create an unbound form that calls a SQL stored procedure to save data for each row.


Sounds good. Can you elaborate?

The question is posted on an Access Forum because surely somebody, somewhere has needed to get access to an SQL table with encrypted fields via MS Access for the purpose of editing them, and later re-encrypting them.

Also my question on another forum is not the same.

Cheers
Go to the top of the page
 
isladogs
post May 28 2019, 01:37 AM
Post#4


UtterAccess VIP
Posts: 1,540
Joined: 4-June 18
From: Somerset, UK


Hi
I have an article on my website with an example database showing how this can be done.
I used the RC4 encryption cipher to encrypt/decrypt data in an Access BE but the principle is the same
See Encrypted Split No Strings Database
The purpose in that case was also to show how a BE can be linked with no connection strings.
As already stated, the form(s) must be unbound.

Hope that helps

--------------------
Go to the top of the page
 
cheekybuddha
post May 28 2019, 02:18 AM
Post#5


UtterAccess VIP
Posts: 11,459
Joined: 6-December 03
From: Telegraph Hill


>> The question is posted on an Access Forum <<

You appear to have posted in the PHP/MySQL forum.

I'll try and get a mod to move it.

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 01:41 AM