Encryption at rest documentation?

ERP 10
#1

I am wanting to explore the setup for configuring the Epicor database for encryption at rest to help satisfy some regulatory concerns. I did see this link posted:
Transparent Data Encryption (TDE) - SQL Server | Microsoft Docs

And I’ve read several topics here saying that 10.2 supports it out of the box.
What I am unsure of is any Epicor documentation to configure this, and any steps to be aware of?
I have not used this functionality before and I have this fear of encrypting the database without knowing how to decrypt it :grimacing:

Any conversations on going from non-encrypted at rest to encrypted would be much appreciated!

#2

It’s either in the implementation guide or the Admin guide. Another place to check is the Admin tool help.

Test it first on a throw away database first, sorry for stating the obvious.

1 Like
#3

Thanks Simon
I’m on the struggle bus with this one. I can’t seem to find any documentation in the implementation guide or in the system administration guide relating to database encryption, only relating to transport layer encryption.

#4

All that Epicor Does is support TDE there’s no special setup you do all the stuff on the SQL Side and just tell Epicor About it (as I understand it)

1 Like
#5

Is this what you’re looking for?

image
image784×290 7.34 KB

That’s in EAC (Application Server -> Application Server Settings)

It is described in the New Install Guide:
(section 5.3, step 7 of the one for version 10.2.300)

image
image

#6

No, that’s at the OS level I believe. TDE is at the database transaction level

#7

Well… The EAC must somehow be aware of it, as its status is visible in EAC:

image
image912×633 63 KB

Does EAC need to store a key so that it can access the encrypted DB? Or is this something entirely within SQL? And as long as the “user” (the App Server in EAC) has access, SQL encrypts and decrypts the data it passes to the App Server? The encryption part really just refers to how the data is stored in the DB?

Just trying to follow along and learn something new.

#8

As I understand it, TDE encrypts the data in memory before it’s written to the disk, then decrypts it upon retrieval (again, in memory).
These are great questions, and I have no idea what role EAC plays with it. If it stored the key, that would be great

#9

I’m going to dig into this in more detail later day after I’ve done some Azure courses

1 Like
#10

I remember getting excited about this when if first became available in SQL. The company I worked for at the time was in the FSI software field.

Here’s a link from MS which explains. TDE in pretty good detail

The crux of it is it is at rest DE at the page level of the database. To restore the database to another server, you need the backup, the certificate and the related private key.

Size of the db doesn’t change, but my guess is that there would be some performance hit.

Stay tuned.

1 Like
#11

I’ve got a feeling that the admin console just looks at the sys.dm_database_encryption_keys DMV or some custom view/stored procedure that relates the database name to the database_id and returns the encryption state in SQL, you would not want Epicor storing its own copy of the key that would be bad.

I’m still digging into this and doing some testing, researching.

If you get a chance or have access to, could you run the following on a demo database.

select count(*)
from sys.default_constraints con
    left outer join sys.objects t
        on con.parent_object_id = t.object_id
    left outer join sys.all_columns col
        on con.parent_column_id = col.column_id
        and con.parent_object_id = col.object_id

This counts the number of columns with default constraints. You can’t use Always On Encryption on these columns, which I was hoping you might be able to do, I suspect this is why Data Masking might have been introduced. It would take a lot of re-architecting on the database and the app side to sort that out, with modern development tools today, you might be in with a chance.

My document is going to include the three areas around TSQL with regards to Encryption. TDE, Backup and Always on encryption. I was hoping that you might be able to utilize Always on Encryption, but alas I don’t think that will fly, at least not yet.

EDIT 2

Hello @Aaron_Moreng,
Here is the first cut. I hope you find it useful. I know I enjoyed digging into this a bit deeper, a bit of a can of worms actually. Those MKEs, DEKs, and EKM OMG! :exploding_head: The attached document outlines the different encryption methods with various links to examples, it didn’t make sense to reinvent the wheel. I perhaps could have gone into more depth on Column/Cell level encryption, where do you stop… Be as harsh a critic as you need.

Using SQL Server Transparent Data Encryption with E10

1 Like