This documentation relates to an earlier version of Bamboo.
View

Unknown macro: {spacejump}

or visit the current documentation home.

These instructions will help you connect Bamboo to a Microsoft SQL Server 2005 or a Microsoft SQL Server 2008 database. Microsoft SQL Server 2000 is no longer a supported database for use with Bamboo.

(info) SQL Server Express 2005 and 2008 are not recommended databases due to CPU, memory, database size limitations (please see these pages for full details: SQL Server Express 2005 feature comparison, SQL Server Express 2008 feature comparison). However, the instructions below will allow you to connect Bamboo to SQL Server Express 2005/2008.

(info) Please note, the JDBC driver for SQL Server 2005/2008 (JTDS 1.2.2) is bundled with Bamboo. You do not have to download and install the driver.

1. Configuring SQL Server

 

Before you connect Bamboo to SQL Server, you need to configure SQL Server appropriately.

  • Change server authentication to 'SQL Server and Windows Authentication mode' — On a typical SQL Server installation, 'Windows Authentication' mode is the default security mode. However, if you try to connect to the database with a database user using this authentication mode, SQL Server will throw an error. You need to change the server authentication mode to 'SQL Server and Windows Authentication mode' in SQL Server before you can connect Bamboo to SQL Server. Please see this MSDN article for instructions on how to do this.
    Screenshot: Changing the SQL Server authentication mode (click to view larger image)
  • Configure your firewall to allow SQL Server access — If you need to access SQL server through a firewall, you will need to configure your firewall appropriately. The following MSDN article describes how to configure a Windows firewall to allow SQL Server access, however the instructions are applicable to other firewalls: Configuring the Windows Firewall to Allow SQL Server Access.
  • Enable the TCP/IP protocol for your database instance — You must enable the TCP/IP protocol for your SQL Server database instance by following the instructions in this MSDN article.

2. Creating Your Database

  • Create the database for Bamboo — see this MSDN article for instructions.
  • Assign the 'db-owner' role on the database for the user that will access the Bamboo database * — the 'db_owner' fixed database role allows the user to perform all configuration and maintenance activities on the database. You need to add this role to the Bamboo user used to access your database by updating the login properties for your database user in SQL Server. Read more about login properties for SQL Server.
    Screenshot: Adding the 'db_owner' database role to a database user in SQL Server (click to view larger image)

(warning) * Please ensure that you are using an SQL Server user to log into your database, not a Windows user.

3. Connecting Bamboo to SQL Server

 

Bamboo provides two ways to connect to a Microsoft SQL Server database — via JDBC or via a datasource. JDBC is generally simpler and is the recommended method.

(info) If you are planning to support Unicode in Bamboo please enable unicode settings for MSSQL

Connecting via JBDC

To connect Bamboo to a MS SQL Server database, via JDBC,

  1. Run the Setup Wizard and choose the 'Custom Installation' method.
  2. At the 'Choose a Database Configuration' step, choose 'External Database' and select 'Microsoft SQL Server 2005/2008' from the list.
  3. The 'Select Database Connection' screen will appear. Select 'Direct JDBC connection'.
  4. The 'Setup JDBC Connection' screen will appear as shown in the screenshot below.
    • 'Driver Class Name' — Type the following: net.sourceforge.jtds.jdbc.Driver
    • 'Database URL' — Type the URL where Bamboo will access your database, e.g. jdbc:jtds:sqlserver://localhost:1433/<database>. For syntax, please see this MS SQL Server documentation.
    • 'Username' — Type the username that Bamboo will use to access your database.
    • 'Password' — Type the password that Bamboo will use to access your database.
  5. Select the 'Overwrite existing data' checkbox if you wish Bamboo to overwrite any tables that already exist in the database.
  6. Click 'Continue' to finish specifying your connection settings.

Screenshot 1: 'Set Up JDBC Connection SQL Server 2005/2008'

Connecting via a datasource

To connect Bamboo to a MS SQL Server, via a datasource,

  1. Configure a datasource in your application server (consult your application server documentation for details). For the syntax of the MS SQL Server URL to use, please see the MS SQL Server documentation
  2. Run the Setup Wizard and choose the 'Custom Installation' method.
  3. At the 'Choose a Database Configuration' step, choose 'External Database' and select 'Microsoft SQL Server 2005/2008' from the list.
  4. The 'Select Database Connection' screen will appear. Select 'Connect via a datasource (configured in the application server)'.
  5. The 'Setup Datasource Connection' screen will appear as shown in the screenshot below. In the 'JNDI name' field, type the JNDI name of your datasource, as configured in your application server.
    (warning) If java:comp/env/jdbc/DataSourceName doesn't work, try jdbc/DataSourceName (and vice versa).
  6. Select the 'Overwrite existing data' checkbox if you wish Bamboo to overwrite any tables that already exist in the database.
  7. Click 'Continue' to finish specifying your connection settings.

Screenshot 2: 'Setup Datasource Connection'

Unicode Characters Not Supported By Default

Problem
Non-ASCII characters will not be displayed by Bamboo.

Reason
The default SQL Server dialect uses column types that do not support Unicode, specifically the char, varchar and text column types. See CONF-4786 for details.

Solution
To add Unicode support, use the Unicode SQL Server dialect which uses nchar, nvarchar and ntext column types. Unicode SQL Server dialect has the downside of halving the maximum length of each column from 8000 characters to 4000, as every char is stored in two bytes.

Enable Unicode SQL Server dialect on a new setup, perform these steps prior to 'Step 3 - Database Connection Setup'.

  1. Open the <bamboo-install>\webapp\WEB-INF\classes\database-defaults\mssql.properties file within your Bamboo installation folder.
  2. Comment the line: dialect=net.sf.hibernate.dialect.SQLServerDialect
  3. Uncomment the line: #dialect=net.sf.hibernate.dialect.SQLServerIntlDialect
  4. Start the Bamboo Setup Wizard
  • ラベルなし



Log a request with our support team.



Raise an issue for our developers.



See answers from the community.



Tweet, blog and update our documentation.

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.