Full Test Searches on MSSQL Encrypted Tables/Fields

[stanlyn]

Hi,

Using VFP9sp2 and MSSQL2016, what is the best way to setup a full text search mechanism from VFP to search either
1. a ChillKat encrypted field in an un-encrypted SQL table and
2. an un-encrypted field in an MSSQL encrypted table.

If the table is encrypted, then I'd probably use MSSQL's encryption. If I use field encryption on a un-encrypted table, then I'd use ChillKat.

I have experience using ChillKat and zero experience using MSSQL's encryption. I need to provide that same functionality that phdbase gave us for .dbfs for years, but now I need it for sql encrypted data. I intend to use MSSQL's fulltext and freetext search functions.

Because MSSQL provides both the encryption and full text searching functionality, does anyone know if what I'm trying to do will actually work? It makes sense that it would, but haven't seen any articles discussing it. Any ideals, issues, gotchas and any discussion would be great.

Thanks,
Stanley

 

[Olaf Doschke]

Let's remove Chillkat from the scenario and say anything encrypting field values before they even get stored in MSSQL and decrypting them after reading them back. Any third party encryption component.

Encryption is a complicated topic. I'll try my best to keep it understandable. In short MSSQL encrypted databases are encrypted on the storage level, no more, no less. That's also referred to as "transparent" encryption. With this mechanism, the MDB/LDF files on HDD of the MSSQL server are encrypted, but whatever the MSSQL process reads from such DBs is automatically decrypted.

When you encrypt via third-party component, the safety of that strongly depends on a) the good implementation of the encryption mechanism and b) handling secrets (keys/certificates/passwords) with best practice. But let's assume all that is handled correctly, MSSQL then doesn't know the unencrypted values and you can forget about full-text search capabilities of MSSQL.

This gets ugly, because you now have two choices: Always read and decrypt all data to be able to search it, or create your own full text index, eg in a simple case a list of all words, each given an ID and a n:m relation table (wordid, recordid) linking each word with records they occur in. That's not giving away the full text stored in the records encrypted but allows to do reverse queries of knowing the list of words contained in some record. If you now encrypt this index data, too, you just shift the problem and don't solve it. Something has to stay readable.

In very short, this is a case of you can't have the cake and eat it, too.

To add, why MSSQL security works this way: partially the security concerns of many industry standards only ask for the security of "data at rest."Om itself, this would be insufficient, but it is just one of many concepts of security you have to fulfill. What's also important and what's also done is encryption of the data transfer. When the term end-to-end encryption is used, that normally means the encrypted values of files are read unencrypted (otherwise queries clauses searching for clear text strings and values can't work), results are then encrypted for transfer from server to client and decrypted at the client end, most likely from the ODBC driver before handing over the result to whatever client software using the driver. Weak points always are endpoints of this. And since hackers don't steal servers to steal data, but hack into the systems and try to get root (admin) permissions, all that safety is depending very much on the security of accounts and OS, too, and as of recent, also the security of CPUs about the memory management and separation of memory access of processes.

So in short native MSSQL security works with several vulnerable points, but in itself, it's still a big advantage, as you "only" need to take special care about securing these vulnerable points. Remember, security always also was and is handled by guarding unencrypted data and files with access privileges and with guarding these guards, so to say. Classic file system security wasn't making use of encryption and also was and is justified.

Actually, the most important part of security is the transfer encryption, as that is happening over the public internet network. SQL connections are very fine for that and also guard unencrypted data in the transfer. So additional encryption you do with MSSQL or third-party components mainly adds a burden and doesn't higher security much. It's often a requirement anyway.

As far as you have choices: Using built in encryption and secure connections (ssl) will both be good measures, encrypting single fields on top of that encryption is done and has it's reasons, but should only be done on data you don't need to include in where clauses. Eg it's not unusual to encrypt credit card numbers or other personal private infos that deserve or require protection, as you don't search customers by credit card number.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

Take a look at

https://dba.stackexchange.com/quest...2012-transparent-data-encryption-and-indexes"

as this clearly states that all normal queries, functions and commands works with TDE turned on.

The issue we see is it is only available in the expensive enterprise edition...

I'm going to build a sample using it and see if fulltext searching actually works.

Thanks,
Stanley

 

[Olaf Doschke]

Yes, this doesn't contradict and indeed just confirms what I said, because that's transparent encryption, it's encryption only done on the MDF/LDF files and not in what MSSQL Server process reads and sees, thus there is the possibility to run all queries as if data wasn't encrypted.

Bye, Olaf.