TeamCity – Upgrade to 7.1 & Migrate to SQL Server

Having gone through the process of upgrading a TeamCity server from 7.0.3 to 7.1 and then migrating to a SQL Server back end I thought I would highlight some of the problems and solutions I encountered.

First of all, this article helped me immensely in getting through the process. I followed the exact same process listed in the article to upgrade my TC server from 7.0.3 to 7.1 and then again when migrating to a SQL Server back end. The only notable difference was that I did not need to specify the SQL Server port number (1433) in the connectionUrl entry within the file. Also, before doing the upgrade to SQL Server make sure you can login to your new (empty) TeamCity database using a TCP/IP connection otherwise the maintainDB command will fail. You need to ensure that the SQL Browser service is running and then you can test a TCP connection either by;

  • Using the SqlCmd Utility and specifying the tcp protocol for the -S flag
  • Using SSMS and selecting the Options >> button and setting the Network Protocol under the Connection Properties tab

SSMS Options

The other part of the upgrade process that is worth a mention, for 7.1 at least, is the NTLM authentication. Make sure you read this article first, and complete the changes, before you complete the tasks listed in this article. Since we had made the switch a SQL Server back end I did come across one gotcha when trying to login to TeamCity after changing to NTLM authentication. As per the documentation “… on switching from one authentication to another you start with no users (and no administrator) and will be prompted for administrator account on first TeamCity start after the authentication change.” However, when I tried to create a new administrator account it would fail saying username or password is incorrect and I could not login to my new TeamCity server. To get around the problem I had to do the following;

  • Stop the TeamCity Server service.
  • Take a backup of my TeamCity database (just to be safe, but this is optional).
  • In SSMS open up the users table in the TeamCity database and look for the administrators entry in the table – this should be row #1.
  • In the auth_type column change the entry from DefaultLoginModule to NTDomainLoginModule.
  • Restart the TeamCity Server service

I was then able to login to the server as the administrator to ensure the settings were correct and new users to the system could automatically login to the TeamCity server.