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:
Expand SQL Server Network Configuration, click Protocols for MS SQL.
In the right pane, right-click TCP/IP, then click Enabled and select Yes. (MS SQL comes with TCP/IP disabled by default.) Click OK.
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 click Restart.
Check that SQL Server Browser is running.
Create new database
In MS SQL Server Management Studio:
Connect to your database server, right-click the Databases node in the Object Explorer and select New database.
On the General page, specify the database name, "TeamCity" in the image below, and allocate sufficient transaction log space. The recommended minimum is 1Gb (1024 Mb) in the image below. The requirements vary depending on how intensively the server will be used.
Next specify the 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:Now make sure the "
no count
" setting is disabled as follows: right-click the server instance in Object Explorer, go to Properties | Connections. In the Default Connection Options frame,no count
must be turned off. 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
Go to the Security node, right-click Logins, select New Login, and in the General window that opens provide the login name ("TeamCity" on the image below), select the SQL server authentication and provide the password for the user. Set the default database to TeamCity.
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
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.
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.
Grant this user the 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
Download a Microsoft JDBC driver version 6.0 or later (pick
.exe
or.tar.gz
depending on your TeamCity server platform) from the Microsoft Download Center.Unpack the downloaded package into a temporary directory.
Copy the
sqljdbc42.jar
(ormssql-jdbc-<version>.jre8.jar
in versions above 6.0) package from the just downloaded package into the<TeamCity Data Directory>/lib/jdbc
directory.
Additional 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.
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.
Start TeamCity
Start the TeamCity server. For Windows authentication (MS SQL integrated security), make sure the server is running under the user configured at this step.
Select MS SQL as the database.
Click the Refresh the JDBC drivers if asked.
- 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
Select the required authentication type, for SQL Server authentication provide the credentials of the dedicated SQL user configured at this step.
Continue with the setup. It'll 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 reindexing every several months.