danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

Recipe - TeamCity v9.1.1 and SQLExpress 2014

Daniel WertheimDaniel Wertheim

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:

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.

teamcity-sqlexpress-sqllogin1

teamcity-sqlexpress-sqllogin3

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  

teamcity-sqlexpress-jar

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.

teamcity-sqlexpress-authdll

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...

teamcity-sqlexpress-empty1

teamcity-sqlexpress-empty2

The End

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

//Daniel

Developer that lives by the mantra "code is meant to be shared".

Comments