4 ms·

> - Encrypt data at rest and in transit. > - Log data access. > - Protect data against corruption. It's these three that I imagine MSSQL can help with. I bel

> - Encrypt data at rest and in transit. > - Log data access. > - Protect data against corruption. It's these three that I imagine MSSQL can help with. I believe it's got a built in storage encryption setup for tables/databases and can definitely be setup to log access to the database and queries run against HIPAA data.

dogma1138 10y ago
It's got cell level TDE if you want which also makes it transparent to the application which makes it pretty easy to implement as you don't need to handle the encryption within your app. All the key management, split knowledge, key rotation and most importantly access management and a full audit trail are also a "few clicks" away. Having a turn key solution for one of the most annoying things to implement in a correct manner should not be under-estimated. Anyone who done any compliance work knows just how many weirdly stitched solutions you can have. Something along the lines of "we manage split knowledge to our keys by splitting the key and keepass file into 2 files and storing them on USB keys in separate vaults".... Now technically this can pass a compliance check but this is a pretty darn silly situation simply because there isn't any better solution that works out of the box, this also quite often means that anytime you restart your DB you have an entire process of getting the keys, decrypting the tables copying their content into temporary views, re-encrypting the files and remove the keys from the server.. For MSSQL it wasn't that long ago that you have to do it "by hand" also, usually have a stored procedure that would handle the encryption and have a key which is then encrypted by several KEK's whilst it did afford some split knowledge it wasn't a true m of n which also meant that if you had more than 2 DBA's you had to make sure that the DBA's are know all of the KEK's and not be in a situation where all 3 DBA's that are not off during that week know the exact same password for the KEK...
- closeparen 10y ago
  What security benefit does TDE provide over full disk encryption on the database server?
  - dflenniken 10y ago
    I think it's more a performance benefit to have MSSQL be aware of and handling encryption directly. Of note in the article is how much they bypass the host OS for things like disk read-cache etc.
  - dogma1138 10y ago
    Full disk encryption isn't sufficient for complience (at least pci) as you need to encrypt the sensitive data in use also and manage the keys in a very specific manner. FDE won't even work for data at rest unless you can do split keys and auditing. This isn't about security this is about complience.
closeparen 10y ago
How can the access log help you? Presumably only the sysadmin and the EMR application should have SQL accounts; the only person this guards against is the sysadmin, unless the application passes through the user ID it's querying on behalf of somehow? Otherwise, you know that a query happened but not who ran it.
- dogma1138 10y ago
  You need to be able to trace individual queries which MSSQL also makes it easy since you can pass the user identity and map it to a set of credentials on the DB side automatically. But in general again this isn't about security it's about complience so it's more about doing things by the book and a very specific one.

dogma1138 10y ago

It's got cell level TDE if you want which also makes it transparent to the application which makes it pretty easy to implement as you don't need to handle the encryption within your app. All the key management, split knowledge, key rotation and most importantly access management and a full audit trail are also a "few clicks" away. Having a turn key solution for one of the most annoying things to implement in a correct manner should not be under-estimated. Anyone who done any compliance work knows just how many weirdly stitched solutions you can have. Something along the lines of "we manage split knowledge to our keys by splitting the key and keepass file into 2 files and storing them on USB keys in separate vaults".... Now technically this can pass a compliance check but this is a pretty darn silly situation simply because there isn't any better solution that works out of the box, this also quite often means that anytime you restart your DB you have an entire process of getting the keys, decrypting the tables copying their content into temporary views, re-encrypting the files and remove the keys from the server.. For MSSQL it wasn't that long ago that you have to do it "by hand" also, usually have a stored procedure that would handle the encryption and have a key which is then encrypted by several KEK's whilst it did afford some split knowledge it wasn't a true m of n which also meant that if you had more than 2 DBA's you had to make sure that the DBA's are know all of the KEK's and not be in a situation where all 3 DBA's that are not off during that week know the exact same password for the KEK...

closeparen 10y ago
What security benefit does TDE provide over full disk encryption on the database server?
- dflenniken 10y ago
  I think it's more a performance benefit to have MSSQL be aware of and handling encryption directly. Of note in the article is how much they bypass the host OS for things like disk read-cache etc.
- dogma1138 10y ago
  Full disk encryption isn't sufficient for complience (at least pci) as you need to encrypt the sensitive data in use also and manage the keys in a very specific manner. FDE won't even work for data at rest unless you can do split keys and auditing. This isn't about security this is about complience.

closeparen 10y ago

How can the access log help you? Presumably only the sysadmin and the EMR application should have SQL accounts; the only person this guards against is the sysadmin, unless the application passes through the user ID it's querying on behalf of somehow? Otherwise, you know that a query happened but not who ran it.

dogma1138 10y ago
You need to be able to trace individual queries which MSSQL also makes it easy since you can pass the user identity and map it to a set of credentials on the DB side automatically. But in general again this isn't about security it's about complience so it's more about doing things by the book and a very specific one.