MS SQL and Always Encrypted - how to use in Clarion?

Tags: #<Tag:0x00007f2024d19020> #<Tag:0x00007f2024d18e90> #<Tag:0x00007f2024d18d50>

Has anyone work with the always encrypted functions of MS SQL from Clarion?

We have several “always encrypted” columns and if we set

Column Encryption Setting=enabled

as additional parameted in Microsoft SQL Server Management Studio when connect to the server and then do

select * from MyTable

we see all encrypted columns info as a normal text.

So when we run our Clarion 10 application and try to open our MyTable (in browse window) we are getting error (because of encrypted columns).

Anyone know how to work with encrypted (in MS SQL server) columns from Clarion? Any idea?
What should be connection string? Any specific settings for the fields in clarion dct?

TIA

This is because of the new EU regulation forces us to encrypt the data (GDPR).

I have not implemented it yet, but it is on my near term horizon.
I have a couple of clients interested in using always encrypted.
I can report on my progress when I start.

Hi Geunnadi,

I’ve updated your post on the newsgroups with a couple of links.

So far I’ve been able to get a browse and a report to work with an SQL Server 17 encrypted column using the ODBC Driver 13 for SQL Server - example connection string below…

‘DRIVER=ODBC Driver 13 for SQL Server;SERVER=LENOVO-PC\SQLEXPRESS;UID=sa;PWD=XXXXXX;DATABASE=encrypt_test;ColumnEncryption=Enabled;’

If I omit the extra ‘ColumnEncryption=Enabled’ clause the data is displayed encrypted, with that clause present the data is retrieved in plain-text.

Graham

2 Likes

Not what you looking for but I use CryptoNite from CapeSoft to do the encryption. Encrypt before I write it to the SQL Table and decrypt it before displaying it in the Front-End.

Graham,
Thank you very much, good (?) news :slight_smile:

Can you just provide more info:

  1. What DRIVER is used for your table in DCT (MSSQL? ODBC?)
  2. What is actual declaration (type and length) of encrypted filed in Clarion DCT and sql table on the server?

Nardus,
The idea is to use built-in feature in MS SQL server using built-in feature of Clarion :sunglasses:

Hi Guennadi,

I’ve updated the posts on Clarion10 newsgroup with details.

At the moment I can’t get a stored procedure to work for changes and inserts.

However I can do inserts using .NET 4.6.1 code as per this link…

https://blogs.msdn.microsoft.com/sqlsecurity/2015/06/04/getting-started-with-always-encrypted/

NB I’m using VS on the same machine as the SQL Server instance so didn’t need to do any of the certificate exporting.

I’m confident updates could be easily done via .NET as well.

Lots of question remain however, such as…
What happens when you move the database via backup/restore?
If I run the .NET code on another machine will it work?
Do we need to export keys from here to there to get stuff to work?

Graham

Graham,

Thank you for update. Yes, there are a lot of questions remain…

I took your idea and bought CapeSoft CryptoNite, follow the sample JustOneField.tps in FILE AND FIELD ENCRYPTION program, it works. But how to search the indexed ebcryption field? Even the “get” command does not work. Do you know why? Thank you!

I think the idea is that the data is encrypted, so you will not be able to search on encrypted data.

If you have to do a search, maybe read the values into an In-Memory table - decrypting them before adding it into the memory table and then do search in the memory table. But this will only be viable if you have a smallish number of records.

Nardus ,
Thank you to answer my question.
But the instruction of the capesoft Cryptonite said:
" Tables To Encrypt
A list containing the tables which contain encrypted fields.
Fields in the table which will be encrypted. Note that encrypting fields which are used in Keys can lead to problems. Since keys are used to sort data in the table, if you encrypt key fields then the key is sorted by encrypted value, not actual value. This means that you cannot do a SET/NEXT loop through a table using a encrypted field, and Browses cannot use column-sorting on encrypted fields. You can still do a GET on encrypted keys."

The link is:
https://mirror.capesoft.com/docs/CryptoNite/Cryptonite.htm#CryptoNiteTemplates

I try GET, it does not work, Do you have such experience?

Nardus ,
Good news, the capesoft released new Cryptonite on 10-15-2019, The “GET” command works now, I can search any encrypted data field.

1 Like