Tips for setting up TeamCity with SQLExpress

Team City + SQL Express 2012

Jetbrains has a nice document for setting up an External Database but i run into a few problems and it took me a while to figure out it so I thought I could share it here and hope that it might be helpful for those who are having the same problem.

Here is the link for the installers and its versions.

The following are the issues that I faced when I was setting up the SQL express an external database for TeamCity.

Issues

  • teamcity SQL exception: Network error IOException: Connection refused: connect
  • SQL error when doing: Connecting to MSSQL: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
  • SQL exception: Cannot open database “TeamCityDb” requested by the login. The login failed
  • Database is empty or doesn’t exist

Connection string in database.properties file

The string below is the connection string that I am using in database.properties file.

connectionUrl=jdbc:jtds:sqlserver://localhost:1433/TeamCityDb;instance=sqlexpress

Ok. I am going to share the problem/solution one by one here.

#1. Connection refused: connect

Error: teamcity SQL exception: Network error IOException: Connection refused: connect

Root cause: I got this error because JTDS JDBC driver connection string in database.properties file is using TCP/IP and SQL express comes with TCP/IP disabled by default.

Solution:

  1. Open SQL Configuration Manager
  2. Go to “Protocols for SQLExpress” under “SQL Server Network Configuration”
  3. Open the properties dialog of TCP/IP and select “Yes” for Enabled
    TCPIP in SQL Server Configuration Manager
  4. After that, go to “IP addresses” and set 1433 to “TCP port” property. Note: Please refer to my connection string above. I am using 1433 port but if you are using different tcp port then you need to set your port in this property.
    TCPIP in SQL Server Configuration Manager - 1

#2. Native SSPI library not loaded.

Error: SQL error when doing: Connecting to MSSQL: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

Root cause:This error occurs because I am using windows authentication and I didn’t put the correct build of ntlmauth.dll. If you are using windows authentication, you need to copy ntlmauth.dll from \jtds-1.3.1-dist\x86\SSO\ folder to \TeamCity\bin folder. But I wrongly copied that dll from x64 folder instead of x86 so I was getting this error message.

Solution:   Copy ntlmauth.dll from \jtds-1.3.1-dist\x86\SSO\ folder to \TeamCity\bin folder. “x86” not “x64”.

#3. Cannot open database “TeamCityDb” requested by the login.

Error: 

Cannot open database “TeamCityDb” requested by the login. The login failed.
SQL query: connect
SQL exception: Cannot open database “TeamCityDb” requested by the login. The login failed

Root cause: There is no database called “TeamCityDb”  in SQL express.

Solution: Please create new database called “TeamCityDb” in SQL express.

#4. Database is empty or doesn’t exist

Error: Database is empty or doesn’t exist 

Error #2: Could not take a connection from data source: Cannot open database “TeamCityDb” requested by the login

Root cause: I am not very sure about it but the troubleshooting guide for Team City said that it’s something to do with database schema but I already gave the sysadmin role to “NT Authoriy\System” in SQL but still couldn’t solve the issue.

Solution: 

  1. You will see the error message below when you access the webapp of TeamCity. Please click on “I’m a server administrator. show me the details” link. It will ask you to enter the token. You can get the token under \TeamCity\logs\teamcity-server.log.
    TeamCity Database Creation - 2
  2. Enter the token and you will see this screen.
    TeamCity Database Creation
  3. Just click on “Proceed” and wait a few minutes while TeamCity is setting the settings.

Yap! Those are the issues that I faced when I was setting up SQL express for TeamCity. Hope you find it useful. Please feel free to share your TC tips/tricks with me and my blog readers too. Thanks!

One thought on “Tips for setting up TeamCity with SQLExpress

Leave a Reply

Your email address will not be published. Required fields are marked *