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

clarion10
mssql
encryption
Tags: #<Tag:0x00007fdb8c24f5f8> #<Tag:0x00007fdb8c24f490> #<Tag:0x00007fdb8c24f210>

#1

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).


#2

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.


#3

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


#4

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.


#5

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?

#6

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


#7

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


#8

Graham,

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