SQL_Guy

This is the SQL Server Blog for Perry Whittle

How To Create a Corrupt SQL Server Database for Test Purposes

clock April 16, 2012 13:52 by author TopDog

It's quite possible that at some point you may want to have the use of a corrupted SQL Server database for test or DR practice purposes. In fact, this can also aid in the recovery steps for a corrupted database once the file and object architectures are fully understood.
Don't ever be tempted to just go run the "Repair_allow_data_loss" clause of DBCC CHECKDB, it does what it says on the tin. I was recently involved on a forum post where a user had done exactly that, without understanding the ramifications or understanding the output of DBCC CHECKDB.

To understand more we will create and corrupt our own test database, this is very easy to achieve as I will detail below. 

For this exercise we merely need a Hex editor and the use of a SQL Server instance.

Note: do not use a Production SQL Server instance!

I have chosen XVI32 as this editor is free of charge and requires no installation to take place, simply place the files into a folder and create a shortcut to the program.

The core database will be created using the following simple script. We’ll go through the process in stages with diagrams to see exactly what’s happening. Start with the code below;

 

Don't forget to modify any drive letters and paths before executing the script ;-)

USE [master]

CREATE DATABASE [Corrupt2K8] ON PRIMARY

( NAME =N'Corrupt2K8', FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Corrupt2K8.mdf',

SIZE = 524288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Corrupt2K8_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Corrupt2K8_log.ldf',

SIZE = 262144KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)

GO

USE [Corrupt2K8]

GO

CREATE TABLE dbo.NoddyTable(

NoddyID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()

, NoddyName VARCHAR(128) NULL

, NoddyInt BIGINT NULL

, NoddyDate DATETIME NULL

) 

GO

INSERT INTO dbo.NoddyTable

SELECT NEWID(), name, ROUND(RAND(object_id)*856542, 0), GETDATE() FROM sys.columns

UNION ALL

SELECT NEWID(), name, ROUND(RAND(object_id)* 1048576, 0), GETDATE() FROM sys.columns

 

ALTER TABLE dbo.NoddyTable ADD CONSTRAINT PK_NoddyID

PRIMARY KEY CLUSTERED (NoddyID)

WITH (IGNORE_DUP_KEY=OFF)

 

CREATE NONCLUSTERED INDEX IDX_NoddyName_NoddyDate

ON dbo.NoddyTable(NoddyName, NoddyDate)

WHERE NoddyName IN ('password','length','created','crtype','offset','intprop')

 

CREATE NONCLUSTERED INDEX IDX_NoddyDate

ON dbo.NoddyTable(NoddyDate)

 

Once you have the database created, take a full backup followed by a differential and then a transaction log backup, you may then use these in future testing scenarios.

We now want to choose an object as the target of our corruption exercise. I am going to choose a non clustered index on the table 'dbo.NoddyTable', to get a list of indexes on this table use the following query;

SELECT OBJECT_NAME(object_id), name, index_id, type_desc FROM sys.indexes

ORDER BY 1

I will be using the non-clustered index 'IDX_NoddyDate', this has an index id of 3. To find details of the page numbers in use by this index we now need to turn to an undocumented DBCC command 'DBCC IND'. Full details of how to use this command may be found at the links below but basically this is used as follows;

DBCC IND (DatabaseName,'tablename', index_id)

So, I have

DBCC IND (Corrupt2K8,'dbo.NoddyTable', 3)

Below is the output from the command above

 

 

I'm going to pick a page of page type 2 (an index page), my chosen page number here is 174.

Next I need to go view a dump of this page just to have a look at the records it contains. This requires the use of another undocumented DBCC command called DBCC PAGE. Again full details of this are in the links below but basically it's used as follows;

DBCC PAGE (DatabaseName, filenumber, pagenumber, printoption)

So, I have the following code

 --switch on client output first

DBCC TRACEON(3604)

--now read the page

DBCC PAGE (Corrupt2K8, 1, 174, 1)

This is the page header;

 

I'm going to home in on slot7 or record 7. I'll use the Hex editor to modify this record in the page which will then generate an error when DBCC CHECKDB is run. The detail for slot 7 looks as follows;

 

So, to hack the record at slot 7 on page 174, I first need to work out some figures to find the address locations within the file. Convert the record offset (indicated in the screenshot above) from hex to decimal first and then the address for slot 7 is calculated as follows

page number x num of bytes per page + record offset

This equates to 174 x 8192 + 292 = 1425700

Take the database offline and now open the primary data file using XVI32. From the File menu select open and then browse to the MDF file.

 

Now, from the File menu select "Address" > "Goto". In the dialog box which appears ensure you select the decimal radio button and enter the address which was calculated above, in my case 1425700. As you can see from the screenshot below, the editor has placed me at the start of my chosen record in page 174.

 

This record has a length of 28 bytes which was detailed in the page dump we did earlier, now to modify the record. First switch the editor to Text and Overwrite Mode if it isn't already. From the File menu ensure "Tools" > "Text Mode" and "Tools" > "Overwrite" are selected. Now I'll mark the blocks I wish to mangle. To do this, from the File menu select "Edit" > "Block <n> chars". Switching to decimal, I enter the record length of 28. The blocks have now been marked in red as shown below 

 

Now, to overwrite the record in slot 7 as shown below, in text mode type a simple string

 

Now click "File" > "Exit" and save the file when prompted to do so. In SQL Server you may now bring the database back online. We'll re run the page dump and check the results which are shown below;

 

Well, as we can see above the record was modified in the anticipated location, of course the only part hosed here is the non clustered index which is easily fixed by dropping and re creating it. What does DBCC CHECKDB show us?

 

Now you have a corrupt database which you may use for your DR and script tests. Give this a go in your test systems and by all means post back if you're stuck. 

Credits

Information on these undocumented procedures was digested from Paul Randal's blogs at the following links

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/08/09/692806.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

Warnings

Obviously you should not perform this on your production databases\servers, complete all tests in your offline environments. I will not be held responsible for those who wish to apply this in online environments Wink



Using sp_change_users_login to fix orphaned logins

clock April 14, 2012 13:25 by author pezzar

Picture the scenario, you've moved the enterprises mission critical database from one SQL Server instance to another. You bring the database online, all looks good. Alas, however, you have not created the server logins to provide the necessary database access. The boss wants to know why the application server cannot connect to the database, she's wanting heads to roll unless the application is back up and available in the next 2 minutes!!

 

Luckily, up to and including SQL Server 2012 you have a system stored procedure which handles the mapping of new and existing logins to database users, this procedure is deprecated in future versions of SQL Server but still useful in older versions, How does it work?

Firstly, the stored procedure sp_change_users_login is only used for resolving SQL Server orphaned logins, it takes a number of parameters which differ based upon the action to be performed. The syntax is as follows;

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]

Valid actions are;

Auto_Fix - this will take the username and password parameters only. If the procedure finds an existing login with the same name the password will be ignored and the user account mapped. If the login does not exist it will be created with the specified parameters.

Report - this action takes no parameters, execute the procedure under your chosen database context and it will report any orphaned users within that database. The accounts are determined as orphaned if the unique SIDs do not match between the server level login and the database user, the name is not used here. You can check this by comparing the catalogs sys.server_principals in the Master database for the server login and sys.database_principals inside each user database for the database user. The report returns the ophaned user name and SID.

Update_One - this action is used to specifically map a database user back to an existing server level login, the login must exist.

  

Back to our scenario, you've contacted the application administrator and he's given you the account password from the web.config file, how do you proceed? We have the password and we know the login name which is the same as our database user, we issue the following query,

exec sp_change_users_login 'Auto_Fix', 'Jon', NULL, 'P@ssw0rd1'

 

The login is supplied as a NULL parameter as this will be created from the user name parameter, If the new login already existed the password parameter would have been ignored. Now, the more astute amongst you would have noticed that the server login and the database user don't have to have the same name, although possibly they should do for clarity. The default for the procedure using Auto_Fix will create a login of the same name, to map the user to a login of a different name, create the login  manually using CREATE LOGIN and then map the acccount. For example, to map the database user Jon to the new server login Bob use the following query,

 exec sp_change_users_login 'Update_One', 'Jon', 'Bob'

 

As an alternative to our scenario, the junior DBA in their panic, has manually created a new server login and is having trouble mapping the login to the database as the user already exists. Fortunately you catch him right before he drops the database user and avoid losing all granted permissions. You do this using the following query

exec sp_change_users_login 'Update_One', 'App_User', 'App_User'

 

Great, the application is back online, the boss declared you a hero and your peers are holding you high on a pedastool. And to think, you very nearly panicked and started dropping users from the database. Next time, you'll script the accounts from the source server and ensure they exist on the target server, hopefully! Wink