Setting up TeamCity with MS SQL Server

Prerequisites

• MS SQL server

• MS SQL Server Management Studio

• Installed TeamCity Server

Configure  MS SQL server to be used with TeamCity

Enable TCP/IP protocol for your SQL server 

Open SQL Server Configuration Manager and do the following:

1. Expand SQL Server Network Configuration, click Protocols for MS SQL.

2. In the right pane, right-click TCP/IP, and then click Enable and select Yes. (MS SQL comes with TCP/IP disabled by default.) Click OK.

   

3. You need to restart your MSSQL server for the changes to take effect: in the left pane, click SQL Server Services, in the right pane, right-click SQL Server, and then click Restart.

4. Check that SQL Server Browser is running.

   

Create new database

In MS SQL Server Management Studio:

1. Connect to your database server, right-click the Databases node in the Object Explorer and select New database.

2. On the General page, specify the database name, TeamCity in the image below and allocate sufficient https://msdn.microsoft.com/en-us/library/ms365418.aspx space. The recommended minimum is 1Gb (1024 Mb) in the image below. The requirements vary depending on how intensively the server will be used.

   

3. Next we need to specify primary collation: Go to the Option node in the left pane and select a collation on the right. We recommend a case-sensitive collation (with the collation name ending with '_CS_AS') corresponding to your locale and click Ok to save the settings:

   

4. Now make sure that the "no count" setting is disabled as follows: Right-click the server instance in Object Explorer, Choose Properties, Select the Connections tab. In the Default Connection Options frame, "no count" must be unchecked. Save changes if any.

   

Set up TeamCity database user

SQL Server supports two ways of authentication: SQL Server authentication and Windows authentication mode.

• SQL Authentication requires specifying username and a password in the database settings. It is recommended to start with this authentication before you try to use Windows authentication.

• Windows authentication (MS SQL integrated security) allows the TeamCity server running under a specific Windows user connect to the SQL server as that user, without providing a username and a password. However, it requires additional setup

Create dedicated user for TeamCity with SQL server authentication

1. Go to the Security node, right-click Logins, select New Login and in the General window that opens provide the login name, TeamCity in the image below, select the SQL server authentication and provide the password for the user. Set the default database to TeamCity.

2. Next select User Mapping in the left pane, in the upper right pane check the TeamCity database in the list and in the lower pane grant the user TeamCity database owner rights: check the db_owner box. Click Ok.

   

Create SQL database user with Windows authentication

1. Go to the Security node, right-click Logins, select New Login and in the General window that opens provide the login name, select Windows authentication and click the search button.

2. In the Select user or Group dialog, specify the user account which will be used to run TeamCity. Click Check names. Once the user is found, click OK.

   

3. Now grant this user db owner permissions: select User Mapping in the left pane, in the upper right pane check the TeamCity database in the list and in the lower pane grant the user TeamCity database owner rights: check the db_owner box. Click Ok.

   

Set up JDBC Driver for SQL Server database

1. Download the Microsoft JDBC driver v6.0+ (sqljdbc_6.0.x package, .exe or .tar.gz depending on your TeamCity server platform) from the Microsoft Download Center.

2. Unpack the downloaded package into a temporary directory.

3. Copy the sqljdbc42.jar from the just downloaded package into the TeamCity Data Directory/lib/jdbc directory.

integratedSecurityAuthAdditional settings for Windows authentication (MS SQL integrated security)

For Windows authentication (MS SQL integrated security), in addition to the JDBC driver, it is necessary to install native driver library sqljdbc_auth.dll from the JDBC driver package. The required version of the library depends on the bitness of the Java version used by the server.

Since TeamCity 2017.2.4, for the default 32-bit JVM bundled with the TeamCity server, copy the <sql_jdbc_home>/enu/auth/ x86 /sqljdbc_auth.dll file into TeamCity Data Directory \lib\jdbc\native\windows-i386. For the  64-bit JVM used to run the TeamCity server, use the <sql_jdbc_home>/enu/auth/ x64 /sqljdbc_auth.dll and place it into the TeamCity Data Directory \lib\jdbc\native\windows-amd64 directory.

For earlier versions, you need to copy the required library into a directory of your choice outside the TeamCity installation and specify the directory in the TeamCity server JVM options and maintainDB command-line tool options as "-Djava.library.path=<DIRECTORY_WITH_DLL>".

Start TeamCity

1. Start the TeamCity server. For Windows authentication (MS SQL integrated security), make sure the server is running under the user configured at this step.

2. Select MS SQL as the database.

3. Click the Refresh the JDBC drivers if asked.

4. Specify the connection settings: - Database host - your SQL server host - Port - optional, - Database instance name - leave blank for the default SQL server instance. If a named instance is used, provide its name here. - Database name - the name of the newly created database

5. Select the required authentication type, for SQL Server authentication provide the credentials of the dedicated SQL user configured at this step.

   

6. Continue with the setup. Itll take some time to initialize the database schema and the components.

Post-Setup notes

It is recommended to monitor the database performance regularly. For example, perform https://msdn.microsoft.com/en-us/library/ms189858.aspx#Fragmentation once every several months.