Data encryption at rest, VFP vs MS SQL 2

[Steve Yu]

Hello,

Need help.
What is data encryption at rest (vs not at rest) ?
Are VFP tables encrypted ?
Even though .dbf tables can't be viewed with Word or text editor, I don't think they are encrypted, correct ?
How about MS SQL, are tables encrypted as default (we have the 2019 version) ?
One of our trading partners is requiring their data (stored as MS SQL tables) be encrypted at rest on our server.
How would SQL encryption impact VFP SPT operations, any coding changes needed ?

Steve Yu

 

[Chris Miller]

Well, "at rest" means when you only have the access to the database files, you can't get at the data, even when knowing the file structure of MDB files (in this case).

"at rest" refers to the state of the SQL Server not running, being at rest, though that's not the important part, really - the databse file content doesn't change with the status of the service running or not. The reference makes sense only in the oppposite direction, because running SQL Server Service, as normal, and connecting to it, using the databases just as usual with no code changes, you don't need to write decryption routines to get at the actual data.

So this is just protection against someone getting at the hardware but not getting into the service. It would not be surprising, if your customer doesn't even know this is a level of security not protecting against a hacker instrusion by software, which is the more probable way a hacker takes than actually getting into a data center in person, isn't it?

So that security level protects you from guys swapping out drives in the data center. It's good for that case but no more than that.

What I can't tell you is the exact specification of the necessary security level if you'd wanted to offer same security level with DBFs, technically the step to login to SQL Server has the same two times authentication as is necessary by file permissions, so DBFs can be double secured in the same strength as long as besides file permissions the drives used for storge are using certified hardware encryption. Which means when not running Windows and a specific user SID, just 1:1 reading the drive will give you encrypted data only. Whereas when logged in as usual and having the right permissions, you also don't need cryptor or other such tools/DLLs top read the data decrypted, that happens by the hardware controller in conjunction with Windows.

Which also means that VFP+DBF files+Cryptor is a higher level of security than that, by the way. But I guess data protection officers working for authorities qualified to verify a company wouldn't know, recignize or allow this, so you're bound to what laws specify.

I think it's not your goal to use DBFs. Using SQL Server with this level of data at rest security is possible without changing client side code. Once you're connected to SQL Server and pass in a query as usual you also get back the data as usual, not encrypted. At best it's combined with connections using SSL, so the network traffic from SQL Server to clients is also encrypted and you get to the level of end to end encryption.

Not to forget, SQL Server also has features to store data encrypted on top of that general data at rest protection.

With only data at rest protection, the hurdle for hackers is still "just" the login to the data center server (OS level) and then the SQL Server service or the file access permissions (or both). With all their means like listening on a network, hijacking already made connections, etc, etc.

These demamnds are driven by governments, usually, and paint a picture of false/incomplete and wrong understanding, as they enforce very little against the real threats. Encryption at rest is something that I consider important and good for laptops, but they are less helpful for servers that are placed in secure places. So the actual case they protect against are - as said - data center employees able to swap server drives to steal data, more or less. It becomes less important when there is an additional layer of VMs running on servers, which add another level of access and likely also encryption to not have hands on data even encrypted, just by having a swapped out drive.

To protect against ransomware encrypting a file system, for example, already encrypted data does not protect against being encrypted again by the ransomware and thus not accessible also by an SQL Server able to decrypt it's own encryption. So the only protection level against that remains what always was best to recover from any kind of loss: Backups. Also stored remote to protect against the case of local physical damage like fire or water damages.

The topic I guess is data privacy and protection of rights on personal data, for example in medicinical data. It's not a step in the wrong direction, it's just a too small step. Well, the only good news is that as a developer you are not involved in when SQL Server stores its databases with this data at rest protection.

Chriss

 

[vernpace]

Steve;

Putting it more simply and more accurate:

(1) "Data at Rest" is the state of data being stored.
(2) "Data in Transit" is the state of data being moved.
(3) "Data in Use" is the state of data loaded into memory.

VFP tables are not encrypted by default. There are 3rd party encryption tools for this. Why are you asking this question if you are using SQL Server 2019?
SQL Server tables are not encrypted by default.
"How would SQL encryption impact VFP SPT operations, any coding changes needed ?" You bet!

For more information, go here:

https://learn.microsoft.com/en-us/s...crypted-database-engine?view=sql-server-ver16

Get ready to learn (and perform) a lot of DBA stuff - good luck!

 

[Mike Lewis]

Even though .dbf tables can't be viewed with Word or text editor ...

That's not correct. Just open any DBF in, say, Notepad or Notepad++. You will easily be able to see all the data - more or less at a glance. The only exceptions would be memo and general fields, but even memo data is visible if you open the corresponding FPT file in the editor.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

vernspace,

I said:

Not to forget, SQL Server also has features to store data encrypted on top of that general data at rest protection.

Data at rest encryption in MSSQL is this:

https://learn.microsoft.com/en-us/s...sparent-data-encryption?view=sql-server-ver16

Transparent data encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure Synapse Analytics data files. This encryption is known as [highlight #FCE94F]encrypting data at rest[/highlight].

No, you don't have to learn how to work with Always Encryted data, when that's not used. And when they use Always Encrypted databases and say they use Data at rest encryption, that again just shows they don't know the basics, even, of categorizing this.

But you could use this, it would be more to the point of data protection, I already gave my personal opinion about the too small step the law asks for with data at rest encryption only.

Chriss

 

[Steve Yu]

vernpace,

Appreciated your help first of all.

"How would SQL encryption impact VFP SPT operations, any coding changes needed ?" You bet!

Not sure I agree.
From what I have been able to decipher from feedbacks all you guys provided here and research from other sources since my initial post, I concluded no coding changes will be needed if TDE is implemented on the MS SQL databases (our MS SQL 2019 version 15.x does have TDE capability, just never configured).

Why are you asking this question if you are using SQL Server 2019?

We are operating in a kind of hybrid environment where both VFP .dbf (small portion) and MS SQL tables are employed.
And all our development and production projects are done under VFP 9.0 (character mode, xBase compatible), using SPT only for SQL server access.

Mike and Chris,

Thank you both for valuable opinions always.

Steve Yu

 

[vernpace]

Steve,

What you are able to decipher from feedback here is only your opinion (based on the opinion or expertise of others) and nowhere near as important as learning by doing.

Some of your questions (e.g. "Are VFP tables encrypted ?") and your lack of other questions tells me you are not very familar with VFP. In addition, some of what you asked here could have been answered by simple Google queries in two seconds - this is actually a persistent and curious phenomenon with many members on this board

 

[vernpace]

Chris,

You remind me of Olaf to the "T" - are you Olaf in disguise? Or, maybe Olaf was you in disguise? Just sayin...

 

[Chris Miller]

Steve Yu,

I can only add this, going back to this quote:

One of our trading partners is requiring their data (stored as MS SQL tables) be encrypted at rest on our server.

encryption at rest is a term that's not as clearly coined as what a bit or a byte is, but if you don't trust TDE (Transparent Data Encryption) is encryption at rest by some means, here are more references to it from MS:

https://learn.microsoft.com/en-us/power-platform/admin/about-encryption

https://learn.microsoft.com/en-us/azure/storage/common/storage-service-encryption

The first link also makes another statement about complince with FIPS 140-2:

Dataverse databases are using SQL TDE (Transparent Data Encryption, compliant with FIPS 140-2) to provide real-time I/O encryption and decryption of the data and log files for data encryption at-rest.

So here you have another reference of TDE being MS data at rest implementation. That's not just my opinion, I forward knowlegde here, with references to sources for checking it. What I/we don't know is the background of the demand your trading partner asked for, if they want that security level for reasons of data privacy that relate to FIPS then this might be best fit. You can always do more and thereby have that requirement fulfilled, too.

They should be more specific about what compliance they need for what reasons, like FIPS or GDPR or other such laws, because with such a lax specification you'll not be able to tell whether it's sufficient or not, but obviously you can discuss it further with them. I agree with vernspace that your question is pointing out lack of knowlegde and perhaps also certification. Asking us and acting accordingly might not suffice, even if it technically suffices, because such laws and regulations like FIPS or GDPR define more than just one aspect and may require your company storing data of clients to have employees that acquired a certificate of some sort.

Well, if you ask me, I'd not enter terrain I'm not sure about being able to stand a verification/audit, so what you really need isn't technical talk with programmers but a legal advice and schooling on such regulations on a broader level.

Chriss