Recipe - TeamCity v9.1.1 and SQLExpress 2014

Given you have installed TeamCity and have been using the internal data store HSQLDB in TeamCity and now want to follow JetBrains recommendations of NOT using HSQLDBfor production use, you need an external database. This is what this little recipe is about.

Offical JetBrain docs

Of course there are good documentation about this, but I write for my self, so… Anyway, you can consult the following:

• Setting up an external database – JetBrains
• Migrating to an external database – JetBrains

Careful if you MIGRATE!!!

I’m not covering any steps to keep any data you have in your current database, e.g. HSQLDB. Please ensure that you have your back covered if you migrate and need to keep any data. Consult the documentation for this.

SQL-Server

First, ensure that you can access the SQL-Server from your server where you have installed TeamCity. If this is not the same server, you can consolidate my previous writings “Recipe – Allow remote connections to SQL-Server Express 2014 on Windows 2012R2”.

Integrated security

My TeamCity installation runs under a specific account: TeamCity; and I want to use integrated securityfor connecting to my database. So lets get a database and login for use with integrated security:

1. Create an empty database, e.g. teamcity.
2. Create a new login, using Windows authentication in SQL-Server and
   map the login to the database created previously.

The log can be your friend

Before continuing with TeamCity configuration. If anything fails when trying to restart TeamCity after you have reconfigured it, check the teamcity-server.log located in your installation. E.g.:

C:\TeamCitylogsteamcity-server.log

JAVA, JAVA, JAVA…

Download the JDBC package from Microsoft’s download center. I downloaded the sqljdbc_4.2.6420.100_enu.exe.

Unzip and take the Microsoft JDBC Driver 4.2 for SQL Serversqljdbc_4.2enusqljdbc4.jar file and place it under your TeamCity installation, e.g.

C:\TeamCityDataBuildServerlibjdbc

Since integrated security is going to be used, we apparently also need to pick a dll for this. From the unzipped files, copy the C:\TempMicrosoft JDBC Driver 4.2 for SQL Serversqljdbc_4.2enuauth[x86|x64]sqljdbc_auth.dll file. NOTE! You need to pick the one matching your JAVA environment, for me it was x86, but I picked the wrong one first but the teamcity-server.log was kind enough to tell me that I picked the wrong one.

The sqljdbc_auth.dll can according to docs be placed in various locations. I just swinged it and put the sqljdbc_auth.dll under C:TeamCitybin.

Connection string

Edit the file: C:\TeamCityDataBuildServerconfigdatabase.properties, and define a connection string mapping to your environment. For me, it was:

connectionUrl=jdbc:sqlserver://127.0.0.1\SQLEXPRESS2014:1433;databaseName=teamcity;integratedSecurity=true

Restart

Again, if you want to keep any existing data, ensure to consolidate the docs. If you are good to go, just restart the TeamCity Server service and hope for the best. When restarted, you should be able to access the webUI. If it fails. Check the log. For me, it took some tries to pick correct version of the sqljdbc_auth.dll and place it in a working directory.

Empty database

When it succeeds in connecting to your database, it will complain about an empty database. If the UI requires a token to continue, you will find that in the mentioned teamcity-server.log. There will be a line stating something like:

[2015-09-17 22:05:00,494]   INFO -  jetbrains.buildServer.STARTUP - Administrator can login from web UI using authentication token: 11111...

The End

That’s it. Hope you made it and found a use for this recipe.

//Daniel