• Perry Whittle - Thursday, May 25, 2017 11:35 PM

    I really recommend you don't blindly enable TDE on all user databases on all of your servers, the performance and administrative overhead needs to be understood first.If you harden your sql server efficiently this should provide a high level of security.Why do you feel it necessary to implement TDE across the board

    I agree that it's prudent to do performance testing in a test environment before applying TDE to production systems.  You should also make sure to save off your encryption certificate and password back up files to a different place than the database server or where you keep your back ups and then delete them from the database server (and secondary server where applicable).  Additionally, you may want to make sure you can restore backups to a disaster recovery server and then document the process.

    Also, the encryption process does not encrypt existing data in the log file, so it's a good idea to flush the log file after encrypting the database.  Others, please chime in if you know of a better way, but the best way I'm aware of is to detach the database, rename the log file, and then re-attach it.  When re-attaching, SQL server will return a "File activation failure." warning that it can't find the log file, but it will go ahead and create a new blank one for you.  All data written to it after TDE implementation will be encrypted.  Be sure to take note of the database owner and log file size and growth settings, because they will be reset by the re-attach.  (Maxsize will be set to unlimited!)  I used the following interactive script to do this:

    /* Flush un-encrypted data from the log file */
    /* Use command mode and replace "myDB" with your database name */
    :setvar DatabaseName "myDB"

    /* Make sure to capture database owner, log file maxsize and log file growth settings prior to implementing following steps!!! */
    sp_helpdb $(DatabaseName);

    USE $(DatabaseName);
    GO

    CHECKPOINT;
    go

    USE MASTER;
    go

    EXEC master.dbo.sp_detach_db @dbname = N'$(DatabaseName)';
    GO

    /*STOP HERE Use file explorer to rename old T-log file to DELETETHIS.ldf */

    :setvar DatabaseName "myDB"

    CREATE DATABASE [$(DatabaseName)] ON
    ( FILENAME = N'R:\MSSQL11.MSSQLSERVER\MSSQL\DATA\$(DatabaseName).mdf' )
    FOR ATTACH;
    GO

    /* Ignore "File activation failure." warning message that log file can't be found */

    /* Replace asterisks with max size and growth of log file prior to detach */

    ALTER DATABASE $(DatabaseName) MODIFY FILE ( NAME = N'$(DatabaseName)_log', MAXSIZE = ***KB , FILEGROWTH = ***KB );
    GO

    USE $(DatabaseName);
    GO

    /* replace asterisks with original owner prior to detach */
    sp_changedbowner '***';
    GO

    /* Now use file explorer to delete DELETETHIS.ldf */

    Why do you feel it necessary to implement TDE across the board"  

    I'm lucky because the policy where I work only requires that databases with PII be encrypted, so I don't have to encrypt them all.  I don't know what the author's reasons are but it's not hard to imagine a scenario where every db on the server has PII or other sensitive information.  Even if "you harden your sql server efficiently", you may have vulnerabilities beyond your control with off-site back up archivals.
    Also, in some shops, DBAs may not be aware of the nature of the data being stored.  For example, end users might enter sensitive information in a comment field.

    Finally, TDE is not a "magic bullet".  TDE encrypts data stored in the file system, but unless other steps are taken, unencrypted data is still passed to/from the client application.