Teamcity NTLM Authentication change - admin user lost in transition

Question

I've switched in teamcity from using basic authentication to using NTLM, on an existing installation.

This works fine, except that the admin user didn't have a corresponding NT account, and so doesn't work on the NTLM configuration. (It is easy to roll back, so it is not a stress).

My question is - what is the command to set a user to admin manually - ie modifying the database?

(like this: TeamCity forgotten admin password - where to look?) but changing the role of a user to global system administrator.

Edit - we're using HSQLDB.

Answer 1

Turns out this is the process:

1. Log onto the teamcity server
2. Stop teamcity web server and build server services on this machine
3. From the teamcity prefs directory ...\TeamcityPrefs\system run the following command java -cp ...\TeamCity\webapps\ROOT\WEB-INF\lib\hsqldb.jar org.hsqldb.util.DatabaseManager
4. Set the connection url to "jdbc:hsqldb:file:buildServer" with username "SA" and password ""
5. Get the userid of the user you want to update: select * from users u where u.username = 'myuser'
6. Insert an admin role for that user: insert into user_roles (user_id, role_id) values (42, 'SYSTEM_ADMIN')
7. Close the HSQLDB manager
8. Start teamcity

Answer 2

You could try this from the TeamCity documentation but it may not work with NTLM.

What database are you using for the TeamCity installation? If you're using the SQL Server backend then you can look in the user_roles table, you would need to set the role_id to SYSTEM_ADMIN with a NULL for project id and find the user_id from the users table (the user will need to have logged into TeamCity). The approach would be similar for other storage backends but it may be easier to simply restart.

Answer 3

Running HSQLDB Database Manager requires an X display - it's actually a lot quicker and simpler to use SqlTool instead:

1. Create an sqltool.rc in the home directory of the user running TeamCity

   urlid teamcity
   url jdbc:hsqldb:file:buildserver
   username sa
   password

2. in your TeamCity datadirectory, cd to system and run:

   java -cp PATH-TO/webapps/TeamCity-6.5.5/WEB-INF/lib/hsqldb.jar org.hsqldb.util.SqlTool teamcity

3. At the SQL prompt, run the remdial action above given by hawkeye.

4. Remember to commit after making your changes