Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Instant File Initialization Security Risk

A while ago I blogged about instant file initialization. A colleague of mine pointed out that there is a small but non-zero risk involved with using this setting.

So I decided to put a demo together to test out the security risks involved.

I have a default instance and a named instance of SQL Server 2008 R2 running on my Windows 7 laptop. Both services are running under the local system account and therefore have the perform volume maintenance policy permissions and will use instant file initialization when creating new databases.

On the default instance create a new database:

CREATE DATABASE [SecurityTest] ON  PRIMARY 
( NAME = N'SecurityTest', FILENAME = N'C:\MSSQL\SQLData\SecurityTest.mdf' , SIZE = 2048MB)
 LOG ON 
( NAME = N'SecurityTest_log', FILENAME = N'C:\MSSQL\SQLLogs\SecurityTest_log.ldf' , SIZE = 1024MB)
GO

And a table in the database:

USE SecurityTest;
GO

CREATE TABLE PrivateData
(
	data VARCHAR(20)
);

And populate the table with some “sensitive data” (this takes a minute or so on my machine to run):

DECLARE @i INT = 1;

SET NOCOUNT ON;

WHILE @i < 100000
BEGIN
	INSERT INTO PrivateData VALUES ('TOP SECRET');
	SET @i += 1;
END

And then delete the database:

USE [master]
GO
ALTER DATABASE [SecurityTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SecurityTest]
GO

Now change to the named instance and create a new database in the same location as the first database:

CREATE DATABASE [SecurityTest] ON  PRIMARY 
( NAME = N'SecurityTest2', FILENAME = N'C:\MSSQL\SQLData\SecurityTest2.mdf' , SIZE = 2048MB)
 LOG ON 
( NAME = N'SecurityTest2_log', FILENAME = N'C:\MSSQL\SQLLogs\SecurityTest2_log.ldf' , SIZE = 1024MB)
GO

And have a scan through some of the pages in our new database:

DBCC TRACEON(3604) 
GO

DBCC PAGE('SecurityTest',1,300,2);

DBCC PAGE('SecurityTest',1,400,2);

You might have to experiment with different page numbers, but on my instance I get these results:

And:

Conclusion: This demonstrates the security risks involved with instant file initialization. These risks need to be balanced against the performance gains in a particular environment.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...