As TFS evolves, it is becoming more helpful for users to have a profile picture. In TFS 2015, for instance, thumbnail pictures are now displayed on the Kanban board — a nice visual way of seeing who currently owns the story.

Of course, out-of-the-box TFS doesn’t have any pictures. Users can add their own picture in TFS by:

  1. Switching to the admin view (clicking the gear icon in the upper right of the TFS web page)
  2. Selecting the Security tab
  3. Clicking on the Users heading in the left-hand navigation pane
  4. Searching for their own name in the user list
  5. Clicking on the user image on the upper-right of the web page

This doesn’t work, however, for changing pictures that are not your own. If you are a TFS Administrator, you might: a) want to improve the user experience for everyone by having lots of user pictures represented, and b) not want to load each one individually.

You need an automated tool to import pictures and keep them updated.

Updating Everyone’s Photos

To do this, your first difficulty is finding a source for picture information. I can offer no help there. Most companies do have this in a database somewhere, or possibly a file store. The requirements for this store are simply that you have an image and an associated user logon name. Most common image file formats will work.

You shouldn’t use a name for matching purposes, because larger organizations will likely have multiple employees with the same name. Nobody likes having their picture mixed up with someone else’s.

In our case, the images originated out of the employee badging system. Because they are used to provided images in the Active Directory — for display in Outlook and Lync — the work to associate them with a Windows account was already done (by me, as it happens). Your mileage may vary.

I’ve provided the source code to this tool, but I’ve also tried to make this flexible enough that many users won’t need to change it. If you have the images in a SQL Server database, along with their Windows account, you can probably run this tool without any code modification. Otherwise, change the SQL data provider and change the code it to make it work.

I happen find it fun when people load whimsical pictures of themselves, so the tool does not overwrite any pictures that already exist in TFS. If a user changes their picture, the tool will not change it back. It also means that an updated photo will not get pushed into TFS unless the previous one is deleted. If you don’t what this behavior, just remove the check from the code.

What it Does

The application connects to the TFS web service and retrieves the identity information for each user in a Project Collection. It then queries a SQL database for an image that corresponds to the account name. If an image is found, it is resized, converted to PNG format, then uploaded to TFS via the same web service. Progress and error information are written to the Windows Event Log.

The tool is a Windows console (command line) application, intended to be run as a Windows scheduled task. You monitor the health of the application using your existing server monitoring software, just include the new events in the event log monitor.

Setting IT Up

If your organization has a SQL database that meets our criteria, you can use the tools as-is (without modifying code). Setup requires setting four values in the app.config file, granting permissions to the database, adding an event definition in the event log, and scheduling the tool to run a regular intervals.

The four values in the app.config file are named:

  • TfsServerUrl - the URL to the TFS server
  • ProjectCollectionID - the GUID for the Project Collection
  • SQLCommand - the SQL to execute to retrieve a photo from the database
  • PhotoData - the SQL connection string to the photo database

The TfsServerUrl should be straightforward — it’s the name you use to connect to the root of the TFS web site. The URL should include HTTP or HTTPS and/or port 8080 as appropriate.

To get the ProjectCollectionID, easiest approach is to query the TFS Configuration database (usually helpfully named Tfs_Configuration) on the database server.

SELECT HostId, Name, Description FROM dbo.tbl_ServiceHost

The first column, HostId, will contain the GUID for the ProjectCollectionID.

The SQLCommand value can be any SQL query that accepts a Windows logon name as a parameter and returns an image blob (in the first field). For example, the query can be something like:

SELECT PhotoDB.Photo
FROM dbo.PhotoDB
WHERE PhotoDB.OptOutPhoto = 0
       AND PhotoDB.WindowsAccount = @SamAccount;

As it happens, our implementation requires inner joins and such, but it still returns varbinary picture data in the first column (when it finds a match) so it works. The parameter must be named @SamAccount. The code will create the parameter and set it with the appropriate value from TFS. An even better approach is to use a stored procedure that has a @SamAccount parameter (nvarchar). In that case, just change your SQL to “Execute dbo.YourProcedure @SamAccount” (or change the ADO.Net command code to expect a procedure).

Finally, the PhotoData entry should contain a connection string to the photo database. Note that you will need to ensure that permissions are set up correctly for however this string connects. I recommend that you use the “Trusted Connection” or Windows login approach. That way you can set up a service account to run this as a scheduled task and the same service account should have to have permission to connect to the database and read from the table(s) or execute the stored procedure.

This application will work as a scheduled task or as a console application. To schedule this, you need only specify that you want to start a program, indicate the schedule, and provide the name and path to the application. You should specify that the program should “start in” the same path.

I strongly recommend that you specify a service account (that also has permissions in the database) for the scheduled task. Otherwise, the task will stop working when you change your password or when you account becomes inactive.

Monitoring

The system will write status and error information to the Windows event log. To see these records, open then Event Log Viewer and filter the current log (right-hand option) to show event from an Event Source called TfsUpdatePhotos. The informational entries are detailed enough to illuminate why a picture might be missing; error records will contain error details.

If you find the informational event log detail too large, you can add the command-line parameter -SUPPRESSEXISTING. This will exclude people who already have TFS pictures from being recorded in the log. This may be helpful if you have a lot of users.

Here are the files: