Reset Domain Admin Password

I had to change my admin password on my development domain recently... Then promptly forgot it. How do you over come this? I thought that I would have to reinstall Windows on my DC, and redo all of my domain work. Turns out there is a hack.

First to make this solution work you need to have a local account that has admin rights to the OS. This can be the built in Administrator user that is created on install, but if you are like most people that account has been disabled. If you have no local accounts active with admin access, you are screwed. Go find some third party hackerware that will hack your domain and leave your vulnerable to any attack the hackerware wants to put on.

Anyway I always create an account that is a local admin on my servers for various reasons. This account is not named administrator and is set up according to best practices. So what do?

First you need to get out your Windows Server 2008 R2 DVD. You boot to this disk and select the repair option.
You are then given several options. You want to click on the Command Prompt option.

Things get slightly tricky here, because it is ambiguous what your drives are called. On a physical server, the C drive is most likely C, but if you are dealing with a HyperV or a VMWare server, C just might not be C. You need to find your OS dive and navigate to your Windows\System32 directory.

Here you will need to do some trickery. You need to be able to get to the command prompt from the log in screen when you get back to normal mode. So we have to do some fun stuff in order to get this to happen. So... When you look at your log on screen what do you have to work with? You have the two text boxes, the switch user button, the submit button, and (ta da!!) the Ease of Access button. We need to rename the command prompt application to the Ease of Access application, so that when we press the Ease of Access button on the log in screen the command prompt opens.

So from your command prompt in the recovery console rename the utilman.exe to utilman.exe.bak and then rename cmd.exe to utilman.exe. Reboot

When you get to the log on screen, simply click on the Ease of Access button. That will launch the command prompt. From the prompt type "user administrator NewPass123" That's it! Log in now with your new password.

Pivot Tables

For the last few weeks I have been building a web site that would display parent companies with the services they offered. The kicker is that I needed to differentiate if that service was provided by the parent company or some sub contracting company.

So I needed a variety of tables in the database to keep track of the data. First I needed a table to keep all of the various companies data in. This table also contained a flag to differentiate what was a parent company.
Next I needed a table to keep the services in. Then I needed a couple of look up tables that matched services to companies, and sub contracting companies with the parent companies. Lots of tables.

My requirement was to display the data in a grid that had the services run across the top, the parent companies run along the left, and a check box in cells where the company could provide the services.
At any time the services could be added to or removed, so I was left with the prospect of building the table from a set of four lists.
I first did this in C#, with several ForEach loops. Easy enough, and was fine for small amounts of data. Then word came down that another team saw the web site and wanted to use it for their parent company/sub contractors. This team had been in existence for nearly 10 years, so they had hundreds of services and thousands of companies. My C# ForEach loop solution would not scale well for this much data. The performance would be unacceptably slow. What to do? Use the power of SQL server and feed the roll up data to the data service. How?

My first problem was, how to take a table and use its rows as columns. There is no foreach loop in TSQL, so how to solve? With the release of SQL 2005 Microsoft gave us a neat little function for just such a problem. It is the PIVOT function:
SELECT <non-pivoted column>,
 [first pivoted column] AS <column name>,
 [second pivoted column] AS <column name>,
 ...
 [last pivoted column] AS <column name>
FROM
 (<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
 <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
 ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Looks worse than it is. So we start with my services table:

ServiceID	ServiceName
1	Engine
2	Fuel
3	Wheels
4	Wings

Company Table:

CompanyID	CompanyName	HoldingCompany
1	Bob's Holding Company	true
2	Jim's SubContractor	false
3	Matt's SubContractor	false
4	Bruce's Holding Company	true

Service Match Table:

ServiceMatchID	CompanyID	ServiceID
1	1	1
2	4	1
3	2	2
4	3	3

Company Match Table

CompanyMatchID	ParentCompanyID	ContractorCompanyID
1	1	2
2	4	3

Fun right? Anyway what we first need to do is craft the data table that the PIVOT function will use. What we want is to match up our Companies with our Services, while joining on the other tables to make the return human readable.

SELECT CompanyName, Company.CompanyID, ServiceName, Services.ServiceID, HoldingCompany, HoldingCompanySubContractMatch.SubContractID,
CellType =
CASE
WHEN HoldingCompany = true THEN 'M'
WHEN HoldingCompany = false THEN 'S'
END
FROM ServiceCompanyMatch
JOIN Services ON ServiceCompanyMatch.QualID = Service.ServiceID
JOIN Company ON ServiceCompanyMatch.CompanyID = Company.CompanyID
LEFT OUTER JOIN HoldingSubContractMatch ON company.CompanyID = HoldingSubContractMatch.MOAHolderID

This gives us:

CompanyName	CompanyID	ServiceName	ServiceID	HoldingCompany	SubContractID	CellType
Bob's Holding Company	1	Engine	1	true	2	M
Bruce's Holding Company	4	Engine	1	true	NULL	M
Jim's SubContractor	2	Fuel	2	false	NULL	S
Matt's SubContractor	3	Wheels	3	false	NULL	S

Now we have our data table. Now we have to make some decisions. First how do we want our pivot table to look? We want the columns to be the Services, and the rows to be the companies. So we want to pivot on the the ServiceID column, but display the CellType information.
We also want to be very careful with the Services table. These rows can change at any point, so we have to dynamically load these in to the PIVOT statement.
There is no ForEach loop in TSQL so we have to do something else to load all of the columns. To do that we need to use a little trick, called dynamic SQL. We load the function as a varchar in to an EXEC statement. Inside this varchar we load a flattened list of the rows. We use the COALESCE statement to do this. So, in pieces, here is our statement:

DECLARE @ColumnHeader varchar(MAX)
SELECT @ColumnHeader=
COALESCE(
@ColumnHeader + ',[' +Services.ServiceName+']','['+Services.ServiceName+']'
)
FROM Services

First, we declare a varchar to contain the list of the would be columns. Then we use COALESCE to put the rows in to a comma delimited string.

DECLARE @Pivot varchar(MAX)
SET @Pivot = N'
SELECT
*
FROM
(
SELECT CompanyName, Company.CompanyID, QualName, Quals.QualID, MOAHolder, MOAHolderSubContractMatch.SubContractID,
CellType = CASE
WHEN MOAHolder = 1 then ''M''
WHEN MOAHolder = 0 then ''S''
END
FROM QualCompanyMatch
JOIN Quals on QualCompanyMatch.QualID = quals.QualID
JOIN Company on QualCompanyMatch.CompanyID = Company.CompanyID
LEFT OUTER JOIN MOAHolderSubContractMatch on company.CompanyID = MOAHolderSubContractMatch.MOAHolderID
) DataTable

I then declare the varchar that will be executed in the EXEC statement, and set it to the string that follows, thus the opening tick mark after the N.
Then we grab the entire table from the SELECT statement that we figured out above. We call that result set DataTable.

PIVOT
(
MAX(CellType) FOR ServiceName
IN
(
'+@ColumnHeader+'
)
)as PivotTable'
exec (@Pivot)

Here we pivot the columns and load the column names from the varchar variable that we loaded earlier.
In the syntax of PIVOT we need to have an aggregation function over the column that contains the data we want to display in the rows. In my example, the aggregated column is a varchar, so we use the MAX aggregation function. This does nothing to a varchar, so the data remains untouched.
Next we have the FOR statement determining the column that we wish to pivot on. SQL takes the data in the rows of this column, and sets them as the columns in the new result set we are creating.
The IN statement then names the columns, we load in the @ColumnHeader vachar that we created in the first part of the statement.
Finally we name the new result set "PivotTable", and close the tick. We then run the dynamically crated command in the EXEC function.
The result is

CompanyName	CompanyID	ServiceID	HoldingCompany	SubContractID	Engine	Fuel	Wheels	Wings
Bob's Holding Company	1	1	true	2	M	NULL	NULL	NULL
Bruce's Holding Company	4	1	true	3	M	NULL	NULL	NULL
Jim's SubContractor	2	2	false	NULL	NULL	S	NULL	NULL
Matt's SubContractor	3	3	false	NULL	NULL	NULL	S	NULL

So we now have a data source that now can be called as a stored procedure or a view. It contains everything we need to display the information that we want it to.
I am still working on getting the result set to display with the data rolled up in to the parent companies. If I figure it out I will post!