locked
Database not encrypted (Encryption Enabled state) RRS feed

  • Question

  • Our customer scans our network and had advised a finding that we have non-encrypted databases. Per the is_encrypted field I believe we can recrify this although I was wondering how this may affect our environment. 1) will this balloon the database backup size? 2) If the application is not using SSL, will this or is this the same type of encryption?

    Any clerification would be greatly appreciated.

    Wednesday, August 8, 2018 4:57 PM

Answers

  • Eland,

    • What effect on the database does changing "is_encrypted" to true have?  Is that actually the TDE setting?
    • Does this encryption effect the database/backup size or data retrieval from the application?

    You cannot simply set "is_encrypted" to 'true' without actually configuring TDE on your database. In other words, you'd have to follow the procedure to enable TDE on a database and once you run "Alter database set encryption ON" is when "is_encrypted" (in sys.databases) shows as "true". 

    As for the second part of your question, as the name "Transparent Data Encryption" implies, it's transparent to the calling apps. It's encryption of data-at-rest. As for backups, yes, it may affect backup compression if you compress your backups. encryption and backup compression do not get along well. If data is encrypted, the backup compression may not reduce the backup size to a greater degree as it would against unencrypted data. I believe this has changed starting SQL 2016. 

    The real question, as Tom asked, is do you want to enable TDE and be responsible to manage it just because the scanner had a finding? If there's a way to get around this problem by suppressing the encryption check on the scanner then I'd rather do that and not do TDE. But, if there's no way out and you got to enable TDE, then sure, go ahead and enable it and the scanner should then return the encryption check as true (I am assuming it queries sys.databases column 'is_encrypted'). 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by B3ach B0Y Thursday, August 9, 2018 1:20 PM
    Wednesday, August 8, 2018 10:45 PM

All replies

  • I believe customer has little idea about encryption. Ask him what type of encryption he is asking for. There are basically 3 

    1. Transparent data encryption

    2. Column level encryption

    2. Always Encrypted

    All 3 has pros and cons, one should not jut go ahead and encrypt a column or database.

    Database encryption does not gives mileage with backup compression so if you are using compression your compression ratio may not be that good. Encryption does not bloats backup.

    SSL is used for encrypting application connection it is not same as DB encryption


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Wednesday, August 8, 2018 5:06 PM
  • They are using DBProtect for the scan which presents the results:
    Check Details
    Database not encrypted  Is Encrypted=false;Database=<db name>
    Wednesday, August 8, 2018 5:19 PM
  • DBProtect is simply a policy scanner.  It is up to you/your customer to configure what is valid or not to alert on.  

    Are you required to have your databases encrypted?  If not, then don't do it.  Is it complaining about system databases?  Is it complaining about the customer's databases?

    Wednesday, August 8, 2018 7:37 PM
  • Since this is apparantly a matter of a checkbox where no one cares about whether data is actually protected or not, just enable TDE and be done it. ...although TDE requires Enterprise Edition. And, no, your data is not that much more protected than it used to be. But it is a checkbox.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 8, 2018 9:31 PM
  • Eland,

    • What effect on the database does changing "is_encrypted" to true have?  Is that actually the TDE setting?
    • Does this encryption effect the database/backup size or data retrieval from the application?
    Wednesday, August 8, 2018 9:56 PM
  • Eland,

    • What effect on the database does changing "is_encrypted" to true have?  Is that actually the TDE setting?
    • Does this encryption effect the database/backup size or data retrieval from the application?

    You cannot simply set "is_encrypted" to 'true' without actually configuring TDE on your database. In other words, you'd have to follow the procedure to enable TDE on a database and once you run "Alter database set encryption ON" is when "is_encrypted" (in sys.databases) shows as "true". 

    As for the second part of your question, as the name "Transparent Data Encryption" implies, it's transparent to the calling apps. It's encryption of data-at-rest. As for backups, yes, it may affect backup compression if you compress your backups. encryption and backup compression do not get along well. If data is encrypted, the backup compression may not reduce the backup size to a greater degree as it would against unencrypted data. I believe this has changed starting SQL 2016. 

    The real question, as Tom asked, is do you want to enable TDE and be responsible to manage it just because the scanner had a finding? If there's a way to get around this problem by suppressing the encryption check on the scanner then I'd rather do that and not do TDE. But, if there's no way out and you got to enable TDE, then sure, go ahead and enable it and the scanner should then return the encryption check as true (I am assuming it queries sys.databases column 'is_encrypted'). 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by B3ach B0Y Thursday, August 9, 2018 1:20 PM
    Wednesday, August 8, 2018 10:45 PM
  • Eland,

    • What effect on the database does changing "is_encrypted" to true have?  Is that actually the TDE setting?
    • Does this encryption effect the database/backup size or data retrieval from the application?

    Again are you going to make your database slower as compared to before by enabling TDE, can your users live up to that. If yes go ahead do it. Who manages this databases, how good are they with TDE ? There are lot of things which come into picture when you enable TDE. I would never simply follow some scan advise and enable TDE, I would discuss with stakeholders the pros and cons and would then enable it.

    Enabling TDE also encrypts tempdb and since tempdb is shared other databases who use tempdb would also be affected.

    You need to manage certificates which are used to encrypt database, make sure you backup it and keep it safe.

    TDE does not protects data in memory, only data at rest which is lying at disk. Its major advantage is no one can restore the backup file of database "just like that", they need to have certificate used to encrypt that database. So data theft is avoided to some extent. With TDE a sysadmin or one having DBO permission on database cans till see all the data.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, August 9, 2018 4:54 AM
  • Here's how I look at this (not in conflict with other replies, just my viewpoint):

    I would never enable encryption "just because". Be aware of what you are doing, test test test, and play with this so you understand how it work, *if* you decide to implement this. You don't want to end up with a database lost forever, just because you had some accident, reinstalled but couldn't restore your backups. I've seen this happening to folks. It isn't complicated, but it is important!

    And, make sure that you understand what you are protected from and what you *aren't* protected from. Implementing these things should always from from the business requirement, that that would dictate what you need to protect yourself from, which in turn would tell you what technology to use.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, August 9, 2018 11:45 AM