Cursors to apply TDE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Cursors to apply TDE

Author

Topic

vbwrangler
Starting Member

9 Posts

Posted - 2011-06-21 : 08:56:46

I am still a little clumsy with cursors. I am trying to set a catch all script for the database to make sure the TDE is set. My concept is to iterate through the database and identify if Encryption has been set; if not then to set it on that database. The meat of the code would look something like this:

IF EXISTS (SELECT DBName = DB_NAME(database_id), encryption_state
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DBNAME
AND encryption_state NOT IN (2,3,5))
ALTER DATABASE DBNAME
SET ENCRYPTION ON;
GO

I then would like to cycle through the database but I can't quite get the Cursor quite right:

DECLARE
@SqlStatement nvarchar(MAX)
,@DatabaseName sysname;

IF OBJECT_ID(N'tempdb..#DatabaseEncryption') IS NOT NULL
DROP TABLE #DatabaseEncryption;

CREATE TABLE #DatabaseEncryption(
DATABASE_NAME sysname
,LOGICAL_NAME sysname
,ENCRYPTION_ST int);

DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases;

OPEN DatabaseList;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
IF @@FETCH_STATUS = -1 BREAK;
SET @SqlStatement = N'USE '
+ QUOTENAME(@DatabaseName)
+ CHAR(13)+ CHAR(10)

+ CHAR(13)+ CHAR(10)
+ N'IF EXISTS (SELECT DBName = DB_NAME(database_id), encryption_state
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = '
+ QUOTENAME(@DatabaseName)
+ 'AND encryption_state NOT IN (2,3,5))
ALTER DATABASE '
+ QUOTENAME(@DatabaseName)
+ 'SET ENCRYPTION ON;';

EXECUTE(@SqlStatement);

END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

SELECT * FROM #DatabaseEncryption;

DROP TABLE #DatabaseEncryption;;
GO

Your thoughts would be appreciated.

vbwrangler

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-21 : 09:25:25

What's wrong with:

SELECT name, is_encrypted FROM sys.databases

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-21 : 09:35:10

I would set the output to text, and execute this, then copy/paste the results back into the query editor and execute it


SELECT	'ALTER DATABASE [' + d.name + '] SET ENCRYPTION ON;
GO'
FROM	sys.databases d
LEFT JOIN
	sys.dm_database_encryption_keys k
On	k.database_id = d.database_id
WHERE	k.database_id is NULL
AND	d.database_id > 4
AND	d.name <> 'distribution'
OR	isNULL(k.encryption_state, 0) NOT IN (2,3,5)

But if you insist on a cursor:


DECLARE @db sysname

DECLARE c CURSOR
READ_ONLY
FOR
	SELECT	d.name
	FROM	sys.databases d
	LEFT JOIN
		sys.dm_database_encryption_keys k
	On	k.database_id = d.database_id
	WHERE	k.database_id is NULL
	AND	d.database_id > 4
	AND	d.name <> 'distribution'
	OR	isNULL(k.encryption_state, 0) NOT IN (2,3,5)
OPEN  c
FETCH NEXT FROM c INTO @db
WHILE	@@FETCH_STATUS = 0
BEGIN
	EXEC ('ALTER DATABASE [' + @db + '] SET ENCRYPTION ON;')
	FETCH NEXT FROM c INTO @db
END
CLOSE c
DEALLOCATE c

And Rob is right. That would simplify the query a bit

vbwrangler
Starting Member

9 Posts

Posted - 2011-06-21 : 09:35:41

Very good that is certainly more simplistic.

But the crux of my post was to iterate through your list and apply
ALTER DBName SET ENCRYPTION ON;
To any database where the value of is_encrypted =0 other than master, model, and msdb of course. Referring to the database name for a command, inside the cursor loop is where I am struggling.

quote:
Originally posted by robvolk

What's wrong with:

SELECT name, is_encrypted FROM sys.databases

vbwrangler

vbwrangler
Starting Member

9 Posts

Posted - 2011-06-21 : 09:43:17

Thanks Russel,

I add a little to your code:

SELECT 'ALTER DATABASE [' + d.name + '] SET ENCRYPTION ON;
GO'
FROM sys.databases d
LEFT JOIN
sys.dm_database_encryption_keys k
On k.database_id = d.database_id
WHERE k.database_id is NULL
AND d.database_id > 4
AND d.name NOT in ('distribution', 'master', 'model', 'msdb', 'tempdb')
-- tempdb is applied to TDE if any other database has TDE applied.
OR isNULL(k.encryption_state, 0) NOT IN (2,3,5)

vbwrangler

vbwrangler

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-21 : 09:46:55

master = 1
tempdb = 2
model = 3
msdb = 4

But your addition won't hurt :)

You might also want to exclude reportserver and reportservertempdb if RS is installed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources