SQL Server Encryption

  • Looking for some advice...

    I'm being asked to look into SQL encryption. I'm not sure yet if they want certain items or the entire database encrypted. I started looking at a few MS links including this one https://msdn.microsoft.com/en-us/library/ms189586.aspx.

    In the end I'm looking for the best solution that wont impede performance. I also know that without the proper pass-phrase or encryption key you run the risk of data loss.

  • A title from Joe Jackson's excellent album "Body and Soul" comes to mind: You can't get what you want, before you know what you want.

    None of the encryption solutions in released SQL Server versions are particularly satisfactory in my opinion, but it depends on what you want to achieve.

    Transperant Data Encryption (TDE) available in Enterprise Edition since SQL 2008, requires no changes to the application, since it is, well, transperant. The great thing with it is that if someone walks away with a backup or a database file, they can make no use of it - unless they also snatched the keys that protects the database. If they access to the files, they may also have access to the keys. And if someone comes into the database by SQL injection, everything is visible to them.

    With cell-level encryption it is different, as without access to the keys, the data is encrypted. But either data are protected by keys on the server, and anyone with access to the server can access the data. Or it is protected by passphrases, and now is the the question where do you put the passphrases?

    SQL 2016, currently in CTP, changes the game by introducing Always Encrypted. Here data is encrypted in the API, and SQL Server only knows that data is encrypted. With access only to the database, you can make out nothing of the data.

    But in the end it depends on what you want to achieve. Oftentimes it seems that people think "we need to encrypt" the database, without any real understanding of why. To this end, TDE is the best solution, since it permits you to tick off a box on the list without any cost in application changes and modest cost in performance. Nevermind that the security gain may be negligible.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thank you for the info, it helped a lot. We had an audit recently and they didn't like the fact that data was not encrypted regardless of other security measures. I agree.. when asked to "encrypt this data" it can open a can of worms. I will look at TDE next week and followup here with you when I start playing around with it.

    One more question.. Can TDE be applied to an existing database or is TDE something that needs to be configured/started when the SQL server is setup?

  • Always be cautious of audit recommendations, most companies literally have a template that they follow. TDE is an enterprise edition feature so if you aren't running EE then you can't use it. Also given that EE is roughly 4 TIMES the cost of standard it is hard to justify the costs associated with EE just for TDE. Furthermore I would argue that VERY VERY VERY few databases actually need to be fully encrypted, there are usually just a few columns in 1-3 tables that are truly sensitive and TDE will NOT protect them from a data breach where the data can be queried live. TDE ONLY protects data at rest with the database engine turned off or from backups. However encrypting those individual fields ALSO protects your data at rest. I'll be really clear, I view TDE as a lazy and largely ineffective way to do encryption that provides little REAL protection and is little more than feel good protection.

    TDE can be applied after the fact as far as I know. You will want to be sure you have copies of all the keys because if you don't you won't ever be able to restore if the machine dies. Should you go down the TDE path I strongly recommend testing restores on a separate machine just to document the procedure and prove you can. Failure to do so is easily capable of putting smaller businesses OUT of business because their data is gone.

    CEWII

  • TDE can be applied to an existing database. Beware that it does not apply to FILESTREAM data.

    I agree with everything that Elliot said and not the least: be careful with your keys. If you lose them, you lose the data.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • I know that this is a month old post but. . . I am also going to be enabling TDE on databases and was wonder what best practices people follow?

    i.e where/how do you secure you keys and certs.

    Also FIPS is turned on the servers so does that limit the encrypiton options to me b/c i don't want to wait an hour to find out that the encryption I chose isn't FIPS compliant.

    Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply