Backdoor TFS User Image and Email Manipulation

Caveats

• This approach is not for the uninitiated. Proceed at your own risk.
• Microsoft does not condone the direct manipulation of the TFS database. Doing so will void your Microsoft TFS Support Agreement.
• I have only performed limited testing on TFS 2013 Update 5 and cannot vouch for earlier or later versions. (Please comment below, with version details, if you find this approach successful or unsuccessful on another version of TFS.)
• I have only tested this with 'image/png' data. (Please comment below with other image types that you found successful or unsuccessful.)

Introduction

Though not condoned by Microsoft, SQL Manager can be used as a backdoor way to administer the TFS profiles for your TFS users. The settings for the User Profile are stored in the tbl_PropertyValue in the Tfs_TFSConfiguration database, with a transformed association to the tbl_Identity, and additional associations to the tbl_PropertyDefinition and tbl_PropertyArtifactKind tables.

First, Transform the Identity GUIDs to `ArtifactId`s

The Tfs_TFSConfituration database has a tbl_Identity table whose entries are correlated to the tbl_PropertyValue by a transformation of the Id column of the first table into the ArtifactId of the second.

The tbl_Identity can have duplicate rows for the same user. I found that the rows with the highest SequenceId were the active identities. The following query can be used to extract the active user id, or AccountName with the Id that is a GUID and needs to be transformed into the binary format used by the ArtifactId column of the tbl_PropertyValue table.

USE Tfs_TFSConfiguration
SELECT i1.AccountName, i1.Id FROM tbl_Identity AS i1
LEFT OUTER JOIN tbl_Identity AS i2
    ON (i1.AccountName=i2.AccountName AND i1.SequenceId<i2.SequenceId)
WHERE i2.AccountName IS NULL
    AND i1.AccountName IN ('<your first user>',_
    '<another user>','<and so on>')

This gives a list of the most recent the Id(s), in GUID form, for the accounts that you need to update. The last line is only needed to filter the returned list to specific identities and can be discarded if you want all identities returned. The returned GUIDs must be reformatted into ArtifactId(s), which is a transformed binary format. This is accomplished by reversing the byte order (low to high) of each of the first three parts of the GUID, but leaving the last two parts in order. E.g.:

Returned 'Id' GUID     =01020304-0506-0708-090A-0B0C0D0E0F10
Byte Swapped GUID      =04030201-0605-0807-090A-0B0C0D0E0F10
Reformatted 'ArtifacId'=0x0403020106050807090A0B0C0D0E0F10

Second, Find the `PropertyId`s for the Profile Fields that You Want to Add or Modify

In TFS 2013 U5, these can be found with the following query:

USE Tfs_TFSConfiguration
SELECT Name, PropertyId FROM tbl_PropertyDefinition
WHERE Name LIKE '%Address%' OR Name LIKE '%Identity.Image%'

This will give you the PropertyId(s) for the following Profile columns of interest:

• ConfirmedNotificationAddress
• CustomNotificationAddresses
• Microsoft.TeamFoundation.Identity.Image.Data
• Microsoft.TeamFoundation.Identity.Image.Id
• Microsoft.TeamFoundation.Identity.Image.Type

The first two are used by TFS 2013 U5 to send notification emails, the last three are used for the user image displayed in various places of the TFS web interface.

Third, Find the `InternalKindId` Associated with the `PropertyId` Fields

Find the InternalKindId for the Identity Framework for the TFS DatabaseCategory:

USE Tfs_TFSConfiguration
SELECT Description, InternalKindId FROM tbl_PropertyArtifactKind
WHERE Description='Identity'

To Update or Set the Notification Email for the User's Profile

Updating Existing Records

If the configuration records for your user(s) already exist, you can update the email notification settings with:

USE Tfs_TFSConfiguration
UPDATE tbl_PropertyValue SET LeadingStringValue='<user's notification email address>'
WHERE ArtifactId=<ArtifactId, reformatted from tbl_Identity query>
AND PropertyId IN ('<PropertyId for ConfirmedNotificationAddress>',
               '<PropertyId for CustomNotificationAddresses>')

Note: ArtifactId is a binary value, based upon a semi-byte-swapped database GUID, and will not match a quoted value in the UPDATE query, i.e., this part of the query will look something like:

WHERE ArtifactId=0x90D490F6BF7B31491CB894323F38A91F AND

Inserting New Records

Below, I assume that the PartitionId is '1'; this should be verified before you continue by a brief scan of the records in the tbl_PropertyValue table.
If you are loading configuration settings that have not yet been set:

USE Tfs_TFSConfiguration
INSERT INTO tbl_PropertyValue
    (PartitionId, ArtifactId, InternalKindId, Version, PropertyId, LeadingStringValue)
VALUES ('1', <ArtifactId, reformatted from tbl_Identity query>,
    '<InternalKindId from tbl_PropertyArtifactKind>', '0',
    '<PropertyId for ConfirmedNotificationAddress>', 
    '<user's notification email address>'),

    ('1', <ArtifactId, reformatted from tbl_Identity query>,
    '<InternalKindId from tbl_PropertyArtifactKind>', '0',
    '<PropertyId for CustomNotificationAddresses>', 
    '<user's notification email address>')

Note: ArtifactId must be an unquoted binary value, transformed from the GUID returned from the tbl_Identity as explained above.
Note: Two records are created for each ArtifactId, one for each PropertyId.

To Update or Set the User's Profile Image

Updating Existing Records

If the image records for your user(s) already exist, you can update them with:

USE Tfs_TFSConfiguration

UPDATE tbl_PropertyValue SET BinaryValue=<binary image data>
WHERE ArtifactId=<ArtifactId, reformatted from tbl_Identity query>
AND PropertyId='<PropertyId for Microsoft.TeamFoundation.Identity.Image.Data>'

UPDATE tbl_PropertyValue SET BinaryValue=<16 random bytes>
WHERE ArtifactId=<ArtifactId, reformatted from tbl_Identity query>
AND PropertyId='<PropertyId for Microsoft.TeamFoundation.Identity.Image.Id>'

UPDATE tbl_PropertyValue SET LeadingStringValue='<image MIME type>'
WHERE ArtifactId=<ArtifactId, reformatted from tbl_Identity query>
AND PropertyId='<PropertyId for Microsoft.TeamFoundation.Identity.Image.Type>'

Note: The BinaryValue(s) for the image Data and Id and the comparative value for ArtifactId are binary values and should be preceded by an 0x and not quoted.
Note: The BinaryValue for the image Id might be a hash of the image data, but I found that any 16-byte random value would work.
Note: I only have experience with 'image/png' for the Type record. I make the assumption that other image types, such as 'image/jpg', are also accommodated.

Inserting New Records

If you are adding the user's Profile image where it has not previously been set:

USE Tfs_TFSConfiguration

INSERT INTO tbl_PropertyValue
    (PartitionId, ArtifactId, InternalKindId, Version, PropertyId, BinaryValue)
VALUES ('1', <ArtifactId, reformatted from tbl_Identity query>,
    '<InternalKindId from tbl_PropertyArtifactKind>', '0',
    '<PropertyId for Microsoft.TeamFoundation.Identity.Image.Data>', 
    <binary image data>),

    ('1', <ArtifactId, reformatted from tbl_Identity query>,
    '<InternalKindId from tbl_PropertyArtifactKind>', '0',
    '<PropertyId for Microsoft.TeamFoundation.Identity.Image.Id>', 
    <16 random bytes>)

INSERT INTO tbl_PropertyValue
    (PartitionId, ArtifactId, InternalKindId, Version, PropertyId, LeadingStringValue)
VALUES ('1', <ArtifactId, reformatted from tbl_Identity query>,
    '<InternalKindId from tbl_PropertyArtifactKind>', '0',
    '<PropertyId for Microsoft.TeamFoundation.Identity.Image.Type>',
    '<image MIME type>')

Note: The notes from the previous subsection still apply.

History

• 2017.03.31 - Initial submission (JR.o)