Transparent Database Encryption on all User Databases

  • premkumardr

    Say Hey Kid

    Points: 708

    Comments posted to this topic are about the item Transparent Database Encryption on all User Databases

  • Andy DBA

    SSCommitted

    Points: 1776

    I wrote a similar script and I used a wait loop to display encryption progress every 2 seconds.  I found it helpful when encrypting large databases so I can tell that the server is not hung up and also get a feel for about  when it will be done.  Fair warning: For very large databases it may say "100% complete..." for a few seconds before it finally finishes.

    To display progress, the following code can be pasted between the "Exec (@sql)"  and "FETCH NEXT FROM apply_tde into @dbname" commands in Prem Kumar's script:

    /* Show encryption progress */
    Declare @PercentDone as int
    Declare @EncState as int = 0
    Declare @StatusMsg as nvarchar(255)

    WHILE @EncState not in (3,-1)
    BEGIN
        SELECT @EncState = coalesce (max(encryption_state), -1), @PercentDone = coalesce (max(percent_complete),0)
        FROM sys.dm_database_encryption_keys
        WHERE db_name(database_id) = @dbname

        set @StatusMsg =
       N'Encrypting ' +
       @dbname +
       ' database using AZ_TDE_PRD certificate. ' +
       cast(@PercentDone as nvarchar(4)) +
       N'%% complete...'

        RAISERROR( @StatusMsg, -1, -1) WITH NOWAIT;
        WAITFOR DELAY '00:00:02'
    END
    IF @EncState = -1 RAISERROR( N'No encryption key found!', -1, -1) WITH NOWAIT;

  • premkumardr

    Say Hey Kid

    Points: 708

    Andy DBA - Thursday, May 25, 2017 2:23 PM

    I wrote a similar script and I used a wait loop to display encryption progress every 2 seconds.  I found it helpful when encrypting large databases so I can tell that the server is not hung up and also get a feel for about when it will be done.  Fair warning: For very large databases it may say "100% complete..." for a few seconds before it finally finishes.

    To display progress, the following code can be pasted between the "Exec (@sql)"  and "FETCH NEXT FROM apply_tde into @dbname" commands in Prem Kumar's script:

    /* Show encryption progress */
    Declare @PercentDone as int
    Declare @EncState as int = 0
    Declare @StatusMsg as nvarchar(255)

    WHILE @EncState not in (3,-1)
    BEGIN
        SELECT @EncState = coalesce (max(encryption_state), -1), @PercentDone = coalesce (max(percent_complete),0)
        FROM sys.dm_database_encryption_keys
        WHERE db_name(database_id) = @dbname

        set @StatusMsg =
       N'Encrypting ' +
       @dbname +
       ' database using AZ_TDE_PRD certificate. ' +
       cast(@PercentDone as nvarchar(4)) +
       N'%% complete...'

        RAISERROR( @StatusMsg, -1, -1) WITH NOWAIT;
        WAITFOR DELAY '00:00:02'
    END
    IF @EncState = -1 RAISERROR( N'No encryption key found!', -1, -1) WITH NOWAIT;

    Reply premkumardr - Yes, I agree this will help in fetch the right status when the database is getting encrypted

  • Perry Whittle

    SSC Guru

    Points: 233779

    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

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Andy DBA

    SSCommitted

    Points: 1776

    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.

  • premkumardr

    Say Hey Kid

    Points: 708

    Andy DBA - Tuesday, May 30, 2017 11:09 AM

    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.

    Sometimes it is an enterprise policy which is in place to apply TDE across all databases. It depends on the business request and is purely driven by the business owners.

  • premkumardr

    Say Hey Kid

    Points: 708

    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

    It is very important to analyse the need to implement the TDE on all user databases.For example, banking sector may have the requirement to implement the TDE across all the databases in a particular instance. This is purely business driven.

  • WHug

    Ten Centuries

    Points: 1197

    Andy, do you have a reference link for "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."

    I didn't find reference to this concern on the Microsoft Docs

Viewing 8 posts - 1 through 8 (of 8 total)

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