SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transparent Database Encryption on all User Databases


Transparent Database Encryption on all User Databases

Author
Message
premkumardr
premkumardr
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 190
Comments posted to this topic are about the item Transparent Database Encryption on all User Databases
Andy DBA
Andy DBA
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 775
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
premkumardr
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 190
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
Perry Whittle
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91897 Visits: 17972
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
Andy DBA
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 775
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
premkumardr
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 190
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
premkumardr
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 190
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search